Formulas to count duplicate rows on sheet

[Deutz]

Hi and thanks in advance,

I'm wondering how to build a couple of formulas that don't use a helper column/concat column. One formula should count duplicate rows (on a single sheet) including the first occurrence of the duplicate. The second formula should count duplicate rows (on a single sheet) and NOT include the first occurrence of the duplicate. The sheet can have multiple columns.

Thanks
Deutz

 

[NotoriousPopol]

This seemed to work : =SUM(IF(COUNTIFS($A$1:$A$10,IF(ISBLANK(A1:A10),"",A1:A10),$B$1:$B$10,IF(ISBLANK(B1:B10),"",B1:B10),$C$1:$C$10,IF(ISBLANK(C1:C10),"",C1:C10))>1,1,0)). So, the criteria for each column becomes "" if the cell is empty. But this will count empty rows too (if you have 2 empty rows, it will increase the result).

You can affect this change to the 2nd formula too I guess!

 

[Deutz]

This seemed to work : =SUM(IF(COUNTIFS($A$1:$A$10,IF(ISBLANK(A1:A10),"",A1:A10),$B$1:$B$10,IF(ISBLANK(B1:B10),"",B1:B10),$C$1:$C$10,IF(ISBLANK(C1:C10),"",C1:C10))>1,1,0)). So, the criteria for each column becomes "" if the cell is empty. But this will count empty rows too (if you have 2 empty rows, it will increase the result).

You can affect this change to the 2nd formula too I guess!

Thanks for your help with this, it is much appreciated. I will try to implement your suggestion with the second formula as well.

Cheers
Deutz

 

[Deutz]

Hi NotoriousPopol,

I modified your first formula to count all dupes except for the blank row dupes ...

{=SUM(IF(COUNTIFS($A$1:$A$10,IF(ISBLANK(A1:A10),"",A1:A10),$B$1:$B$10,IF(ISBLANK(B1:B10),"",B1:B10),$C$1:$C$10,IF(ISBLANK(C1:C10),"",C1:C10))>1,1,0))-IF(SUMPRODUCT(--(A1:A10=""),--(B1:B10=""),--(C1:C10=""))>1,SUMPRODUCT(--(A1:A10=""),--(B1:B10=""),--(C1:C10="")),"")}

Don't know if there is a simpler way to achieve this but it seems to get the right count without the blank dupe rows counted.



For your second formula, to get the count of dupes without first occurrence ...

=SUM(IF(COUNTIFS($A$1:$A$10,A1:A10,$B$1:$B$10,B1:B10)>1,(COUNTIFS($A$1:$A$10,A1:A10,$B$1:$B$10,B1:B10)-1)/COUNTIFS($A$1:$A$10,A1:A10,$B$1:$B$10,B1:B10),0))

... I am still trying to get it working in the cases where there are no values in some columns. Not sure how to implement the ISBLANK functions since there is also division involved in this formula?

Thanks
Deutz

 

[NotoriousPopol]

Hi, did you try to simply replace each 2nd argument of COUNTIFS with the IF(ISBLANK()) statement? No problem for the division, you do it only if the value is >1. The formula is quite ugly tho...

 

[Aladin Akyurek]

Are the following what you are after?

1. In E1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(MATCH(A1:A8&"|"&B1:B8&"|"&C1:C8,A1:A8&"|"&B1:B8&"|"&C1:C8,0),ROW(A1:A8)-ROW(A1)+1),1))

2. In E2 control+shift+enter:

=SUM(IF(LEN(A1:A8&B1:B8&C1:C8),1))-E1

 

[NotoriousPopol]

Could you explain a bit your formula Aladin? Seems interesting and much more efficient. But it doesn't give me the correct result in my Test workbook.

 

[Aladin Akyurek]

Could you explain a bit your formula Aladin? Seems interesting and much more efficient. But it doesn't give me the correct result in my Test workbook.

I'm not sure about the specs the OP has provided. You seem to think otherwise. We can either wait for a reaction by Deutz or you post a small sample with the associated expected results.

 

[Deutz]

I'm not sure about the specs the OP has provided. You seem to think otherwise. We can either wait for a reaction by Deutz or you post a small sample with the associated expected results.

Hi Aladin and NotoriousPopol,

Thanks for your assistance.

I will try to make my specs a bit clearer.

Here is an example of what I'm trying to count for the first formula which should include all duplicate rows (in red) but not include blank rows, so the count would be 5 ...

1	aaa	x
1	aaa	x
4	e
2	r
z	z
2	r
1	aaa	x

<tbody>
</tbody>

Here is an example of what I'm trying to count for the second formula which should include duplicate rows (in red) but not include the first occurrence or blank rows, so the count would be 3 ...

1	aaa	x
1	aaa	x
4	e
2	r
z	z
2	r
1	aaa	x

<tbody>
</tbody>

Hope this is a bit clearer. Sorry about the table formatting which shows the blank rows a bit narrower than those with data.

thanks
Deutz

 

[Aladin Akyurek]

Control+shift+enter...

[1]

=SUM(IF(FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),
    ROW(A1:A9)-ROW(A1)+1)>1,FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),
    MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),ROW(A1:A9)-ROW(A1)+1)))

[2]

<strike></strike>=SUM(IF(FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),
     ROW(A1:A9)-ROW(A1)+1)-1>0,FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),
     MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),ROW(A1:A9)-ROW(A1)+1)-1))<strike></strike>

 

[Deutz]

Control+shift+enter...

[1]

=SUM(IF(FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),
    ROW(A1:A9)-ROW(A1)+1)>1,FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),
    MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),ROW(A1:A9)-ROW(A1)+1)))

[2]

<strike></strike>=SUM(IF(FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),
     ROW(A1:A9)-ROW(A1)+1)-1>0,FREQUENCY(IF(LEN(A1:A9&B1:B9&C1:C9),
     MATCH(A1:A9&"|"&B1:B9&"|"&C1:C9,A1:A9&"|"&B1:B9&"|"&C1:C9,0)),ROW(A1:A9)-ROW(A1)+1)-1))<strike></strike>

Thanks Aladin, that is impressive and works as required. I understand you are concatenating the column values with a pipe character in the MATCH and that makes sense to me but not sure how the next bit works in the FREQUENCY ... ROW(A1:A9)-ROW(A1)+1
Are you able to briefly shed some light on that? It is a bit hard for me to visualize how that works in an array formula.

Thanks also to NotoriousPopol for your helpful suggestions.

Regards
Deutz