Create a list from a named range based on criteria

[Awesome Wally]

I am trying to create a unique list from a named range containing duplicates based on a set of criteria. The goal is to use this unique list in a combo box. I believe the response that NARAYANK991 gave in response to the question posted at: http://forum.chandoo.org/threads/ho...ray-that-is-created-by-an-array-formula.5171/ is on the right track but the values I want extracted are text and not numbers. I really like the example Luke has in http://chandoo.org/wp/2011/11/18/formula-forensics-003/ but it doesn't work with duplicate entries. Any idea how I can combine the two formulas [=IFERROR(SMALL((B=E$2)*(A),(ROWS(A)-COUNTIF(B,E$2)+ROW(A1))),"")] and [=IF(COUNTIF(A:A,$D$2) < ROWS($E$2:E2), “”, INDEX(B:B, SMALL( IF($A$2:$A$10 =$D$2, ROW( $A$2:$A$10)), ROW(A1))))] to generate a unique list based on selected criteria?

I have attached a file that helps illustrate what I'm after.
Thanks in advance!

 

[Somendra Misra]

Hi,

See this file.

Regards,

 

[Awesome Wally]

Somendra, Thanks for the quick reply! The formulas all work perfect. I never would have thought of using a frequency helper cells.

 

[Somendra Misra]

Thanks for the feedback, and welcome back!!!

Regards,

 

[Awesome Wally]

I thought of another piece that I thought I had an existing code for but can't find now. The Form Control combo box will contain blank spaces where the cell contains a formula but displays blank "" such as F6:F9. Could I use a counta to leave these out of my combo box and only display data that isn't ""?

 

[Somendra Misra]

Hi,

See this file, used dynamic named range for 2nd drop down. Go through name manager.

Regards,

 

[Awesome Wally]

Perfect! Thanks again.