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.

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

<tbody>
</tbody>

The output I require is:

Member	Multi Entry	Record ID
10320	2	593
10320	2	1322
10918	2	594
10918	2	1323
10921	1	1131
11010	1	1132
11106	1	595
11107	1	596
11287	1	1133
17081	1	1144
1711	1	1107
1712	1	1108
1726	2	448
1726	2	1109
1727	3	449
1727	3	1110
1727	3	604

<tbody>
</tbody>

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?

Location:	Member	Multi Entry	Record ID
North	10320	3	593
East	10320	2	1322
South	10918	4	594
East	10918	2	1323
East	10921	5	1131
North	11010	3	1132
South	11106	1	595
West	11107	1	596
East	11287	1	1133
West	17081	1	1144
South	1711	1	1107
West	1712	1	1108
North	1726	1	448
West	1726	1	1109
East	1727	3	449
South	1727	1	1110
East	1727	2	604

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Output:
North:

North

10320

3

593

<tbody>
</tbody>

North

1726

1

448

<tbody>
</tbody>

North

11010

3

1132

<tbody>
</tbody>

South:

South

10918

4

594

<tbody>
</tbody>

South

11106

1

595

<tbody>
</tbody>

South

1711

1

1107

<tbody>
</tbody>

South

1727

1

1110

<tbody>
</tbody>

East:

East

10320

2

1322

<tbody>
</tbody>

East	10918	2	1323
East	10921	5	1131

<tbody>
</tbody>

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
1	Location:	Multi Entry	Member
2	East	2	10320
3	East	2	10918
4	East	5	10921
5	East	1	11287
6	East	5	1727

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</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