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

 

[Richard Schollar]

Hi Michale

The following works with a single column data source:

Excel Workbook
	A	B
1	List	Uniques
2	Richard	Richard
3	Dan	Dan
4	Terry	Terry
5	Michael	Michael
6	Richard	Bob
7	Terry	#N/A
8	Richard	#N/A
9	Bob	#N/A
Sheet2

Excel 2002

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[LearnMeExcel]

HI
another way

Excel Workbook
	A	B
1	List	Uniques
2	Richard	Richard
3	Dan	Dan
4	Terry	Terry
5	Michael	Michael
6	Richard	Bob
7	Terry
8	Richard
9	Bob
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
this formula from Bosco_yip

 

[daniels012]

Awesome!
Now can it be done for 2 columns worth of unique names?
Say A2:B9?

Michael

 

[daniels012]

Any ideas on the multiple columns of data?????

Michael

 

[Domenic]

Try...

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

=INDEX($B$3:$C$10,SMALL(IF(ISNA(MATCH($B$3:$C$10,$E$2:E2,0)),ROW($B$3:$C$10)-ROW($B$3)+1),1),RIGHT(MIN(IF(ISNA(MATCH($B$3:$C$10,$E$2:E2,0)),(ROW($B$3:$C$10)-ROW($B$3)+1)*10^5+(COLUMN($B$3:$C$10)-COLUMN($B$3)+1))),5)+0)

 

[daniels012]

Domenic,
I am assuming your column E is where the formula is to be placed?
Mine is Column X Starting at Cell X16
Easy enough, I can change your E to X

My Range I have named is "tbl_text" which is B3:C90, should I un-name this to make the formula work?

Thank You,
Michael

 

[Domenic]

Try...

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

=INDEX(tbl_text,SMALL(IF(ISNA(MATCH(tbl_text,$X$15:X15,0)),ROW(tbl_text)-MIN(ROW(tbl_text))+1),1),RIGHT(MIN(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)

 

[daniels012]

That is soooooo awesome.
I have looked on the internet and searched here and have not found the right formula. This is great.
I thank you so much,
Michael

ps. One other thing (but not that important). Is there a way to not include a cell if it is blank, so the blank cell does not show as one of the names. Again, I will live with it if it is big hassle to edit the formula, I just thought I would ask!!!

 

[Domenic]

I thank you so much,

You're very welcome!

Is there a way to not include a cell if it is blank, so the blank cell does not show as one of the names.

Try...

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

=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)