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]

Should they match on every item for each row? (A1=A3 AND B1=B3)?

 

[Deutz]

Should they match on every item for each row? (A1=A3 AND B1=B3)?

Yes, that is exactly what I need, where they match on every column for each row.

Thanks

 

[NotoriousPopol]

You will find what your are looking for with COUNTIFS. I don't know if you want to sum all duplicates, or have in from of each row the number of duplicates for this row?

 

[Aladin Akyurek]

Try to post a small sample along with the desired results.

 

[Deutz]

Try to post a small sample along with the desired results.

Sorry, I should have made it clearer.

With the following set of data the first formula should count all duplicate rows including the first occurrence. Rows to count are shown in red here, so the count = 5

Column A	Column B	Column C
1	aaa	xxx
2	bbb	rrr
1	aaa	xxx
3	aaa	xxx
1	aaa	xxx
5	ccc	qqq
2	bbb	rrr
1	aaa	zzz

<tbody>
</tbody>

With the following set of data the second formula should count duplicate rows but NOT including the first occurrence. Rows to count are shown in red here, so the count = 3

Column A	Column B	Column C
1	aaa	xxx
2	bbb	rrr
1	aaa	xxx
3	aaa	xxx
1	aaa	xxx
5	ccc	qqq
2	bbb	rrr
1	aaa	zzz

<tbody>
</tbody>

The number of colums may vary over time.

Thanks
Deutz

 

[NotoriousPopol]

For the first solution, I propose : {=SUM(IF(COUNTIFS($A$1:$A$10,A1:A10,$B$1:$B$10,B1:B10,$C$1:$C$10,C1:C10)>1,1,0))}. You will need to press ctrl+shift+enter as this is an array formula.

For the second, this one seems to work : =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)). Basically we do the same but instead of summing 1, we sum (x-1)/x where x is the number of occurrence.

 

[Deutz]

For the first solution, I propose : {=SUM(IF(COUNTIFS($A$1:$A$10,A1:A10,$B$1:$B$10,B1:B10,$C$1:$C$10,C1:C10)>1,1,0))}. You will need to press ctrl+shift+enter as this is an array formula.

For the second, this one seems to work : =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)). Basically we do the same but instead of summing 1, we sum (x-1)/x where x is the number of occurrence.

Thanks so much for that, NotoriousPopol and for the explanation. Works fine for the examples I provided. Just one other wrinkle ... is it possible to make this work in the case where some of the dupe rows have no values in one or more columns?

Also wondering about the range references in the COUNTIFS function as to why the first reference is absolute and the second is relative?

Thanks
Deutz

 

[NotoriousPopol]

Hi,

Do you want rows to match when there is empty data? For example, should this configuration match :

- 1 / aaa / xxx and 1 / / xxx ?
- 1 / / xxx and 1 / / xxx ?

As for the absolute and relative reference. For the first result you wanted, we want to count how many time COUNTIF is > 1 for each row. We could have done that with an additional column, with =IF(COUNTIFS($A:$A, A1, $B$B, B1, $C$C, C1)>1,1,0) for each row. Then we would have SUM the results, and get what you wanted.
To do this in one single formula, we need to "simulate" this additional column with an array formula. We start with the SUM, and replace the single reference A1 (and A2, A3...) with the array A1:A10. This way, Excel knows it has to SUM the result for each cell contained in this array!

Hope this is clear^^

 

[Deutz]

Hi,

Do you want rows to match when there is empty data? For example, should this configuration match :

- 1 / aaa / xxx and 1 / / xxx ?
- 1 / / xxx and 1 / / xxx ?

As for the absolute and relative reference. For the first result you wanted, we want to count how many time COUNTIF is > 1 for each row. We could have done that with an additional column, with =IF(COUNTIFS($A:$A, A1, $B$B, B1, $C$C, C1)>1,1,0) for each row. Then we would have SUM the results, and get what you wanted.
To do this in one single formula, we need to "simulate" this additional column with an array formula. We start with the SUM, and replace the single reference A1 (and A2, A3...) with the array A1:A10. This way, Excel knows it has to SUM the result for each cell contained in this array!

Hope this is clear^^

Thanks for the excellent explanation.

Your first example would NOT match since the second column has the value aaa in the first row but no value in the other row ...
- 1 / aaa / xxx and 1 / / xxx

Your second example would match since the column values are identical for both rows ...
- 1 / / xxx and 1 / / xxx