# Compare if identical value in all 7 columns

#### Laird

##### New Member
I have a spreadsheet of hundreds of MAC addresses in 12 columns. I want to check that every column has the exact same list.

As an example, let's say four columns with five values each, like this. Note that one row has XXX.

123 ABC F7F CDC
123 ABC F7F CDC
123 XXX F7F CDC
123 ABC F7F CDC
123 ABC F7F CDC

Q: How to quickly find if all the same, or which row has a problem?

There must be an easier way than using nested IF statements, and I would need to nest an impossible 12 levels.

How would you approach this?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### lenze

##### Legend
Use CF. Say your rows start in Row 2. Something like

=(A4<>A3)+(A4<>A2)=2

lenze

#### pgc01

##### MrExcel MVP
Hi Laird

If I had hundreds of records, I would build a small table at the beggining that tells me if there is any record different and where it is located.

This small example, that you can adapt, assumes the first record is OK.

- insert some rows at the start

- in B2:

=COUNTIF(B5:B1000,"<>"&B5)-COUNTBLANK(B5:B1000)
Copy accross

- in B3:

=IF(B2=0,"",ROW(B5)-1+MATCH(TRUE,B5:B1000<>B5,0))
... confirmed with Control-Shift-Enter
Copy accross

I would then use conditional format to make them stand out when you get to the page they are in.

Last edited:

#### Laird

##### New Member
Hi Lenze, yours worked -- thank you so much !

PGC, I'll try yours next. It looks a little more complicated but appears to do more..!

Last edited:

Replies
0
Views
108
Replies
4
Views
130
Replies
10
Views
171
Replies
16
Views
321
Replies
0
Views
226

1,132,896
Messages
5,655,850
Members
418,247
Latest member
The_aze

### 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.

### Which adblocker are you using?

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

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