Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel Help - vlookup where a Range can change - including column numbers

  • 30-08-2022 1:00pm
    #1
    Speedsie
    Registered Users, Registered Users 2 Posts: 1,328 ✭✭✭
    ¡arriba, arriba! ¡andale, andale!


    Hi,

    I'm trying to simplify an Excel workbook which is used for mailmerge to create address labels. The 'raw' data comes from multiple sources, the only real consistency is that the data in the first line of

    Worksheet one is used for the merge, and has the following column headers:-

    "ID" "NAME" "LINE_ONE" "LINE_TWO" "LINE_THREE" "LINE_FOUR" "COUNTY" "POSTCODE"

    The raw data is dumped into Worksheet Two. Column 'A' always has the ID number, but after that it gets complex.

    The data could come in as:-

    "ID" "TITLE" "NAME" "LINE_ONE" "LINE_TWO" "LINE_THREE" "LINE_FOUR" "COUNTY" "POSTCODE"

    or

    "ID" "FORENAME" "SURNAME" "NAME" "LINE_ONE" "LINE_TWO" "LINE_THREE" "LINE_FOUR" "COUNTY" "POSTCODE"

    or

    "ID" "NAME" "POSITION" "STARTDATE" "LINE_ONE" "LINE_TWO" "LINE_THREE" "LINE_FOUR" "COUNTY" "POSTCODE"

    or

    "ID" "NAME" "AREA" "LINE_ONE" "LINE_TWO" "LINE_THREE" "LINE_FOUR" "COUNTRY" "COUNTY" "POSTCODE"

    I know that the simplest thing to do would be to delete the unnecessary columns, so everything stays consistent - eg "LINE_ONE" is always in Column D, so would always be '4' in the array, but I'm dealing with a person who is very much a technophobe. They will just about copy and paste 'as plain text' (which was a battle of months in itself), so if I could have a bulletproof formula, or a macro, it would make my life easier.

    The first line of data is, thankfully, consistent so that's at least one thing.

    I've been looking at it for so long, I'm googly eyes, and I just can't keep running this mailmerge for them.

    Tagged:


Advertisement