Wont let me edit after 10 minutes so I'll reply again.
Trying it out I have a couple of problems:
1) In the example above, if say cells A20 and A19 are blank, then the formulas return value not available errors. This is problematic as I wont know how many names are on the list (people will be adding two or three every day for a month and there's a new sheet each month is how it works if you're interested).
2) When I copy down I get the same name with the same count in D and E, although I do get the right number of lines for the number of dupes in the list before it goes blank. Which is to say, in the above example "Tom 4" would be in rows 2-6 then it would, as it should, go blank.
To account for empty cells...
| Book1 |
|---|
|
|---|
| A | B | C | D | E |
|---|
| 1 | Names | _ | Dupes | Names | Count |
|---|
| 2 | Tom | _ | 5 | Tom | 4 |
|---|
| 3 | Tom | _ | _ | Larry | 5 |
|---|
| 4 | Tom | _ | _ | Kim | 2 |
|---|
| 5 | Tom | _ | _ | Eric | 2 |
|---|
| 6 | Lisa | _ | _ | _ | _ |
|---|
| 7 | Sue | _ | _ | | _ |
|---|
| 8 | Larry | _ | _ | _ | _ |
|---|
| 9 | Larry | _ | _ | _ | _ |
|---|
| 10 | Larry | _ | _ | _ | _ |
|---|
| 11 | Larry | _ | _ | _ | _ |
|---|
| 12 | Larry | _ | _ | _ | _ |
|---|
| 13 | Kim | _ | _ | _ | _ |
|---|
| 14 | Kim | _ | _ | _ | _ |
|---|
| 15 | Eric | _ | _ | _ | _ |
|---|
| 16 | Eric | _ | _ | _ | _ |
|---|
| 17 | Paul | _ | _ | _ | _ |
|---|
| 18 | Sam | _ | _ | _ | _ |
|---|
| 19 | _ | _ | _ | _ | _ |
|---|
| 20 | _ | _ | _ | _ | _ |
|---|
|
|---|
This array formula** entered in C2:
=SUM(IF(FREQUENCY(IF(A2:A20<>"",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1)>1,1))
This array formula** entered in D2:
=IF(ROWS(D$2:D2)>C$2,"",INDEX(A:A,SMALL(IF(FREQUENCY(IF(A$2:A$20<>"",MATCH(A$2:A$20,A$2:A$20,0)),ROW(A$2:A$20)-ROW(A$2)+1)>1,ROW(A$2:A$20)),ROWS(D$2:D2))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
This formula entered in E2:
=IF(D2="","",COUNTIF(A:A,D2))
Select both D2 and E2 and copy down until you get blanks.
Maybe eye popper more appropriate. Getting old ver of Excel to try this at home.
