Count number of multiples/duplicates in a column

[Leebc]

Hi.
I need help to develop a script that will search through a field (Member Column) identify and then total up the number of times the member has entered the building. A new record is created for each entry.

I have tried a number of the suggestions for similar searches in the forum but cannot get it to work. I have managed to identify when a member enters more than once (Multi Entry) but now need to list the number of Multi Entries per member.

[TABLE="width: 274"]
<tbody>[TR]
[TD]Member
[/TD]
[TD]Multi Entry
[/TD]
[TD]Record ID
[/TD]
[/TR]
[TR]
[TD]10320
[/TD]
[TD]TRUE
[/TD]
[TD]593
[/TD]
[/TR]
[TR]
[TD]10320
[/TD]
[TD]TRUE
[/TD]
[TD]1322
[/TD]
[/TR]
[TR]
[TD]10918
[/TD]
[TD]TRUE
[/TD]
[TD]594
[/TD]
[/TR]
[TR]
[TD]10918
[/TD]
[TD]TRUE
[/TD]
[TD]1323
[/TD]
[/TR]
[TR]
[TD]10921
[/TD]
[TD]FALSE
[/TD]
[TD]1131
[/TD]
[/TR]
[TR]
[TD]11010
[/TD]
[TD]FALSE
[/TD]
[TD]1132
[/TD]
[/TR]
[TR]
[TD]11106
[/TD]
[TD]FALSE
[/TD]
[TD]595
[/TD]
[/TR]
[TR]
[TD]11107
[/TD]
[TD]FALSE
[/TD]
[TD]596
[/TD]
[/TR]
[TR]
[TD]11287
[/TD]
[TD]FALSE
[/TD]
[TD]1133
[/TD]
[/TR]
[TR]
[TD]17081
[/TD]
[TD]FALSE
[/TD]
[TD]1144
[/TD]
[/TR]
[TR]
[TD]1711
[/TD]
[TD]FALSE
[/TD]
[TD]1107
[/TD]
[/TR]
[TR]
[TD]1712
[/TD]
[TD]FALSE
[/TD]
[TD]1108
[/TD]
[/TR]
[TR]
[TD]1726
[/TD]
[TD]TRUE
[/TD]
[TD]448
[/TD]
[/TR]
[TR]
[TD]1726
[/TD]
[TD]TRUE
[/TD]
[TD]1109
[/TD]
[/TR]
[TR]
[TD]1727
[/TD]
[TD]TRUE
[/TD]
[TD]449
[/TD]
[/TR]
[TR]
[TD]1727
[/TD]
[TD]TRUE
[/TD]
[TD]1110
[/TD]
[/TR]
[TR]
[TD]1727
[/TD]
[TD]TRUE
[/TD]
[TD]604
[/TD]
[/TR]
[TR]
[TD]17441
[/TD]
[TD]TRUE
[/TD]
[TD]605
[/TD]
[/TR]
[TR]
[TD]17441
[/TD]
[TD]TRUE
[/TD]
[TD]606
[/TD]
[/TR]
[TR]
[TD]17648
[/TD]
[TD]FALSE
[/TD]
[TD]1145
[/TD]
[/TR]
[TR]
[TD]17845
[/TD]
[TD]FALSE
[/TD]
[TD]607
[/TD]
[/TR]
[TR]
[TD]18139
[/TD]
[TD]FALSE
[/TD]
[TD]608
[/TD]
[/TR]
[TR]
[TD]18431
[/TD]
[TD]TRUE
[/TD]
[TD]609
[/TD]
[/TR]
[TR]
[TD]18431
[/TD]
[TD]TRUE
[/TD]
[TD]610
[/TD]
[/TR]
[TR]
[TD]18461
[/TD]
[TD]FALSE
[/TD]
[TD]1004
[/TD]
[/TR]
[TR]
[TD]1883
[/TD]
[TD]FALSE
[/TD]
[TD]993
[/TD]
[/TR]
[TR]
[TD]18863
[/TD]
[TD]FALSE
[/TD]
[TD]1005
[/TD]
[/TR]
[TR]
[TD]1924
[/TD]
[TD]FALSE
[/TD]
[TD]564
[/TD]
[/TR]
[TR]
[TD]19350
[/TD]
[TD]TRUE
[/TD]
[TD]611
[/TD]
[/TR]
[TR]
[TD]19350
[/TD]
[TD]TRUE
[/TD]
[TD]1327
[/TD]
[/TR]
[TR]
[TD]1945
[/TD]
[TD]FALSE
[/TD]
[TD]994
[/TD]
[/TR]
</tbody>[/TABLE]


