Forum Discussion

Andy1883's avatar
Andy1883
Copper Contributor
Nov 02, 2023

Compare and replace

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

 

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 02, 2023

    Andy1883 

    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.

     

    • Andy1883's avatar
      Andy1883
      Copper Contributor
      Nov 03, 2023

      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

       

       

       

      • Andy1883's avatar
        Andy1883
        Copper Contributor
        Nov 03, 2023
        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