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

ChrisTa

New Member
Joined
Jul 30, 2007
Messages
6
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

ChrisTa

New Member
Joined
Jul 30, 2007
Messages
6
???? Where is Lenze's answer ??? Have I done something ???...I should stop dringing coffee so much...
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,272
Members
436,786
Latest member
Deniel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top