Counting Duplicates Once

  • Thread starter Thread starter kevin giblin
  • Start date Start date
K

kevin giblin

following on from the counting of multiple selections once, how do excel to count a a list of card number with duplicates once.

For exapmle a list of card numbers would look like this

Card number

11111 22222 33333
11111 22222 33333
11111 22222 33333
44444 55555 66666

the aswer would be two as two once card number has been inserted 3 times?

EggHeadCafe - Software Developer Portal of Choice
ADO.NET Programmer's Reference
http://www.eggheadcafe.com/tutorial...e35-430fa3022e03/adonet-programmers-refe.aspx
 
following on from the counting of multiple selections once, how do excel to
count a a list of card number with duplicates once.

For exapmle a list of card numbers would look like this

Card number

11111 22222 33333
11111 22222 33333
11111 22222 33333
44444 55555 66666

the aswer would be two as two once card number has been inserted 3 times?

EggHeadCafe - Software Developer Portal of Choice
ADO.NET Programmer's Reference
http://www.eggheadcafe.com/tutorials/aspnet/fadf376e-ec80-4054-ae35-430fa3022e
03/adonet-programmers-refe.aspx
Click to expand...


Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10
)-ROW(A2)+1),1))

Adjust the ranges, accordingly.
 
One way...

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Note that this is slow to calculate on large ranges. ~1000+ rows
 
There are so many ways to do this:
< -- =SUMPRODUCT((A2:A105<>"")/(COUNTIF(A2:A105,A2:A105&"")))
< -- =SUMPRODUCT((A2:A998<>"")/(COUNTIF(A2:A998,A2:A998)+(A2:A998="")))
< --
=SUM(IF(FREQUENCY(IF(LEN(A2:A999)>0,MATCH(A2:A999,A2:A999,0),""),IF(LEN(A2:A999)>0,MATCH(A2:A999,A2:A999,0),""))>0,1))
Note: This is a CSE Function
< -- =SUMPRODUCT((A2:A105<>"")/COUNTIF(A2:A105,A2:A105&"")*(A2:A105<>""))
< -- =SUMPRODUCT(--(A2:A999<>""),1/COUNTIF(A2:A999,A2:A999&""))
< --
=SUM(--(FREQUENCY(IF(A2:A2705<>"",MATCH(A2:A2705,A2:A2705,0)),ROW(INDIRECT("1:"&ROWS(A2:A2705))))>0))
Note: This is a CSE Function
< -- =COUNT(1/FREQUENCY(A1:A400,A1:A400))
< -- =SUM(IF(A2:A400<>"",1/COUNTIF(A2:A400,A2:A400)))
< --
=SUM(IF(FREQUENCY(IF(A2:A1001<>"",MATCH("~"&A2:A1001,A2:A1001&"",0)),ROW(A2:A1001)-ROW(A2)+1),1))

HTH,
Ryan---
 
Back
Top