Finding Unique strings

  • Thread starter Thread starter Randy S
  • Start date Start date
R

Randy S

Col A Col B Col C Col D
1234 Riverwest $52.50 29
1234 Riverwest $49.66 29
5678 Joe's Bar $29.42 22
1234 Riverwest $68.88 29
5678 Joe's Bar $36.21 22
3456 Al's Grill $43.55 29
3456 Al's Grill $78.54 29

I want to auto-generate with a formula (array formula?) a
list of all unique Col B strings that have Col D = 29. In
this example, I want to get:
Riverwest
Al's Grill
 
Do you definitely need a formula. Data / Filter / Autofilter will do this in a heartbeat.
 
Yes, definitely need a formula. I realize this is much
harder. Any ideas on how to do that?
-----Original Message-----
Do you definitely need a formula. Data / Filter /
Click to expand...
Autofilter will do this in a heartbeat.
 
Col A Col B Col C Col D
1234 Riverwest $52.50 29
1234 Riverwest $49.66 29
5678 Joe's Bar $29.42 22
1234 Riverwest $68.88 29
5678 Joe's Bar $36.21 22
3456 Al's Grill $43.55 29
3456 Al's Grill $78.54 29

I want to auto-generate with a formula (array formula?) a
list of all unique Col B strings that have Col D = 29. In
this example, I want to get:
Riverwest
Al's Grill
Click to expand...

If the table above were named TBL, you could try these formulas.

topmost result (in cell F1 in my test workbook):
=INDEX(TBL,MATCH(29,INDEX(TBL,0,4),0),2)

next result (in cell F2 in my test workbook - ARRAY FORMULA!):
=INDEX(TBL,MATCH(29,(COUNTIF(F$1:F1,INDEX(TBL,0,2))=0)*INDEX(TBL,0,4),0),2)

Select F2 and fill down until you get #N/A results.
 
Randy

Assuming that your data is in the range A1 - D7 then:

1) In cell F1 put in the number 29
2) In cell E1 put in the formula
=IF(AND(D1=$F$1,COUNTIF($B$1:B1,B1)=1),ROW(),"")and copy
down as required.
3) In cell I1 put in the formula
=IF(ISNUMBER(SMALL($E$1:$E$7,ROW())),INDEX(B:B,SMALL
($E$1:$E$7,ROW())),"") and copy down. The range E1 - E7
will have to be modified or made a named range as required.

This will provide a list of unique entries but they will
appear in the same order as they are in the original list.

Tony
 
Back
Top