Counting multiple occurences of names

Enduser · Aug 10, 2003

I need to count the number of times a name appears in a given cell, but only those that appear greater than 2 times. Additionally, I need to extract those names into a separate spreadsheet. The problem is, I don't yet know the names that will be entered. I know this is fairly easy with pivot tables, but I'm hoping to come up with a formula so that the names and numbers will update automatically anytime the name appears more than twice. Is this possible?

Ricky Morris · Aug 10, 2003

Do you mean how many times a name occur in a cell or a range?

Enduser · Aug 10, 2003

yes, I should have said number of occurrences in a given column. Sorry bout that.

just_jon · Aug 10, 2003

The counting of instances within a column can be handled easily with a formula, but the grouping of those > 2 to another sheet might require a VBA solution. As a starter, to count the instances of names in column A, rows 2 thru 200, in B2, dragged down to B200, enter --

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

This formula will put a 1 in column B for a 1st instance, 2 for a 2nd, etc. You could then either sort the data on column B, and copy the chunk of >2 to the other sheet, or use a macro to extract those rows to the other sheet, keying on values in B > 2 only.

Aladin Akyurek · Aug 10, 2003

Book1

A

B

C

D

E

F

G

1

19

17

4

Summary

2

Name

Sort

Rank

Name

Freq

3

jill

4

jon

5

damon

6

damon

jon

3

7

jill

8

jon

9

jill

10

jon

11

carol

1

12

piero

13

damon

14

damon

15

carol

16

lucas

17

thomas

18

thomas

19

carol

Sheet1

A1:

=MATCH(REPT("z",255),A:A)

B1:

=A1-(CELL("Row",A3)-1)

B3, which is copied down...

=IF((COUNTIF($A$3:INDEX(A:A,$A$1),A3)>2)*ISNA(MATCH(A3,$A$2:A2,0)),SUMPRODUCT((A3>OFFSET($A$3,0,0,$B$1,1))+0)+1,"")

C1:

=COUNT($C$3:INDEX(C:C,A1))

C3, which is copied down...

=IF(B3<>"",RANK(B3,$B$3:INDEX(B:B,$A$1),1)+COUNTIF($B$3:B3,B3)-1,"")

E3, which is copied down...

=IF(ROW()-ROW($E$3)+1<=$C$1,INDEX($A$3:INDEX(A:A,$A$1),MATCH(ROW()-ROW($E$3)+1,$C$3:INDEX(C:C,$A$1),0)),"")

F3, which is copied down...

=IF(E3<>"",COUNTIF($A$3:INDEX(A:A,$A$1),E3),"")

Enduser · Aug 10, 2003

Nicely done, thanks Aladin.

Enduser · Aug 13, 2003

This works great, but I have one more question. is there a way to show only the top ten names with > 2 occurrences?

PaddyD · Aug 13, 2003

any reason a simple custom filter on the results area wont do?

Enduser · Aug 13, 2003

I need the list to show only the top ten names (with the most occurrences) but none with less than 2. The difficulty is that I need it to happen automatically, without the user doing any sorting or filtering on their own. Also, I have no way to know how many names might be listed (that is why I needed to set it up so the names update automatically. Is it possible to do this with a custom filter?