Filter names with sheet name

[Gazzal]

I have 18 years of a pool comp results and I would like to normalize the names (col A).

Is there a way to pull all unique names with the sheet name (year) attached into another sheet so I can then go to each sheet and correct the duplicate names, eg; P Smith, Pete Smith, P J Smith, Peter Smith is the same person but noted diff. over the years.

 

[Luke M]

You can use the Advanced Filter to generate a list of unique items from within a list. This feature also gives you the ability to copy the list to different location. You could do this for each sheet, and fill in sheet names Yourself via this method:


1. Enter sheet name into first blank cell in col B

2. Double-click on bottom right corner of that cell to extend value to bottom of list

3. Run Advanced Filter on next sheet, copy to next empty cell in col A

4. Repeat 1-3 as necessary

 

[Gazzal]

Thanks Luke M

I've played with the advanced filter but it drives me nuts. I get dialogs galore thrown at me like 'database or list range not valid' or 'Excel cannot determine which row or selection contains column labels' or something about 'not being the active sheet' etc etc.

In saying that, thru the fog, I have managed 'somehow' to get a couple of pages filtered in about 2 hours of agony, so I'll see what I can do with them

 

[XLinXL]

Hi Gazzal,


Assuming you have all the names in Column A, with a Column Header as say "Names" or "Member Names" or anything else.


Select Column A


Go to Data > Advanced


In case you get the following error click on "OK"

"Microsoft Office Excel cannot determine which row in your list or selection contains column labels, which are required for this command"


Click on radio button "Copy to another location"


Give any reference in "Copy to:" box, say "P1"


Check on "Unique records only"


Click on "OK"


All the unique names will be copied to column P, starting cell P1.