adding up number of repeated names

[h20polo]

I have a compiled list of all the guests that have attended different events
that we have held in the last couple of years. We are trying to figure out
the loyalty of our guests; identifying those that have attended 2 or more
events. The guest names are in cells A3:A988. How do I come up with a formula
so that the output is the name of anyone who has attended more than one event
(meaning their name appears more than once in column A) and the number of
times they've attended an event, which is updated as more events/names are
added to the list?

Thanks so much.

 

[ShaneDevenshire]

Hi,

What version of Excel are you using? This is a piece of cake in 2007.

 

[Rick Rothstein \(MVP - VB\)]

I have a compiled list of all the guests that have attended different

events
that we have held in the last couple of years. We are trying to figure out
the loyalty of our guests; identifying those that have attended 2 or more
events. The guest names are in cells A3:A988. How do I come up with a
formula
so that the output is the name of anyone who has attended more than one
event
(meaning their name appears more than once in column A) and the number of
times they've attended an event, which is updated as more events/names are
added to the list?

This is a little different than what you asked for, but perhaps you will
find it useful. The way it works is you specify a name, and every occurrence
of that name will be flagged along with the number of total occurrences.
While I realize you probably have other columns filled in, this solution
assumes that column B will show the flagged rows and that cell C1 will be
where you specify the name to be searched for. Place this formula...

=IF(A1=C$1,COUNTIF(A$1:A$2000,"="&C$1),"")

in B1 and then copy it down through row 2000 (you can use a different
number, but I figured this would give you some growth potential). If you
want to specify a different maximum number of rows, just make sure you
change the 2000 in the formula to the maximum number of rows you will copy
the formula down through. Now, place the name you want to search on in C1
and hit the Enter key... each occurrence of that name will be flagged in
column B and the number shown is the total number of times the name occurs.

Rick

 

[Roger Govier]

Hi

One way
Assuming the data is just a list of names in column A and column B is
blank.
Assuming row 1 is a header with Name in A1 and Count in B1
In cell B2
=IF(COUNTIF($A$2:$A$1000,A2)>2,1,"")
This will put a 1 just against the names which occur more than twice.

Insert>Name>Define>Name Guests
Refers to =OFFSET($A$1,0,0,COUNTA($A:$A))

Then, >Data>Pivot Table>Next>Range =Guests>Next>Layout
Drag Count to Row area - double Click on Field name and set Subtotals
to None
Drag Name to Row area
Drag Name again to the Data area
Finish

On the new sheet created with the Pivot Table report, choose the
dropdown on Count>Remove check marks against everything other than 1

You will now have a list of your Guests who have attended more than
twice, along with the number of times they have attended.
As you add more names to the end of the list, the Dynamic Range Guests
will grow.

 

[ShaneDevenshire]

Hi,

No reason to go to a lot of work:

1. Select the range and choose Insert, Pivot Table, OK
2. Drag the Name field to the Row Labels area and the Values area.
3. Open the Name filter and choose Value Filters, Greater Than, and enter 1
and click OK.

You can handle expanding ranges by 1. starting with a larger range for the
pivot table, 2. inserting the new names between already existing ones or, 3.
best! define the source as a Table (in 2007 or List in 2003) - Ctrl T, OK.

The above applies to 2007.

In 2003 modify this approach:

1. In B1 enter a title, such as "Greater", and in cell B2 enter a formula
similar to one suggest earlier:
=COUNTIF($A$2:$A$1001,A2)>1
2. With this range selected press Ctrl L, OK. This defines this range as a
list.
3. Choose Data, PivotTable and PivotChart Report, Finish.
4. Drag the Name field to the Row area and the Data area, and drag the
Greater field to the left of the Name field in the Row area
5. Open the Greater than drop down and uncheck FALSE. Hide the Greater
column if needed.

No dynamic range name is needed with this solution, so if you are using 2003
or 2007 List/Table is the method of choice for handling expanding ranges.
This opinion regarding the use of List/Table is strictly mine, but I believe
that in time all users will realize their benefits.

 

[Roger Govier]

Hi Shane

You are absolutely right that 2003 Lists, or (better still) 2007 Tables
is definitely the way to go. No more defining Dynamic Ranges.

In the absence of knowledge about which version the OP is using, I tried
to give a solution that would work across all versions.

 

[h20polo]

Hey,
Thanks for the help. However, it only inputs the 1 if the name in A2 is
repeated, not any name in the entire column. Is there a simple way to do it
(I'm using 2003) with one formula, instead of copying it all the way down?

 

[h20polo]

2003.

Hi,

What version of Excel are you using? This is a piece of cake in 2007.

 

[Roger Govier]

Hi

I gave you a formula for names occurring more than twice. On re-reading
your OP I can see that you asked for names attending 2 or more events.
In that case, change the formula to
=IF(COUNTIF($A$2:$A$1000,A2)>1,1,"")

It sound now as if you are saying you want it for any name in the
column, but you don't want to copy the formula down.
I don't understand this part.

As you have 2003, you could use Shane's suggestion by placing your
cursor in column A and Data>List>Create List.
(This means the list will grow automatically as you add more names,
without having to create a dynamic range)
Then Data>Pivot Table>Finish
On the resulting PT template, drag Name to the Row area and drag Name
again to the Data area.
You will now have all your client names listed once, and alongside will
be the number of events they attended.

If I am not understanding what you want, then do post back again with
more details.

 

[h20polo]

2003.

Hi,

What version of Excel are you using? This is a piece of cake in 2007.

 

[h20polo]

Hi Roger,

Thanks for the suggestion. It works for one individual cell, and will show
up with a 1 if the cell i put in the place of A2 repeats. How do I get it to
do it for the entire column, without copying it down for each cell? I tried
changing the input to "A:A" but it didn't work....And then this was probably
just a result of the fact that the formula didn't work, but when I tried to
go to "Layout" for the PivotTable, I only had one thing that i could drag.

Thanks again!

 

[Roger Govier]

The formula has to be copied down the whole of column B, otherwise it
won't work.
To copy down, hover over the bottom right corner of the cell with the
formula, until it turns to a small black cross (the fill handle)
Double click the fill handle at it will copy down column B for the
extent of any data in column A.

If you have 2 column headings, Name and Count, and it the range given to
the Pivot Table includes columns A and B, then there will be two labels.

If you have only given the PT column A as its source data, then there
will only be one heading - Name.
This can be dragged to both the row area , and the data area.

 

[h20polo]

I've tried to reply a couple of times and it hasn't showed up, so I'm sorry
if it ends up showing up later.

Roger, thanks for your suggestion. As described, it is exactly what I want.
However, the first formula only works in an individual cell for an individual
input - how can I get it to be a formula for the entire column or copy it
down, changing it for each cell in the column? I don't know if my using Excel
2003 affects this or not.

Thanks!

 

[Roger Govier]

Hi

I sent you this reply yesterday

The formula has to be copied down the whole of column B, otherwise it
won't work.
To copy down, hover over the bottom right corner of the cell with the
formula, until it turns to a small black cross (the fill handle)
Double click the fill handle at it will copy down column B for the
extent of any data in column A.

If you have 2 column headings, Name and Count, and it the range given to
the Pivot Table includes columns A and B, then there will be two labels.

If you have only given the PT column A as its source data, then there
will only be one heading - Name.
This can be dragged to both the row area , and the data area.