Creating a list of records based upon the occurence of a value

**gmt1975** · 08-07-2012, 12:47 AM

Hi, geniuses. I have an excel spreadsheet with three columns. Column A contains a list of names, and it has a Named Range NAME. Column B contains a simple drop down with three values, blank (null), "PARK1" and "PARK2", and this also has a Named Range LOCATION. What I'd like to do is have Column C return a simple list of each name from NAME where LOCATION reads "PARK1". I'm pretty sure this involves an array formula of some kind, but I'll be darned if I can figure it out.

Now, I have been able to accomplish this with a hidden sheet, and I do it in two steps.

The first step is I mirror the list of names from Sheet 1's NAME onto Sheet 2's Column A, which I gave a Named Range CALCULATION, but I have a formula that checks to see if Sheet 1's LOCATION says "PARK1" in each cell, and if that column for that record does not say PARK1, Sheet 2's CALCULATION returns blank:

IF(LOCATION="PARK1", NAME, "")

Then I have a separate area where I have an array formula that creates a list of the visible names, and removes all the blanks, and it contains an IFERROR section to blank out the end of the list (so I don't have a few names and then a bunch of #VAL! errors in my list):

=IFERROR(INDEX(CALCULATION, SMALL(IF(FREQUENCY(IF(CALCULATION<>"", MATCH(ROW(CALCULATION), ROW(CALCULATION)), ""), MATCH(ROW(CALCULATION), ROW(CALCULATION)))>0, MATCH(ROW(CALCULATION), ROW(CALCULATION)), ""), ROW(A1)), COLUMN(A1)), "")

That kinda works for me, but I'm hoping to accomplish this task with one simple formula on my visible sheet, and get rid of the stupid hidden sheet altogether. Isn't there a way to do this with one formula, instead of two formulas and a hidden sheet? I've scoured Google, and can't find anyone else that's solved this issue. Or even, for that matter, ASKED the question about solving a problem like this. Thanks a million, guys!