The output I require is:

[TABLE="width: 274"]
<tbody>[TR]
[TD]Member
[/TD]
[TD]Multi Entry
[/TD]
[TD]Record ID
[/TD]
[/TR]
[TR]
[TD]10320
[/TD]
[TD]2
[/TD]
[TD]593
[/TD]
[/TR]
[TR]
[TD]10320
[/TD]
[TD]2
[/TD]
[TD]1322
[/TD]
[/TR]
[TR]
[TD]10918
[/TD]
[TD]2
[/TD]
[TD]594
[/TD]
[/TR]
[TR]
[TD]10918
[/TD]
[TD]2
[/TD]
[TD]1323
[/TD]
[/TR]
[TR]
[TD]10921
[/TD]
[TD]1
[/TD]
[TD]1131
[/TD]
[/TR]
[TR]
[TD]11010
[/TD]
[TD]1
[/TD]
[TD]1132
[/TD]
[/TR]
[TR]
[TD]11106
[/TD]
[TD]1
[/TD]
[TD]595
[/TD]
[/TR]
[TR]
[TD]11107
[/TD]
[TD]1
[/TD]
[TD]596
[/TD]
[/TR]
[TR]
[TD]11287
[/TD]
[TD]1
[/TD]
[TD]1133
[/TD]
[/TR]
[TR]
[TD]17081
[/TD]
[TD]1
[/TD]
[TD]1144
[/TD]
[/TR]
[TR]
[TD]1711
[/TD]
[TD]1
[/TD]
[TD]1107
[/TD]
[/TR]
[TR]
[TD]1712
[/TD]
[TD]1
[/TD]
[TD]1108
[/TD]
[/TR]
[TR]
[TD]1726
[/TD]
[TD]2
[/TD]
[TD]448
[/TD]
[/TR]
[TR]
[TD]1726
[/TD]
[TD]2
[/TD]
[TD]1109
[/TD]
[/TR]
[TR]
[TD]1727
[/TD]
[TD]3
[/TD]
[TD]449
[/TD]
[/TR]
[TR]
[TD]1727
[/TD]
[TD]3
[/TD]
[TD]1110
[/TD]
[/TR]
[TR]
[TD]1727
[/TD]
[TD]3
[/TD]
[TD]604
[/TD]
[/TR]
[TR]
[TD][/TD]

[TD][/TD]
[/TR]
</tbody>[/TABLE]
etc.

 

[Trebor76]

Hi Leebc,

Do you really need code to do this? Based on your first post I'll assume that the range is A1:C32 with headings in Row 1. As such you could put this formula into cell B2...

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

...and copy it down to cell B32.

It's good to remember that native Excel formulas are more efficient than code.

HTH

Robert

 

[Leebc]

Thanks for your help Robert.

To add a further dimension.
Lets say I have 4 different locations: North, South, East West. I now want to count the number of attendances by a member at each location.

Here's the issue: Each location issues the same range of member numbers which means that the same membership number may exist for multiple locations.

I want to list the number of attendances by a member for a location.

I hope my explanation makes sense?

