Shift selections down if the top row doesn't match a row of another set of columns

Hi everyone,

I'm working with template spreadsheets that get populated via copy/paste and compare volumes of assets in hypothetical what-if scenarios. In the daily workflow, the user manually inserts this information from another tool into Excel and does further analytics. However, the what-if analysis creates an issue where a lot of manual labor needs to be performed in Excel, where a genius function may probably be written. Here are the specifics:

FM4zh.png


In scenario 1, an asset is subtracted in the what-if analysis in the tool, and when you're pasting it in excel, the right 2 columns (post analysis) no longer match the left 2 columns (pre analysis). I would like the 'asset class 2' value in a row to look at the corresponding value in the **same row** of column 'asset class' and if doesn't match, shift (and keep shifting down) until it finds the identical value.


In scenario 2, an asset is added during the analysis process, and asset class B.2 is created. I would like for the rows in left columns to keep shifting down until they find the identical corresponding row values.

I tried playing around with the OFFSET function, but couldn't produce any effective results. Would love some help from Excel wizard that lurk around here.

Thank you! Let me know if you have questions!

file attached

Here's a modular approach - good luck!

Please Login or Register  to view this content.

Jindon's code should work in that situation but my code won't.