Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
Hi,
What version of Excel are you using? This is a piece of cake in 2007. -- Thanks, Shane Devenshire "h20polo" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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. InsertNameDefineName Guests Refers to =OFFSET($A$1,0,0,COUNTA($A:$A)) Then, DataPivot TableNextRange =GuestsNextLayout 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 CountRemove 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. -- Regards Roger Govier "h20polo" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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. -- Cheers, Shane Devenshire "h20polo" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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. -- Regards Roger Govier "ShaneDevenshire" wrote in message ... 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. -- Cheers, Shane Devenshire "h20polo" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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? "Roger Govier" wrote: 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. InsertNameDefineName Guests Refers to =OFFSET($A$1,0,0,COUNTA($A:$A)) Then, DataPivot TableNextRange =GuestsNextLayout 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 CountRemove 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. -- Regards Roger Govier "h20polo" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
2003.
"ShaneDevenshire" wrote: Hi, What version of Excel are you using? This is a piece of cake in 2007. -- Thanks, Shane Devenshire "h20polo" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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 DataListCreate List. (This means the list will grow automatically as you add more names, without having to create a dynamic range) Then DataPivot TableFinish 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. -- Regards Roger Govier "h20polo" wrote in message ... 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? "Roger Govier" wrote: 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. InsertNameDefineName Guests Refers to =OFFSET($A$1,0,0,COUNTA($A:$A)) Then, DataPivot TableNextRange =GuestsNextLayout 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 CountRemove 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. -- Regards Roger Govier "h20polo" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
2003.
"ShaneDevenshire" wrote: Hi, What version of Excel are you using? This is a piece of cake in 2007. -- Thanks, Shane Devenshire "h20polo" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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" wrote: 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. InsertNameDefineName Guests Refers to =OFFSET($A$1,0,0,COUNTA($A:$A)) Then, DataPivot TableNextRange =GuestsNextLayout 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 CountRemove 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. -- Regards Roger Govier "h20polo" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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. -- Regards Roger Govier "h20polo" wrote in message ... 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" wrote: 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. InsertNameDefineName Guests Refers to =OFFSET($A$1,0,0,COUNTA($A:$A)) Then, DataPivot TableNextRange =GuestsNextLayout 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 CountRemove 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. -- Regards Roger Govier "h20polo" wrote in message ... 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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! "h20polo" wrote: 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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding up number of repeated names
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. -- Regards Roger Govier "h20polo" wrote in message ... 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! "h20polo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do not allow a number to be repeated on the same column? | Excel Discussion (Misc queries) | |||
using conditional formatting to mark repeated names in list? | Excel Discussion (Misc queries) | |||
max number repeated | Excel Discussion (Misc queries) | |||
how do I number a form for repeated use? | Excel Discussion (Misc queries) | |||
Most repeated number | Excel Worksheet Functions |