Sorting a Range by Cell ‘Highlight-color’

Question

I have some large spreadsheets, the range is more than A1 to NZ2500 (very complicated)

All cells (in this range) that contain values matching any cell-value in Column A is highlighted in Green (through conditional formatting)

Example – as in this screenshot, (below)

What I now need is;

1. To sort all Green-Highlighted cells, so that they come on top of every column
2. Arrange all columns, so that the column with most number of ‘Green highlighted cells’ is at Column B

and the Column with least number of ‘Green highlighted cells’ appear at the Right extreme column.

Example as in this Screenshot (below)

Could someone please provide me with formulas and / or Macros, so as to accomplish this task.

Will be most grateful

Thanking You

Imtiaz

<Moved from Excel/Windows10/Office365 for business>

Answer 1

How are these colors applied, highlights or background color?

Second, you can't (easily) sort columns separately. Rows are usually sorted as a unit.

Your data is strange. What are you trying to do record? Items sold in each store?

How are the first 4 "items" in the "items" column, items, they are people's names? Are you selling people (that is frowned on in some places <g>). And the entries in the first row under the various stores are all in the first column, but for the other rows they have some new "items".

I suspect you are going to have to restructure your data, "unpivot" it so that it has 2 columns, store number and item. Then you can sort items, by color. I can tell you how to automate that.

Here is a "shotgun" list of articles about sorting by colors. I doubt that any will do what you need, but they may give you an idea.

**Sort by Color****** April 14, 2016

http://www.excel-university.com/sort-by-color/

In this post, I’ll answer a question submitted by reader Chérie about sorting by color. The basic question is this. “I have created a color coded list, where yes=green, no=red, maybe=orange, and other is any other color. How can I sort the list so that all the yes rows are first, then no, then maybe, […]

**Sort By Colour in Excel****** February 24, 2010****http://blog.contextures.com/archives/2010/02/24/sort-by-colour-in-excel/****

In the old days, the Sort dialog box in Excel only had 3 levels. However, with a bit of planning, you could sort Excel data by 4 columns or more, and once you learned that trick, life was good. Or at least it was sort of good. ;-)

**Sort It Your Way With Custom Lists****** March 10, 2010

You know how to sort an Excel list alphabetically, and with Excel 2007 you can even sort an Excel list by colour. Did you know that you can also create a custom list in...

**Sorting by Colors** 

Need to sort your data based on the color of the cell or the color of the text within the cell? Excel makes it easy to do the sorting, as discussed in this tip.

**Sorting by Fill Color**

http://excel.tips.net/T002009_Sorting_by_Fill_Color.html

Sorting data in a worksheet is easy, unless you want to sort by the color used to fill a range of cells. There are ways to do the sorting, however, as illustrated in this tip.

@ (Advanced) Sort data in a range or table

https://support.office.com/en-us/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by

.  *  Sort numbers---------------------------------      .  *  Sort dates or times

.  *  Sort by more than one column or row---    .  *  Sort by cell color, font color, or icon

.  *  Sort by a custom list-------------------------     .  *  Case sensitive sort

.  *  Sort from left to right------------------------      .  *  Sort by a partial value in a column

.  *  Sort a range within a larger range--------

Answer 2

Thanks for the quick reply.

You have taken some trouble to explain.

Thanks.

First let me answer your 2 queries.

How are these colors applied, highlights or background color?

The Colors are ‘Background Color Fill’ obtained by using ‘Format Cells’ through ‘Conditional Formatting’

Your data is strange

What I have attached as screenshots are not the data I am using.

The data in the screenshots are just samples that I ‘made up’

In each Column, I put a ‘Store Number’, the names of staff members (in that store) and items sold or available in the store. This data has no meaning. I just populated the cells with whatever came to my mind.

Sort By Colour in ExcelFebruary 24, 2010http://blog.contextures.com/archives/2010/02/24/sort-by-colour-in-excel/**** Yes I did go through that information, however it is for a whole row, so it is not applicable o me. I also read (with interest) the rest of your answer. Thanks. Also sorry, as it is not what I am looking for.

Thanking you for your assistance.

Regards

Imtiaz

Answer 3

Data, even example data, has to have some underlying logic.  Right now, as you say, the data you are showing us is apparently random, in which case we can't sort it.

So more realistically based on the first 2 columns of your first example your data could be translated to look like this (defined as tables), 2 separate sets of data in 2 tables:

For example, all of the highlighted items in column A are the only items that are highlighted in the other columns.

What are the conditions you used in conditional formatting to highlight the items. That probably can be used as a base to do the sorting

Answer 4

Your query makes sense.

This is how I got the matching Cells in Green

Step 1 - I selected A2:A13, Right Clicked, Named it ‘Items’

Step 2 - Select B2:F13

Home Tab (From Menu)|Conditional formatting | New Rule | Use formula......|Enter the formula

[=COUNTIF(Items,B2)>0]

Step 3 - Then I clicked the format button | Fill tab and apply a color (Green) and OK

Thanks

Imtiaz

Answer 5

I'm surprised, it can be done. I just had to change the terms of reference.

First, put your highlight list in a separate list / table, at least one blank column between it and your other data.  The idea is to separate

This list can also be used to sort.  You define it as a "custom list", then you can select the column and sort by that custom list, putting your highlighted items at the top of the column and the unhighlighted items, sorted alphabetically below

this article describes the steps to follow:

https://contexturesblog.com/archives/2010/03/10/sort-it-your-way-with-excel-custom-lists/