Array formula needed to pull non duplicate names

[Schubby10]

Hi there

Had a lot of help here before so looking for similar help. I have a big list of accounts with duplicate people for certain accounts

Looking to pull specific people that belong to a certain account without duplicates.

I have the people in column O with the account number in column F

Any idea how to structure this?

Thank you

 

[KolGuyXcel]

Assuming 1st row has headers, to get the list of unique names in a list try this array formula (Using Ctrl+Shift+Enter) in Cell P2 and drag down, the data is assumed to run till 10th row in this formula adjust range accordingly!

{=IFERROR(INDEX($O$2:$O$10,MATCH(0,COUNTIF($P$1:P1,$O$2:$O$10),0)),"")}

When the list has collected all the unique names, the following rows (if dragged further) in column P would show BLANK.

 

[Schubby10]

Sorry KolGuyXcel, maybe I wasn't communicating it right. I am looking to pull unique names for specific accounts in a list. So for X account all names that show up non repeating. Then if I set it to Y acct non repeating names under Y.

Thank you,

 

[AhoyNC]

Try this array formula (enter with CTRL-SHIFT-ENTER)
Drag formula down as needed.

Excel Workbook
	F	N	O	P	Q	R
1	Acct#		People		Find Acct	A1
2	A1		Name1		People	Name1
3	A2		Name2			Name2
4	A3		Name3			Name4
5	A1		Name2
6	A3		Name5
7	A1		Name2
8	A7		Name7
9	A1		Name4
10	A9		Name9
11	A3		Name4
12
Sheet

 

[Schubby10]

Hey thank you for the assistance, the formula looks to just be returning "0"

 

[AhoyNC]

How about posting a small sample of your data and the actual formula you entered. As you can see it works in the example above (is this close to what your data looks like?).
Are your account numbers actual numbers or alphanumeric?
Also, make sure you entered the formula with CTRL-SHIFT-ENTER, if done right Excel will put brackets {} around the formula.

 

[Schubby10]

So the acct numbers I am trying to pull are all numbers (123-456-78900-1111-2222) with some dashes, but they do match. I have two tabs.

Here is the formula in its entirety

={IFERROR(INDEX(Data!$O$2:$O$500000,SMALL(IF(FREQUENCY(if(Data!$F$2:$F$500000=$A$8,MATCH(Data!$O$2:$O$500000,Data!$O$2:$O$500000,0)),ROW(Data!$O$2:$O$500000)-ROW(Data!$O$2)+1),ROW(Data!$O$2:$O$500000)-ROW(Data!$O$2)+1),ROWS($A$8:A8))),"")}

 

[AhoyNC]

I used the formula you posted and it worked for me. Do you see any difference between the example below and your data? Also, if there is an error it should return a blank, not sure why you got a 0.
I set up two tabs one named Data.

Excel Workbook
	A	B
7	Find Acct#	Names
8	123-456-78900-1111-2222	Name1
9		Name2
10		Name4
11
Sheet2

Excel Workbook
	F	G	N	O
1	Acct#			People
2	123-456-78900-1111-2222			Name1
3	123-456-78900-1111-2224			Name2
4	123-456-78900-1111-2224			Name3
5	123-456-78900-1111-2222			Name2
6	123-456-78900-1111-2260			Name5
7	123-456-78900-1111-2222			Name2
8	123-456-78900-1111-2260			Name7
9	123-456-78900-1111-2222			Name4
10	123-456-78900-1111-2224			Name9
11	123-456-78900-1111-2260			Name4
Data

 

[Schubby10]

Thank you for all the help, just to clarify, there are numerous accounts and same names.

For example Acct 123-456-78900-1111-2222 name A, name B, name A, name A, name A.

 

[AhoyNC]

In example below Nam1 appears on same account a number of times and formula returns name only one time.

Excel Workbook
	A	B
7	Find Acct#	Names
8	123-456-78900-1111-2222	Name1
9		Name2
10
Sheet

Excel Workbook
	F	G	N	O
1	Acct#			People
2	123-456-78900-1111-2222			Name1
3	123-456-78900-1111-2224			Name2
4	123-456-78900-1111-2224			Name3
5	123-456-78900-1111-2222			Name1
6	123-456-78900-1111-2260			Name5
7	123-456-78900-1111-2222			Name2
8	123-456-78900-1111-2260			Name7
9	123-456-78900-1111-2222			Name1
10	123-456-78900-1111-2224			Name9
11	123-456-78900-1111-2260			Name4
Data