+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    05-03-2020
    Location
    Portland
    MS-Off Ver
    365
    Posts
    3

    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
    Attached Files Attached Files
    Last edited by excelismyweakness; 05-03-2020 at 04:40 PM. Reason: attaching spreadsheet

  2. #2
    Registered User
    Join Date
    04-26-2020
    Location
    Massachusetts
    MS-Off Ver
    Office 365
    Posts
    68

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

    Please attach a sample Excel file here using the instructions in the yellow area above - HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    This will help someone to find a solution to your problem.

  3. #3
    Registered User
    Join Date
    05-03-2020
    Location
    Portland
    MS-Off Ver
    365
    Posts
    3

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

    Thanks for pointing it out! - I've attached the spreadsheet with the scenarios. Hope this helps

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

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

    Here's a modular approach - good luck!

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,389

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

    Try the attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-03-2020
    Location
    Portland
    MS-Off Ver
    365
    Posts
    3

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

    Quote Originally Posted by jindon View Post
    Try the attached
    Please Login or Register  to view this content.
    So first of all, you and xladept - holy crop - I had no idea this was possible. Big Thank you guys! This accomplishes what I need.

    It seems to be doing the 100% of what I want whenever the scenarios are separated (asset added vs asset removed) - is there a possibility to modify the code to account for a simultaneous occurrence of both? I'm just starting with VBA and things are rough for my brain - apologies.

    Thanks!

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,389

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

    No, you don't need to touch the code.
    It lines up with the key of string before a period.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using an IF statement to produce an error if data doesn't match in columns
    By sophielane in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-10-2016, 03:46 PM
  2. Match values between 2 cells in separate columns and shift down if not matching
    By jimmyi189 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-25-2013, 01:54 PM
  3. Function for looping through multiple columns to match selections from comboboxes?
    By hugo.hardy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2012, 04:53 AM
  4. Compare 2 columns and shift down to match up--almost complete
    By cheerockracy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2011, 11:54 AM
  5. Match two columns with macro and shift cells down
    By cheerockracy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2009, 05:58 PM
  6. Printout doesn't match screen view (columns wrap differently).
    By SheriBee in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. [SOLVED] Printout doesn't match screen view (columns wrap differently).
    By SheriBee in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1