[TABLE="width: 365"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Location:[/TD]
[TD]Member[/TD]
[TD]Multi Entry[/TD]
[TD]Record ID[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]10320[/TD]
[TD]3[/TD]
[TD]593[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]10320[/TD]
[TD]2[/TD]
[TD]1322[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]10918[/TD]
[TD]4[/TD]
[TD]594[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]10918[/TD]
[TD]2[/TD]
[TD]1323[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]10921[/TD]
[TD]5[/TD]
[TD]1131[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]11010[/TD]
[TD]3[/TD]
[TD]1132[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]11106[/TD]
[TD]1[/TD]
[TD]595[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]11107[/TD]
[TD]1[/TD]
[TD]596[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]11287[/TD]
[TD]1[/TD]
[TD]1133[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]17081[/TD]
[TD]1[/TD]
[TD]1144[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]1711[/TD]
[TD]1[/TD]
[TD]1107[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]1712[/TD]
[TD]1[/TD]
[TD]1108[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]1726[/TD]
[TD]1[/TD]
[TD]448[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]1726[/TD]
[TD]1[/TD]
[TD]1109[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]1727[/TD]
[TD]3[/TD]
[TD]449[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]1727[/TD]
[TD]1[/TD]
[TD]1110[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]1727[/TD]
[TD]2[/TD]
[TD]604[/TD]
[/TR]
</tbody>[/TABLE]


Output:
North:
[TABLE="width: 365"]
<tbody>[TR]
[TD]North[/TD]
[TD]10320[/TD]
[TD]3[/TD]
[TD]593[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<tbody>[TR]
[TD]North[/TD]
[TD]1726[/TD]
[TD]1[/TD]
[TD]448[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<tbody>[TR]
[TD]North[/TD]
[TD]11010[/TD]
[TD]3[/TD]
[TD]1132
[/TD]
[/TR]
</tbody>[/TABLE]

South:
[TABLE="width: 365"]
<tbody>[TR]
[TD]South[/TD]
[TD]10918[/TD]
[TD]4[/TD]
[TD]594[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<tbody>[TR]
[TD]South[/TD]
[TD]11106[/TD]
[TD]1[/TD]
[TD]595[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<tbody>[TR]
[TD]South[/TD]
[TD]1711[/TD]
[TD]1[/TD]
[TD]1107[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<tbody>[TR]
[TD]South[/TD]
[TD]1727[/TD]
[TD]1[/TD]
[TD]1110[/TD]
[/TR]
</tbody>[/TABLE]

East:
[TABLE="width: 365"]
<tbody>[TR]
[TD]East[/TD]
[TD]10320[/TD]
[TD]2[/TD]
[TD]1322[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<tbody>[TR]
[TD]East[/TD]
[TD]10918[/TD]
[TD]2[/TD]
[TD]1323[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]10921[/TD]
[TD]5[/TD]
[TD]1131[/TD]
[/TR]
</tbody>[/TABLE]
etc.

 

[Trebor76]

Just put a filter across A1:D1 and filter column A by the region you are interested in.

 

[snjpverma]

Sheet1

*	F	G	H
Location:	Multi Entry	Member
East
East
East
East
East

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]10320[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]10918[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10921[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]11287[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1727[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Try the below formula as it gave me the desires output.
=SUMIFS($C$2:$C$18,$A$2:$A$18,$F2,$B$2:$B$18,$H2) --(Entered in G2 and copied down)

 

[Leebc]

Thanks for your help.

Robert how would I put the resultant values for the locations in a list (column) that I can use further?

 

[Trebor76]

See if these seven steps help:

1. Highlight the entire rows that comprise the filtered range
2. Press the F5 keyboard button
3. Click the Special button on the Go To dialog
4. From the Go To Special dialog select the Visible cells only option (radio button) and then click OK
5. Copy this range to the clipboard (Ctrl + C)
6. Select the first entire row of where you want the output to start
7. Paste the range (Ctrl + V)

HTH

Robert

PS - it's best to start a new thread for each new question