Forum Discussion

GSJones's avatar
GSJones
Copper Contributor
Apr 04, 2024

How do I copy and paste excel rows based on a common cell value (common key) ?

Ok I inherited an Excel document that has a bunch of mismatched records(17k). I'm trying to find a way to append rows from a common value in the far left column (Col D) to Col L and all the rows associated with to column X. Do I use VLOOKUP  or is there a rule I would have to do ? This would save me a lot of time. 

thanks!

  • GSJones 

    =INDEX($X$2:$AC$10,MATCH($L17,$X$2:$X$10,0),COLUMN(A$1))

    =VLOOKUP($L17,$X$2:$AC$10,COLUMN(A$1),FALSE)

     

    Does the small sample represent what you want to do with a large database? Either INDEX and MATCH or VLOOKUP can be entered in cell M17 and filled across range M17:R25 to append the data. Both formulas work in Excel 2013 and in all more recent versions of Excel.

     

    =FILTER($X$2:$AC$10,$X$2:$X$10=L17)

     

    If you have access to the FILTER function you can use this formula. In the example the formula is in cell M17 and filled down to cell M25.

  • GSJones 

    =INDEX($X$2:$AC$10,MATCH($L17,$X$2:$X$10,0),COLUMN(A$1))

    =VLOOKUP($L17,$X$2:$AC$10,COLUMN(A$1),FALSE)

     

    Does the small sample represent what you want to do with a large database? Either INDEX and MATCH or VLOOKUP can be entered in cell M17 and filled across range M17:R25 to append the data. Both formulas work in Excel 2013 and in all more recent versions of Excel.

     

    =FILTER($X$2:$AC$10,$X$2:$X$10=L17)

     

    If you have access to the FILTER function you can use this formula. In the example the formula is in cell M17 and filled down to cell M25.

    • GSJones's avatar
      GSJones
      Copper Contributor
      thank you so much! you are a life saver!!!

Resources