How to count only repeated records for any category?

Eli

You didn't say explicitly whether there is any association between categories and numbers.

Lets say that we have in A1: B7 the following sample data:

{"a",200;"a",200;"d",500;"d",500;"b",300;"c",400;"e",600}

In C1 enter: =COUNTIF(A:A,A1) [ copy down to the last row of data ]

In D1 enter: =COUNTIF(B:B,B1) [ copy down to the last row of data ]

You can sort the range A1:C7 on C descending. Repeats will be on top.

You can also make a list say in E from E1 on of your 10 categories. Then

in F1 array-enter: =SUM(($A$1:$A$6000=E1)+0) [ copy down this as far as needed; 6000 refers to your case not the sample above ]

To array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

This produces the repeats involving the categories. If categories are associated with your 4-digit numbers, the results tells you how many recs are repeats.

Hope this helps.

Not working yet!

YES, THERE IS ASSOCIATION BETWEEN THE FIELDS. SO "A",200 AND "A",200 GIVES 1 REPEAT.
"A",200 AND "A",300 ARE DIFFERENT RECORDS.
SO ARE "A",200 AND "B",200.

IN YOUR EXAMPLE I WOULD EXPECT THESE RESULTS:
FOR CATEGORY "a" 1 REPEAT
FOR CATEGORY "b" 0 REPEATS
FOR CATEGORY "c" 0 REPEATS
FOR CATEGORY "d" 1 REPEAT, AND SO ON.
IF YOUR ARRAY HAD ALSO THE ROW "a",300
THE RESULTS WERE THE SAME.
BUT IF YOR ARRAY HAD THE ROW "a",200 ONCE MORE THEN THE RESULT FOR CATEGORY "a" WOULD BE 2 REPEATS

I DO NOT SEE YET HOW YOUR SUGGESTIONS SOLVE THIS PROBLEM

I HOPE THAT I MADE MY QUESTION MORE CLEAR
THANK YOU IN ADVANCE
ELI

Re: Not working yet!

Eli

At least it made you clarify your data and question a bit further. :)

Lets see whether it can be solved the way you want it.

One more question though:

You said you have more or less 10 cats in A. How many types of 4-digit numbers do you have?

Aladin

===============

YES, THERE IS ASSOCIATION BETWEEN THE FIELDS. SO "A",200 AND "A",200 GIVES 1 REPEAT. "A",200 AND "A",300 ARE DIFFERENT RECORDS. SO ARE "A",200 AND "B",200. FOR CATEGORY "a" 1 REPEAT FOR CATEGORY "b" 0 REPEATS FOR CATEGORY "c" 0 REPEATS FOR CATEGORY "d" 1 REPEAT, AND SO ON. IF YOUR ARRAY HAD ALSO THE ROW "a",300 THE RESULTS WERE THE SAME. BUT IF YOR ARRAY HAD THE ROW "a",200 ONCE MORE THEN THE RESULT FOR CATEGORY "a" WOULD BE 2 REPEATS THANK YOU IN ADVANCE ELI

Re: Not working yet!

Aladin,
The 4-digit numbers are from 1000 to 9999
Eli

This should fit the requirements...

The 4-digit numbers are from 1000 to 9999

That's huge. OK, here we go. I hope it's worth it.

I assume in A2:B13 the following sample of recs.

{"a",200;"a",700;"a",300;"d",500;"b",300;"c",400;"e",600;"a",200;"c",400;"c",800;"b",657;"b",657}

In C2 array-enter: =SUM(($A$2:$A$13=A2)*($B$2:$B$13=B2))
In C3 array-enter: =IF(AND(ISNUMBER(MATCH(A3,$A$2:A2,0)),ISNUMBER(MATCH(B3,$B$2:B2,0))),"",SUM(($A$2:$A$13=A3)*($B$2:$B$13=B3))) [ copy down this up to the last row of data ]

In D2 enter: =IF(ISNUMBER(C2),IF(C2>=1,C2-1,C2),C2) [ copy down this up to the last row of data ]

In E1 enter: =COUNT(E2:E13) [ Name this cell NumDupRecs via the Name Box ]
In E2 enter: =IF(ISNUMBER(D2),IF(D2>0,RANK(D2,D$2:D$13)+COUNTIF(D$2:D2,D2)-1,""),"") [ copy down this up to the last row of data ]

In F2 enter: =IF(ROW()-1<=NumDupRecs,INDEX(A$2:A$13,MATCH(ROW()-1,$E$2:$E$13,0)),"") [ Copy this formula first to G2 then down up to the last row of data ]

In H2 enter: =IF(ROW()-1<=NumDupRecs,INDEX(D$2:D$13,MATCH(ROW()-1,$E$2:$E$13,0)),"") [ copy down this up to the last row of data ]

For the sample data you see in F2:H4 the following as result:

{"a",200,1;"c",400,1;"b",657,1}

Aladin

===========

I'l check it, thank you!

Aladin
I'll check it slowly later
It seems to be a simple task
and I feel that there must be easier way
Any way thank you for your great effort
and much appreciation to your concern
Eli

I'l check it, thank you!

Aladin
I'll check it slowly later
It seems to be a simple task
and I feel that there must be easier way
Any way thank you for your great effort
and much appreciation to your concern
Eli