Nov 01 2023 06:32 PM
Nov 01 2023 06:32 PM
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 :)
Nov 02 2023 12:11 AM
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:
=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.
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.
Nov 02 2023 05:24 PM
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:(
Nov 02 2023 06:05 PM
Nov 04 2023 12:19 AM