Compare entire rows in excel - looking for exact duplicates in the rows.

JSH720

Board Regular
Joined
Oct 9, 2009
Messages
110
Office Version
  1. 365
Platform
  1. Windows
I need a macro or formula that will compare the cell values in a row and if all the cell values across all the columns are EXACTLY the same (excluding formatting) then they mark a specified column as true and filter the spreadsheet with just those rows. In essense, I'll looking to filter based on exact duplicate rows. As data in the rows change and the row is no longer an exact duplicate, then the specified column changes also - it is a dynamic cell.

Thank you for your help.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
See if this does what you want

Amend the count to match the number of columns
=COUNTIF(A1:H1,A1)=8

8 blank cells would return FALSE
- if that matters add anonther condition
=OR(COUNTIF(A1:H1,A1)=8,COUNTBLANK(A1:H1)=8)

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
TRUE
[/td][td] =COUNTIF(A1:H1,A1)=8[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]A[/td][td]a[/td][td]a[/td][td]a[/td][td]
TRUE
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]ab[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
FALSE
[/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0

Forum statistics

Threads
1,226,739
Messages
6,192,739
Members
453,755
Latest member
IQBS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top