Help needed - I think I need VBA

KR1234

New member
Joined
Dec 5, 2012
Messages
3
Reaction score
0
Points
0
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
 

Attachments

  • Excel Help.xlsx
    9.3 KB · Views: 15

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

Attachments

  • Excel Help.xlsm
    20.6 KB · Views: 16

KR1234

New member
Joined
Dec 5, 2012
Messages
3
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Do what you do manually with the macro recorder on, that should give you the code that you need.
 

KR1234

New member
Joined
Dec 5, 2012
Messages
3
Reaction score
0
Points
0
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.
 
Top