Thread: show names of ppl once (from a list of duplicates)

hello all

i have a list of ppl (they have multiple entries)

i tried this formula however it is listing the people's name twice on some instance.....the reason im using this formula is bc the list will expand as ppl will add their names in....so im hoping this formula can keep picking the names once....pls help!!

*see attached*

=IF(ROWS($5:5)>COUNTIF(Master!$H$2:$H$463,Calculations!$A$4),NA(),INDEX(Master!$K$2:$K$463,SMALL(IND EX(ROW(Master!$H$2:$H$463)+(Master!$H$2:$H$463<>Calculations!$A$4)*10^10,0),ROWS($5:5))))

Hi jw01,
I don't think I understand the question. Do you want to count the number of times a persons name appears in the list? If so you could do a simple Countif() function where the range is the whole column.

If you are dealing with expanding data that grows, the normal answer is to do that with a Dynamic Named Range in your formula. See
http://www.beyondtechnology.com/geeks007.shtml or http://www.ozgrid.com/Excel/DynamicRanges.htm

If you are trying to select the correct row a name is in then you may need a helper column to count the number of the same names above each name.

hope this helps

You have another option - You can either -
- Copy your columns with names to a different sheet/workbook and perform a Remove Duplicates action on it (Data -> Remove Duplicates)
or
-Copy your columns with names to a different sheet/workbook and perform a Advanced Filter action on it (Data -> Advanced -> select Copy to another location / Filter list in place-> select List range & criteria range -> Unique records only.

If this is too manual to repeat each time your data changes, you can have a macro setup to do it. At a click of a button, you can get it done.

Did you try or learn Dynamic Named Ranges?
Do you want a helper column to count the number of times a name appears?
Is an event macro anything you would consider or do you want a relative simple Excel worksheet function?

Try:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Master!$K$2:$K$463,MATCH(0, INDEX(COUNTIF($B$4:$B4,Master!$K$2:$K$463),0),0))))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down...

convert the K2:K463 to a dynamic range if you want to add names on the fly...

This one eliminates any blank rows within the data

=LOOKUP(REPT("z",10),CHOOSE({1,2},"",INDEX(Master!$K$2:$K$463,MATCH(0, INDEX((Master!$K$2:$K$463<>"")*(COUNTIF($B$4:$B4,Master!$K$2:$K$463)),0),0))))

you are right... but, unfortunately I've gotta take off... so I'll have get back to it later... or you can either not have them in the original data or live with it as one of the returned results... otherwise, if nobody comes up with it, I will look at it over the weekend....

if you are looking for extract unique names, this should work with CTRL+SHIFT+ENTER

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Name_,MATCH(1,IF(Name_<>"",IF(ISNA(MATCH(Name_,B$4:B4,0) ),1)),0))))

copy down.