Excel Question - Organising data by colour

[PD9]

Hi all,

Stumped by a query on big data I'm brainstorming how to attain quickly. Based on key product per order, I have highlighted said key product row yellow. The workbook contains hundreds of these products over multiple orders which have also been highlighted yellow. The next part of the query is looking to see what was ordered with key product - other lines on order are non-highlighted, but so is all the data I don't want i.e. widgets that were not ordered with key product.

What I am struggling with is a way to sort / filter highlighted cells with the associated lines on order, per snapshot below. I aim to use the order number (Column P), however, I can't get this into any organised format. At the moment I have sorted by order number (Column P - high to low) where I'm manually running down the spreadsheet highlighting the full order to allow me to 'clean' data before pivoting the target data set. Highlight by colour only gives me all the key products.



Any ideas?

[Marcellus]

Filter / Data / "filter by colour" or "sort by colour" ??

[PD9]

Marcellus said:

Filter / Data / "filter by colour" or "sort by colour" ??

This only pulls the yellow highlighted rows. I'm looking for the rest of the items on the associated order number also so I can get visibility. Only the key product is highlighted, the other lines aren't this is where my problem originates. I can't find how to group these easily without organising by 'order number' and manually highlighting the entire order.

[louiebaby]

PD9 said:

Hi all,

Stumped by a query on big data I'm brainstorming how to attain quickly. Based on key product per order, I have highlighted said key product row yellow. The workbook contains hundreds of these products over multiple orders which have also been highlighted yellow. The next part of the query is looking to see what was ordered with key product - other lines on order are non-highlighted, but so is all the data I don't want i.e. widgets that were not ordered with key product.

What I am struggling with is a way to sort / filter highlighted cells with the associated lines on order, per snapshot below. I aim to use the order number (Column P), however, I can't get this into any organised format. At the moment I have sorted by order number (Column P - high to low) where I'm manually running down the spreadsheet highlighting the full order to allow me to 'clean' data before pivoting the target data set. Highlight by colour only gives me all the key products.



Any ideas?

Do you want to Highlight the first time the number appears in column P, and then filter to show just the highlighted column?

If so, you could do some conditional formatting to look at the value in the cell, vs the cell above.

Alternatively, you could go with a column with a simple True / False in it with P2 = P1 formula. Every time you see false, it's the first time it's there.

Both of these approaches require the data to be sorted in such a way that there is no way for the sequence to go like this:

Cherry
Cherry
Sunny
Sunny
Sunny
Cherry
Sunny

Because you'll get the 5th and 6th being seen as "new" things.

[Mr Pointy]

No, he wants to filter out all of the items with 1000308 in column P, but it's not just 1000308 it could be loads of others. The only way he has of identifying them is that each time the first row is coloured yellow.

[paulrockliffe]

Why have you colour coded the rows rather than adding a flag column? It's trivial if you'd just added a flag.

VBA to create the flag column from the colouring?

Excel 2019 (not sure about earlier versions) appears to let you search (Ctrl+F) by cell colour - select all will get you a start.

Select the table, press F5, select Special and there might be a few things in there you can use if you're clever enough.

But the simplest way is to format it as a table, filter by colour and use that to get your list of order numbers. Then put that list into another table and use that as a reference to identify all the other products.

The most elegant way is to create the lookup table, load both into the Data Model and link them with an appropriate Relationship and draw the table you want by inserting a Pivot Table that's hooked off the Data Model.

[PD9]

Thanks everyone. Paul, the key products were initially filtered and highlighted yellow so I could easily identify them in the dataset. From here the idea was to sort by order number - common denominator to allow me to see what else had been ordered with key product. I then highlighted all of the order numbers to gather relevant information - removing the un-highlighted data (standalone products not required). Table 1 below.



Table 2 shows cleansed data. I've un-highlighted the other lines on order referring back to my original query on how I was looking to 'group' the dataset with the key product.



Took a while, but manually is the only way I could think of this.

[PD9]

paulrockliffe said:

Why have you colour coded the rows rather than adding a flag column? It's trivial if you'd just added a flag.

VBA to create the flag column from the colouring?

Excel 2019 (not sure about earlier versions) appears to let you search (Ctrl+F) by cell colour - select all will get you a start.

Select the table, press F5, select Special and there might be a few things in there you can use if you're clever enough.

But the simplest way is to format it as a table, filter by colour and use that to get your list of order numbers. Then put that list into another table and use that as a reference to identify all the other products.

The most elegant way is to create the lookup table, load both into the Data Model and link them with an appropriate Relationship and draw the table you want by inserting a Pivot Table that's hooked off the Data Model.

Like the (Ctrl+F) & F5 tip, will use this going forward.

[AndyAudi]

There’s prob other ways but I would’ve

Filtered by colour & then copied all the yellow orders onto separated sheet so I had a list of them

Created a “lookup” on a column on the original sheet to check all the orders on the yellow list