Counting names in a list

[ChIpStIcK]

Hi, this feels like it should be easy but I can't work it out so have resorted to begging:

I have a column of random names, say 200 of them in column A. I want to have column D show a name if (and only if) it appears more than once and have a count of how many times it appears in column E.

I can't use vba. Please help!

 

[PaddyD]

Hi. Welcome to the board.

There are complex formula systems that will do this, but a fairly straightforward way is:

1) add a new column to the data that flags if a name turns up more than once:

=counitf(a1,$a$1:$a$200)>1

2) set up a pivot table, include the new variable as a page field. only include TRUEs in the pivot.

quicker to do than explain.

 

[T. Valko]

Hi, this feels like it should be easy but I can't work it out so have resorted to begging:

I have a column of random names, say 200 of them in column A. I want to have column D show a name if (and only if) it appears more than once and have a count of how many times it appears in column E.

I can't use vba. Please help!

What version of Excel are you using?

 

[vaskov17]

Not the best way to do this but:

1. In D1:

=IF(COUNTIF(A:A,A1)>1,A1,"")

2. In D2 and drag down to the end of the list:

=IF(COUNTIF($D$1:D1,A2),"",IF(COUNTIF(A:A,A2)>1,A2,""))

3. In E1 and drag down to the end of the list:

=IF(LEN(D1)>0,COUNTIF(A:A,D1),"")

4. Copy columns D and E and sort

 

[ChIpStIcK]

Thanks hugely for the amazingly quick responses there. Its Excel 2002. I havnt had a chance to try these out but will let you know how I get on.

 

[T. Valko]

What version of Excel are you using?

Its Excel 2002.

Try this...

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	_	_	Karen	2
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	Karen	_	_	_	_
20	Karen	_	_	_	_
Sheet1

Enter this array formula** in C2. This will return the count of duplicates.

=SUM(IF(FREQUENCY(MATCH(A2:A20,A2:A20,0),ROW(A2:A20)-ROW(A2)+1)>1,1))

Enter this array formula** in D2:

=IF(ROWS(D$2:D2)>C$2,"",INDEX(A:A,SMALL(IF(FREQUENCY(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))))

Enter this formula in E2:

=IF(D2="","",COUNTIF(A:A,D2))

Select both D2 and E2 and copy down until you get blanks.

** 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.

 

[ChIpStIcK]

That's a jaw dropper, wish there was an emoticon, hang on...http://www.mrexcel.com/forum/images/smilies/icon_eek.gif Maybe eye popper more appropriate. Getting old ver of Excel to try this at home.

 

[ChIpStIcK]

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.

 

[T. Valko]

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	_	_	_	_	_
Sheet2

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.

 

[ChIpStIcK]

Mind pasting me your second line of copy downs? Cant get it to show anything but the first dupe name and I can't work out what I should be changing for each line.