List Lookup

[ScabbyDog]

I have a list of names in Column A. The names can appear numerous times. I want a formula that looks at column A and returns all listed names, but only to return the name once. So basically, if the name appears in column A, show me the name but show me it once only.

[Pete_UK]

Assuming your data starts on row 2, with a header in row 1, you can use this formula in B2:

=INDEX($A$2:$A$55,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$55),0,0),0))

Change the 55 to suit your data, then copy down.

Hope this helps.

Pete

[excelliot]

Hi

Use this formula in B2 & drag down:

Please Login or Register  to view this content.

Cheers!!

[Richard Buttrey]

Hi,

Rather than a formula I'd be inclined to use an Advanced Filter and tick the 'Unique' option. Or alternatively copy the list to another column and use the 'Remove Duplicates' option on the standard Ribbon menu.

[ScabbyDog]

Thanks Guys. BOth work great. How can I then go one step further and in the new list I've generated from your formula above, lookup the name and add the cell to the right of the name every time it appears.

So column A is the list of names, column B is a figure, column C is now the list we've generated from the formula and column D should we the total figure added from each time the name in column C appears in column A.

[Pete_UK]

Put this in D2:

=SUMIF(A:A,C2,B:B)

then copy down.

Hope this helps.

Pete

[Glenn Kennedy]

=sumif(A:A,C1,B:B) should do that for you

[ScabbyDog]

Excellent. Cheers.

[Glenn Kennedy]

You're welcome... and thanks for the feedback.

[ScabbyDog]

Ran into a problem with the formula....

Column A Row 1 my list of names start

I'm putting the index/match formula into E1 and dragging down but it keeps returning "0".

This is the formula I'm using in E1:

=INDEX($A$1:$A$210,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$1:$A$210),0,0),0))

[ScabbyDog]

Nevermind, was a circular reference issue. Solved.