Filter One Column's Values based on if Another Column has multiple values

[go-birds]

Hi all,
I'm pretty new to excel and need help with something (Sorry for the horrible title, didn't know how to describe what I'm looking to do).


Essentially, given a table like this one:
Excel_help.PNG




I want a list of fruits that have more than one unique value under the Color column. So in this case, I would want a list of Apple and Pear to be returned/filtered (since they feature multiple Colors), and not Grape (because all of the grapes are purple).

Would this require programming? or are there built-in functions that can help with this? Any help is greatly appreciated

[queuesef]

Why not add a 'helper column'. call it Multi (for multiple colors) then place a 1 if it has multi colors or a 0 if it does not. Then you can filter on A) Multi column, then B)Fruit Column to get your results

[go-birds]

thanks for the reply! that's a good idea, but there are around 200000 rows of data in my sheet so that approach isn't as feasible :/

[Glenn Kennedy]

Your horrible title is fine, as far as I'm concerned. However, it's your desired output has me wondering....


Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[go-birds]

Hey glenn, thanks for the help. It would be similar to the excel sheet I posted but with ~200,000 rows of data and 4300 unique 'fruits'

[queuesef]

If you filter the data with known 1 colored fruits and put a 0 in those, then you can filter on the Blank Multi colored and assume that the remainders = 1. It really can't take more than 2 minutes.

[Glenn Kennedy]

Just post a sheet containeing what you expect to see and where you expect to see it... from the scenario in your picture.

[go-birds]

Ok, attached example excel file with details on what I'm looking for. Thanks so much for your help so far guys, I really appreciate it

[queuesef]

Again, you can filter the table for fruit to show or not to show....I did it both ways. One way as 0 OR 1, the other way by using vLookup to put the word show or hide in adjacent cell. First, I created a list of Unique values from the Fruits, then I manually put in Show or Hide in column next to the new list and used in a vLookup.
Give this a try.

[go-birds]

Again, you can filter the table for fruit to show or not to show....I did it both ways. One way as 0 OR 1, the other way by using vLookup to put the word show or hide in adjacent cell. First, I created a list of Unique values from the Fruits, then I manually put in Show or Hide in column next to the new list and used in a vLookup.
Give this a try.

Yes but this requires to manually enter Show/hide next to each 'fruit', which I have over 4300 of, and also would have to figure out if I want to show/hide each fruit manually (would involve going through my 200,000 rows)

[queuesef]

Take a closer look!!! There is a formula/vLookup approach. You can also write a macro, but it really isn't necessary. If you actually attempted this, you would see how fast it is. Before you complain, why not just give it a try??? You have nothing to lose. Other people take the time to build a solution - this is the LEAST you could do. THEN go ahead and criticize AFTER.

[Glenn Kennedy]

This, too, requires a helper... but it's a formula, not a value.

In D2, copied down: =SUM(INDEX(($A$2:$A$23=$A2)/COUNTIFS($A$2:$A$23,$A$2:$A$23,$C$2:$C$23,$C$2:$C$23),0))

In F3, copied down: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$B$100)/(($D$2:$D$100>1)*(COUNTIF($F$2:F2,$A$2:$A$100)=0)),1)),"")

I have no idea how it will perform with 200K rows...

[go-birds]

Thanks everybody for the suggestions and help. I ended up just creating a script that iterated through the rows and counted how many different 'colors' each fruit had, and if there more than one unique value, added the fruit, # of colors as key-value pairs to a dictionary which I was then able to print to cells as a list.