A list that is derived from another list

[GeneralThunder]

I have a question for all of you. This may be too hard to do. I don't know. But let me know what you think.

I have a list of about 200 names. In the next column over, there is a drop down list where I can choose their status. For example, I have the first 3 names, Steve, Bob, and Carl. Next to each of them is the drop down, where I can select between (for example) cool, happy, and sad.

So let's say that I change the sheet to reflect the following:

Steve is Happy. Bob is Sad. Carl is Happy.

Now what I want to do is create another sheet that would automatically sort the above list. Going off the above example, I would have 3 columns. Column 1 would have Happy. Column 2 would have Sad. Column 3 would have Cool.

Under Happy, it would list Steve and Carl. Under Sad, it would list Bob. Under Cool, it would be blank.

However, if I change the drop down on Carl to say Cool, then the name would be moved from the "Happy" column to the "Cool" column.

All I'm wondering is if there is a way to make this happen. I don't care what the formula is or how difficult it would be. I'm just looking for an answer. I've asked this question on other forums, but apparently nobody wants to answer it because they keep removing it.

I've played around with the "if", "vlookup", and "lookup" formulas. I've had friends suggest making a macro or using the data validation tool. I've even tried mashing some together. Any pointers in the right direction would be appreciated.

Anyways, let me know what you think. I attached a sample spreadsheet that might help illustrate it a little bit.

Thanks in advance!

[Pete_UK]

Put this formula in D3:

=IF(C3="","-",C3&"_"&COUNTIF(C$3:C3,C3))

then copy down as far as you like beyond your data, in order to accommodate new data being added. Then you can use this formula in F3:

=IFERROR(INDEX($B:$B,MATCH(F$2&"_"&ROWS($1:1),$D:$D,0)),"")

Copy across into G3:H3, then copy all 3 formulae down as far as you need.

Hope this helps.

Pete