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
Help needed - I think I need VBA
- Thread starter KR1234
- Start date
- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- 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:
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.
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 SubThis 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.
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
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
- Joined
- Mar 21, 2011
- Messages
- 1,960
- 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.