Formula to list unique names in range

[daniels012]

I need a formula to create a list of names (starting in cell X16) to pull from the range B3:C90. I only need the names once.

I have seen way in the past a few Array formulas using Crtl-Shft-Enter, I just can't seem to find them now.

Any help is greatly appreciated.
Michael

 

[daniels012]

Actually, it just gives the first name all the way down the list.
Michael

 

[Domenic]

For the first cell containing the formula, make sure that the cell being referenced is the one right above the cell containing the formula. So, if you're entering the formula in X16, the reference should be $X$15:X15. Is this the case?

 

[daniels012]

Beautiful!
I love it!

You are really great at this formula stuff.
Do you want me to add something else......?
How about if the #NUM! error comes up because the formula has chosen all of the given names it then returns a blank or empty result.

I am very happy with what you have helped me with. If you, don't want to attempt this part, I am fine with that!

Thank You again,
Michael

 

[daniels012]

Domenic,
Is there a way to use a range in your array formula to include 2 separate blocks of data?

Like this set of non contiguous cells B3:C90 and I3:J90

Thank You,
Michael

 

[Domenic]

For Excel 2007 or later...

X16, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IFERROR(INDEX(tbl_text,SMALL(IF(tbl_text<>"",IF(ISNA(MATCH(tbl_text,$X$15:X15,0)),ROW(tbl_text)-MIN(ROW(tbl_text))+1)),1),RIGHT(MIN(IF(tbl_text<>"",IF(ISNA(MATCH(tbl_text,$X$15:X15,0)),(ROW(tbl_text)-MIN(ROW(tbl_text))+1)*10^5+(COLUMN(tbl_text)-MIN(COLUMN(tbl_text))+1)))),5)+0),"")

For earlier versions...

W16, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(tbl_text<>"",1/COUNTIF(tbl_text,tbl_text)))

X16, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS($X$16:X16)<=$W$16,INDEX(tbl_text,SMALL(IF(tbl_text<>"",IF(ISNA(MATCH(tbl_text,$X$15:X15,0)),ROW(tbl_text)-MIN(ROW(tbl_text))+1)),1),RIGHT(MIN(IF(tbl_text<>"",IF(ISNA(MATCH(tbl_text,$X$15:X15,0)),(ROW(tbl_text)-MIN(ROW(tbl_text))+1)*10^5+(COLUMN(tbl_text)-MIN(COLUMN(tbl_text))+1)))),5)+0),"")

For a non-contiguous range, I won't be able to look at it now. So if no one else responds, I'll have a look at it when I get a chance.

 

[daniels012]

no problem. I figured what I would do is use helper files down on cells AI3:AJ170. and name it name_tbl
This seems to work fine! The only thing is the empty/blank cells return 0 again.
Thank you for the last formula!!
Michael

 

[Domenic]

no problem. I figured what I would do is use helper files down on cells AI3:AJ170. and name it name_tbl
This seems to work fine! The only thing is the empty/blank cells return 0 again.

Not sure what you mean. By the way, it's possible to avoid helper cells/columns. However, it would mean two additional conditional statements. One added to the SMALL function, and another added to the MIN function. This would make an already expensive formula even more so. How would you like to proceed?

Thank you for the last formula!l

You're welcome!

 

[daniels012]

Some times I type too fast.
I meant helper cells not files.
Instead of having 2 blocks of ranges I combined all data to reflect the cells in Columns B, C, I, and J
I have them in cells AI3:AJ170

Really, I would mainly just want to remove the 0 for blank cells. As far as the non helper cells is ok, I just really don't want to make the formula too confusing.

Michael

 

[Domenic]

The formula I offered deals with both multiple columns and empty/blank cells...

 

[SJ-NY]

Have you tried to the "Advance Filter" to obtain a unique list of names?