Results 1 to 5 of 5

Thread: Help needed - I think I need VBA

  1. #1

    Help needed - I think I need VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I'm a novice so please excuse my ignorance. I have a spreadsheet of data with field names in column A that aren't particularly user friendly as these come from a third party source. I would like to insert a column automatically after these values and then do some kind of lookup to insert a more meaningful description in the cell next to it. The data in columns C, D, E etc need to be maintained in the same rows. I have a list of field names that could be received in column A, but they arent necessarily in the same order etc. So would need to look up the whole column. Ideally - i would like to return the value "UNMAPPED" into Column B if I dont have a match for it in my look up - this is a nice to have & not a necessity. Any help would be greatly appreciatedCheersK
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, so a couple of things here.

    First, I copied your table of Unfriendly and Friendly names to a new sheet, then set it up as a Table (Home->Format as Table). I then changed the name on the table to "tblSensible" (Click in the table then go tot he TableTools tab to change the table name)

    Next I added this macro:

    Code:
    Sub InsertColumn()
        With ActiveSheet
            .Columns("B:B").Insert Shift:=xlToRight
            .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],tblSensible[#All],2,FALSE),""Not Mapped"")"
        End With
        
    End Sub
    This does assume that the data starts in row 2 of the worksheet and column B needs to be updated. IF that's not the case, we'll need to update the references to columns B and A in the code.

    Re-worked sample attached.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Thanks - one more thing

    Ken, Thanks for this - its great. My data actually starts life in rows not columns - I normally copy & paste transpose to get it to go down the page rather than across the page. starts with headers going accross, values in rows 2, 3, 4 etc. I transpose it so that headers all go into column A (and now column B thanks to your wizadry) with values going into columns C, D etc. Is there a way to build the transpose bit at the start of the macro?CheersK

  4. #4
    Do what you do manually with the macro recorder on, that should give you the code that you need.

  5. #5

    Smile Thanks for your help

    Sometimes the basics are overlooked - as I started with a problem I couldnt resolve. I didnt think of using the recorder Thanks both.

Posting Permissions

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