Count duplicate data row

[bdtran]

Hello All,

I really need your help with a formula the check the duplicate data row of the specific range as with the example below.

In column A, I need to check only those rows with the "Data-A" that have the exactly the same data information in B1:M1 range. For example in the table below A5 has exactly the same data information as of A1 row in the B1:M1 range.

The result in the P1 is what I need to know of how many duplicate rows have been inserted. That is what all I need to know.

Also it is not necessary but is there a way to identify the duplicate row using formula with either row number or highlight the row or any other ways so that I can verify and delete the duplicate row.

Thanks much for any help.
*BDT

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Data-A	1	0	1	0	0	1	1	A	0	A	1	1		Duplicate Count	1
2	Data-B
3	Data-A	0	0	0	A	A	A	1	1	1	0	1	A
4	Data-E
5	Data-A	1	0	1	0	0	1	1	A	0	A	1	1
6	Data-A	1	1	1	1	1	A	0	0	A	1	1	1
7	Data-A	0	1	0	1	0	1	0	1	0	1	A	A

<tbody>
</tbody>

 

[dfsobral]

it's probably not the cleanest solution but works

place this formula in cell N1 as an array formula (with Ctrl+Shift+Enter instead of just Enter)

=IF(SUMPRODUCT(--($A$1:$A$7="Data-A"),--(MMULT(--($B$1:$M$7=B1:M1),TRANSPOSE(COLUMN(B1:M1)^0))=12))>1,"Duplicate","")

adjust range as needed

then copy N1 to the entire column

every row where appears Duplicate has at least one duplicate

then you can place a countif in P1 if you want

 

[bdtran]

Thank you Dfsobral.

I tested it and it works. However I just realized that since I have over 20k+ rows which could cause of slowness.

So instead of copying N1 to the entire column to tell which row is duplicated, I just wanted to get the one simple result in P1 indicates that there is a duplication in entire range.

Thanks much for your feedback.
*BDT