Calculate number of duplicates (MrLenze answer is gone !!!)

ChrisTa

New Member
Hi everyone,

I am quite stuck with a Excel work

it contents about 40'000 entries and I need to count specifics things but including constraints.

Unique ID Name Member Location
XXX 001 X 1 London
XXX 002 Y 0 London
XXX 001 Z 1 Manchester
XXX 003 A 1 Manchester
XXX 004 B 1 Liverpool
XXX 001 C 0 London

For example, I have to find how many people with a unique ID are member (1=member 0=not member). In this case, the correct answer would be 3 (because the ID XXX 001 appears twice and only count once)

Besides, I am also looking for a formula to count the same thing adding the location. For example, how many unique ID are member AND live in London. Here, only 1 because even if XXX 001 (C) is not member we count XXX 001 (X).

I hope that my explanations were not too bad and I really count on you guys because I am stuck with this since this morning....

Thanks a lot

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Thanks a lot Lenze

???? Where is Lenze's answer ??? Have I done something ???...I should stop dringing coffee so much...

Sorry, my fault. I accidentally deleted it!!

Here is the PTs again
Code:
``````  A         B    C      D
1 Unique ID Name Member Location
2 xxx 001   X    1      London
3 xxx 002   Y    0      London
4 xxx 001   Z    1      Manchester
5 xxx 003   A    1      Manchester
6 xxx 004   B    1      Liverpool
7 xxx 001   C    0      London

Sheet1

[Table-It] version 07 by Erik Van Geit``````

Code:
``````   A             B                C      D
11 Count of Name
12 Unique ID     Location         Member Total
13 xxx 001       London           0      1
14                                1      1
15               London Total            2
16               Manchester       1      1
17               Manchester Total        1
18 xxx 001 Total                         3
19 xxx 002       London           0      1
20               London Total            1
21 xxx 002 Total                         1
22 xxx 003       Manchester       1      1
23               Manchester Total        1
24 xxx 003 Total                         1
25 xxx 004       Liverpool        1      1
26               Liverpool Total         1
27 xxx 004 Total                         1
28 Grand Total                           6

Sheet1

[Table-It] version 07 by Erik Van Geit``````

Sorry
lenze

Replies
3
Views
274
Replies
8
Views
904
Replies
2
Views
761
Replies
1
Views
2K
Replies
4
Views
383

1,219,574
Messages
6,149,087
Members
450,857
Latest member
Karomi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back