How to located repeated names in an Excel sheet?

Question

I need to count the number of repeated names in an Excel sheet, but there are over a 1000 names!! I'd be dead by then... any help to do that automatically?

Answer 1

ok, heres what you do



lets say that from a2:a1000 is th column you have will all these different names



in b2 type

=IF(A2<>0,

COUNTIF($A$2:A2,"="&A2),0)



it's on 2 separate line because yahoo wouldn't display it all but the countif part should be right after the "," in the first part







after you enter that formula, copy b2 and paste down to b1000. what this does is it looks at a2 and counts how many times the value of a2 is found above it. so this obliously will be 1, lets say the the next name is different than the first one, it will also be listed as 1 because the first one is different. if the third one = the first name or the second name, it would then be 2 because it is the second time that name occured, and so on and so on. now select column a, go to format > conditional format, click on formula is and type =if(b1>1,1,0) and then click on the format button, click patterns tab and change the color to blue or whatever color you want it to show when there is a duplicate. hope this helps.



feel free to e-mail me with questions.

Answer 2

1) Highlight the row of names

2) Go to Data in the top bar where File, Edit and others are.

3) Choose Sort

4) Choose either Ascending or Descending

Answer 3

Do Control+H to find and replace the repeated names in excel.

Alternatively, you can sort the names alphabetically and just delete the duplicates.

Answer 4

hi, i'm uncertain which excel version you're utilising. i'm stable at 2003 and might make it easier to at this volume yet once you are able to detect the 2007 version, i'm specific you will detect the respond by way of fact the 2007 version can detect cells in step with its colorings. 2003, has a smart hindrance that it cant detect cells in step with colorings and supply values to the cells or any cells next to it. in spite of the undeniable fact that it could make it easier to discover the cells and you will manually upload the values. For the example you have given in question as A1 for a million and A2 for 2. Excel has given such numbering to its colorings by utilising default and it incredibly is termed as shade index. Open a sparkling excel workbook and press "Alt + F11". on your left below "project - VBA project" you will discover "Sheet a million", double click it and you will detect a white show screen. Paste the below code there and press F5 and you will discover each and all the colours and shade indexes in Sheet1 in excel. Public Sub Colors_Numbers() Worksheets(a million).turn on Cells(a million, a million).fee = "shade" Cells(a million, 2).fee = "shade Index variety" For n = a million To fifty six Cells(n + a million, a million).indoors.ColorIndex = n Cells(n + a million, 2).fee = n next n end Sub Now you recognize what shade is what index. yet once you're finding a manner of filtering the cells in step with colorings then such isn't obtainable in 2003. in spite of the undeniable fact that, you will discover cells that are colored and then bypass to that cellular and make any differences to it. Press "Ctrl + f" and then click the ideas button. There would be a format button and a small arrow on the top of the button click that arrow and you click "choose format From cellular" and now choose the cellular which includes the "Yellow" shade and press "discover All". what excel does now is it is going to discover each and all the cells that have the yellow shade, by utilising retaining the discover field open you are able to bypass the cells and supply any variety. yet in 2007 this function has been progressed. you are able to clear out the cells in step with colorings. each and all the proper. Cheers Abdul.

Answer 5

If you press Ctrl+F it will find the name that you type in. Then you can just keep track of how many times it comes up.