Automatic Sorting with Multiple Criteria (No pivot tables)

[david@work]

I hope everyone's having a good day!

I've been trying for a few days now to get a workbook to sort automatically into separate tabs per 'Color' after pasting data into a source tab, without the use of a pivot table (I need 5 columns of text to be sorted as well, so pivot tables are of no use).

I've had some success using index, match, if, and offset, but I can't quite seem to get it right. Part of the problem is the 'color' column has multiple instances of the same color, while having different data in the other 5 columns. There is a unique identifier for each row, but I can't seem to get that to match up to its proper row.

Any help would be much appreciated. This has been driving me crazy!

A sample file is uploaded with a bit more context.

 

[david@work]

I should mention too that VBA isn't an option unfortunately.

 

[XLPadawan]

I used to generate monthly reports where I work and because I had dozens of sorts in some of them it was in my best interest to make them sort themselves (which I did). I looked at your Colors Example.xlsx and I have some ideas on how to help you. I don't use VBA because I don't know VBA, so I'm likely going to have to employ a fair number of helper cells if that's ok. I can hide the helper cells or put them on a separate worksheet to keep the output sheet clean if you'd like. Can you provide a separate workbook showing the desired output for what you've already provided? I think I understand what you're wanting, but I'd like to be sure before I dive in.

 

[GFC]

I did a few changes to your file:
1) changed Sub Category to Sub_Category
and On/Off? to On_Off?
As well in source tab as in all other tabs

2) transformed source tab into a table with headers

3) then in the other tabs
in A2 put the following array formula (CRTL SHIFT ENTER)
=IFERROR(INDEX(INDIRECT("Table1["&A$1&"]"),SMALL(IF(Table1[Color]="AAA",ROW(Table1[Color])-1),ROWS($A$1:A1))),"")
(replace AAA with Azure, Blue, ... in each tab)
drag over / drag down

 

[XLPadawan]

Looks like GFC beat me to a solution (I'm not as advanced as a lot of the people helping out here at Chandoo, i.e. I take simpler approaches that usually involve more coding, hence the name XLPadawan instead of XLJedi), but here's a different approach to consider:

 

[david@work]

Thanks to both of you for the help! I've got it all straightened out now and it runs very smoothly. I am quickly discovering just how clever some of these formulas can be within Excel .

 

[Shell Wu]

Do you happen to have the solution still? I'm having the same dilemma, and really liked GFC's solution, but couldn't get it work properly for me, and I don't see he provided a sample file.

 

[NARAYANK991]

Hi ,

If you can provide your sample workbook with your data in it you may get a more appropriate solution.

Narayan

 

[Shell Wu]

Just like the other user, I have one master file to manager, and I would like to have separate tabs for each color, and they are the subset of the source file, which will automatically update as soon as the source tab updates once it's set up. The formula GFC provided was promising, but I could not get it work on the file properly.

=IFERROR(INDEX(INDIRECT("Table1["&A$1&"]"),SMALL(IF(Table1[Color]="AAA",ROW(Table1[Color])-1),ROWS($A$1:A1))),"")
(replace AAA with Azure, Blue, ... in each tab)

Thank you so much!