Formula ? Pull in name from another sheet once ?

[zone709]

Hi

I have a sheet that has a list of names in Column A. The thing is the name is there multiple times. How and if I can pull in the name once onto another sheet having it recognize the next name is different and pull it in? Remember each name will not be repeated the same amount of times. It will always be different.

Examples:

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​	Name
13​	Espo
14​	Espo
15​	Espo
16​	Espo
17​	Rivera
18​	Rivera
19​	Rivera
20​	Rivera
21​	Rivera
22​	Len
23​	Len
24​	Len
25​	Len
26​
27​

Sheet: Flush

Results

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​	Name
13​	Espo
14​	Rivera
15​	Len
16​
17​

Sheet: Roll

 

[AliGW]

You'll need to change the formula to match. Here's my best guess:

=IFERROR(INDEX(Flush!$A$13:$A$1000,SMALL(IF(Flush!$A$13:$A$1000<>Flush!$A$12:$A$999,ROW($A$13:$A$1000)-ROW($A$13)+1),ROWS($A$13:$A13))),"")&""

 

[Joe4]

Change that row reference to EXACTLY match your last row with data in that column.
Also be sure to use CTRL-SHIFT-ENTER to enter your formula again. Just using Enter will not work.

 

[zone709]

works thanks I had to change the A99 to A999 and that worked. Cheers

 

[AliGW]

I changed A99 to A999 in the formula I gave you in post #11 ...

 

[zone709]

Yes thsnk you