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

 

[Joe4]

Just use the Excel "Remove Duplicates" functionality found on the Data menu.

 

[zone709]

ok that's one way of doing it, but I'm pulling all the names in first with formulas only needing name once.

 

[zone709]

hi the duplicate function is good, but you see I'm puling it in from another sheet. So if I have lets says rates like this.

26.30
26.30
26.30
59.80
59.80
26.30
26.30

Mt results I need to be
26.30
59.80
26.30

With using this function it will.
26.30
59.80

Why I thought at first I would need a formula to pull it in.

 

[Joe4]

I have seen people come up with some pretty complex formulas to do that, but that isn't really my forte.
If I had to do something like this, I would probably create VBA code to do it.

 

[zone709]

​agree

 

[Eric W]

With this Flush sheet:

A B C 1 2 3 4 5 6 7 8 9 10 11 12 Name Name 13 Espo Espo 14 Espo Rivera 15 Espo Len 16 Espo Rivera 17 Rivera Len 18 Rivera Espo 19 Rivera Mike 20 Rivera Joe 21 Rivera Espo 22 Len 23 Len 24 Len 25 Len 26 Rivera 27 Len 28 Espo 29 Mike 30 Mike 31 Mike 32 Joe 33 Espo 34 Espo 35 <colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead> </thead><tbody> </tbody> Flush​ Array Formulas Cell Formula C13 {=IFERROR(INDEX(Flush!$A$13:$A$100,SMALL(IF(Flush!$A$13:$A$100<>Flush!$A$12:$A$99,ROW($A$13:$A$100)-ROW($A$13)+1),ROWS($A$13:$A13))),"")&""} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself <tbody> </tbody>

<tbody>
</tbody>

This is an array formula. Enter the formula in the formula bar, change the ranges to match your workbook (notice the offset on the 3rd range), and confirm with Control+Shift+Enter. Now drag it down the column.

Let us know if this is what you're looking for.

 

[Joe4]

Before I go ahead and come up with a VBA solution, does Eric's solution work for you?

 

[zone709]

Eric this is great thanks so much. Joe this helps out a lot, but if I run into a situation that I didn't mention. Then ill come back here to see about a VBA solution. Thanks again.

 

[zone709]

Hi so this formula works great but ran into an issue. When I scroll it down the Flush sheet has like a 1000 rows of data. So what I tried to do is change the range from $A$100 to $A$1000. When I do this to all it returns blank. I feel like its something small to fix this issue and been trying everything, but still cant find solution or understand why it wont work. Thanks