0

Suppose I have multiple, repeating names as follows:

John
John 
John
John
John
Tom
Tom
Tom
Tom
Tom
Tom
Anna
Anna
Anna
Anna
Anna
Anna

I would like to find a way to tell Excel: while the name is John, Tom, Anna, and so on, count 1,2,3,4... up to the last instance of that name. Repeat the count for each distinct name in the list. Keep in mind that there are 100s of names so any solution will ideally not rely on pinpointing the exact name. I was thinking of running an =IF(A1=A2,Do something, do something else) but I realized this is not robust enough. Here is the output for the above sample:

John 1
John 2
John 3
John 4
John 5
Tom  1 
Tom  2
Tom  3
Tom  4 
Tom  5 
Tom  6 
Anna 1
Anna 2
Anna 3
Anna 4
Anna 5
Anna 6
  • @CharlieRB: "What have you tried" is not an acceptable comment. Please guide the user towards improving the answer instead. – bwDraco Dec 18 '14 at 5:16
  • 99% of my comments are guiding towards improvement. You happen to catch the one I thoughtlessly toss out there. ;-) – CharlieRB Dec 18 '14 at 12:16
5

In B1 enter 1

In B2 enter:

=COUNTIF($A$1:A2,A2)

and copy down.

For example:

enter image description here

0

This may not be the most elegant solution, but you can do something like this:

  1. Put the number "1" (no quotes) in the second column of the first row.

    Step 1

  2. Then in the second column of the second row put this formula =IF(EXACT(TRIM(A1),TRIM(A2)),B1+1,1) and hit enter.

    enter image description here

  3. Next drag the lower right corner of that cell (expanding the formula) to the end of your data.

Note: This will only work if all of the names are right next to each other, as they are in your example. E.g. this will not work:

John
Tom
John
Tom

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.