Compare and replace

[AndyAmuri]

Hi Guys

I have a messy data set, 3000+ rows, and I must compare columns M and Q.  Where Q is different to M, Q needs to be the value captured in M.  Or if easier the correct value could go to a new cell/column.

I have played with Replace, X Lookup, and X match but I can't figure this out.  Hoping someone can assist :)

Cheers

Andy

[#2]

@AndyAmuri 

Here is a simply approach. To compare columns M and Q in your Excel dataset and replace the values in column Q with those from column M where they are different, you can use a combination of Excel functions like IF and IFERROR. Here's how you can do it:

Assuming your data starts in row 2, and the data in columns M and Q are in cells M2 and Q2, respectively, you can follow these steps:

1. In a new column, such as column R, starting from row 2, enter the following formula in cell R2:

=IF(M2<>Q2, M2, Q2)

This formula checks if the value in column M (M2) is different from the value in column Q (Q2). If they are different, it takes the value from column M; otherwise, it keeps the value from column Q.

1. Copy the formula in cell R2 down to apply it to all the rows in your dataset.

Now, column R should contain the values from column M where they were different from column Q, and it will keep the values from column Q where they were the same. You can copy and paste these values over column Q if you want to replace the original values.

This method will help you clean up your data by capturing the correct values in column M where they differ from column Q. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@NikolinoDE 

Hi, Thank you for your help.  It didn't quite work.  I copied it into row T and it picked up the address in Q, which I want where they differ from M, But it only gave me a 0.  I have tried swapping the cell values around but I only get either all of M or all of Q with 0 values.   I have tried trying to get that to the reference M2 but to no avail.  Would appreciate any further help you can give me.

I am not great with this sort of thing.  Punching above my weight here:(

Cheers

[#4]

I Think i got it sorted. I had to use IFERROR. Had to do it twice to get it to return a #value instead of a 0
=IFERROR(IF([@Column1],[@billingstreet],[@billingstreet]),[@Column1])

Thanks Heaps

[#5]

Thank you for your feedback.
I am pleased that you have found a solution for your project.
I wish you continued success with Excel.