A viewer downloads data from a system where each item is separated by Alt+Enter
Bill: Why are you doing this? Viewer: It is how I inherit the data. I want to keep it that way.
Bill: What do you want to do with the 40% of values not in the table? Viewer: No answer
Bill: There is a complicated way to solve this if you have the latest Power Query tools.
Instead, a VBA Macro to solve it - the macro should work all the way back to Excel 2007
Instead of doing VLOOKUP, do a series of Find & Replace with VBA
Bill: Why are you doing this? Viewer: It is how I inherit the data. I want to keep it that way.
Bill: What do you want to do with the 40% of values not in the table? Viewer: No answer
Bill: There is a complicated way to solve this if you have the latest Power Query tools.
Instead, a VBA Macro to solve it - the macro should work all the way back to Excel 2007
Instead of doing VLOOKUP, do a series of Find & Replace with VBA
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2150: VLOOKUP Each Alt+Entered Value In Each Cell.
Hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today, one of the more bizarre questions.
Someone said, hey, I want to do a VLOOKUP for each value in the cell, and when I opened the Excel file, the data has been ALT+Entered.
So, there's 4 items in this order and they're all separated by ALT+ENTER, and then only 2 here and 6 here and so on.
I went back to the person who sent this in.
I was like, well, this is a really bad way to store this data.
Why are you doing this?
And he was like, I'm like I'm not doing it.
This is the way that the data is downloaded.
I said, is it okay if I split it out into separate rows?
No, you need to keep it this way.
Alright.
So, there's no good way to do a VLOOKUP for each individual item, and tomorrow, on tomorrow's episode, 2151, I'm going to show you how we can use a brand new feature in Power Query to do this but you'd have to have Office 365 to have that.
So, today, I want to use a method that's going to go all the way back, and what I've done here is I've created a new worksheet with the LOOKUPTABLE, so these are the items.
I also noticed that there's a whole bunch of things, about 40% of the things here, aren't in the LOOKUPTABLE.
I said, what do you want to do there, and no response to that question, so I'm just going to leave them as they are if I don't find a match.
Alright, so, what I have here is I have a sheet called LOOKUPTABLE and you'll see that my file right now is stored as xlsx and I'm going to use a VBA macro.
In order to use a VBA macro, you can't have it as xlsx.
It's against the rules.
So, you have to SAVE AS and save this is xlsm.
FILE, SAVE AS, and change it from WORKBOOK to either a MACRO-ENABLED WORKBOOK XLSM, or a BINARY WORKBOOK -- either one of those will work -- alright, and click SAVE.
Alright, so, now we're allowed to run macros.
ALT+F11 to get to the macro recorder.
You start with this big gray screen.
INSERT, MODULE, and there's our module, and here's the code.
So, I called it ReplaceInPlace and I define one worksheet.
That's the LookupTable.
You’d put your real lookup table worksheet name there, and then my lookup table starts in column A, which is column 1.
So, I go to the very last row in column 1, press the END key and the UP arrow, or, of course, CONTROL+UP arrow would do the same thing, figure out what row it is, and then we're going to go from every row from 2 to FinalRow.
Why 2?
Well, because the headings are in row 1.
So I want to going to replace, starting at row 2 all the way down to the last row, and so, for each row from 2 to FinalRow, the FromValue is what's in column A and the ToValue is what's in column B.
Now if, for some reason, your data was in J and K, then this J would be the 10th column so you put a 10 there, and K would be 11th column, and then, in the Selection, we're going to replace the FromValue to the ToValue.
This is really important here.
xlPart, xlPart -- and that’s an L, not a number 1 -- xlPart which says that will allow us to replace part of the cell because those part numbers are all separated by a linefeed character.
Even though you can't see it, it's there.
So, that should allow us to not accidentally update the wrong thing, and then xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Alright.
So, this is our little macro here.
Let's try it.
We'll take this data, and I don't want to destroy anything so I'm just going to take the original data and copy it over to the right.
Alright.
So, we have our selection there.
Actually, I’m going to start from this point.
CONTROL+BACKSPACE, and then ALT+F8 to get a list of all the macros.
There's our REPLACEINPLACE.
I'll click RUN, and everywhere that it found an item in the LOOKUPTABLE, it replaced that item number with the item, seemingly doing a VLOOKUP, although we're not solving it with a VLOOKUP at all.
Alright.
So, hey, the brand new book that came out -- Power Excel With MrExcel, the 2017 Edition, 617 Excel Mysteries Solved -- all kinds of great new tips in there.
Today's wrap-up: viewer downloads data from a system where each item is separated by an ALT+ENTER, and then needs to do a VLOOKUP at each item, and, you know, why am I doing this; so, the person said, I'm not doing it but I need to keep it this way; and then 40% of the values are not in the table, well, no answer; so I guess they're going to add those items to the table; now, tomorrow, we're going to talk about how to solve this with Power Query, but, today, this macro will work all the way back in all Windows versions of Excel, going back to Excel 2007 at least; so, instead of a VLOOKUP, just a series of find and replace with VBA.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today, one of the more bizarre questions.
Someone said, hey, I want to do a VLOOKUP for each value in the cell, and when I opened the Excel file, the data has been ALT+Entered.
So, there's 4 items in this order and they're all separated by ALT+ENTER, and then only 2 here and 6 here and so on.
I went back to the person who sent this in.
I was like, well, this is a really bad way to store this data.
Why are you doing this?
And he was like, I'm like I'm not doing it.
This is the way that the data is downloaded.
I said, is it okay if I split it out into separate rows?
No, you need to keep it this way.
Alright.
So, there's no good way to do a VLOOKUP for each individual item, and tomorrow, on tomorrow's episode, 2151, I'm going to show you how we can use a brand new feature in Power Query to do this but you'd have to have Office 365 to have that.
So, today, I want to use a method that's going to go all the way back, and what I've done here is I've created a new worksheet with the LOOKUPTABLE, so these are the items.
I also noticed that there's a whole bunch of things, about 40% of the things here, aren't in the LOOKUPTABLE.
I said, what do you want to do there, and no response to that question, so I'm just going to leave them as they are if I don't find a match.
Alright, so, what I have here is I have a sheet called LOOKUPTABLE and you'll see that my file right now is stored as xlsx and I'm going to use a VBA macro.
In order to use a VBA macro, you can't have it as xlsx.
It's against the rules.
So, you have to SAVE AS and save this is xlsm.
FILE, SAVE AS, and change it from WORKBOOK to either a MACRO-ENABLED WORKBOOK XLSM, or a BINARY WORKBOOK -- either one of those will work -- alright, and click SAVE.
Alright, so, now we're allowed to run macros.
ALT+F11 to get to the macro recorder.
You start with this big gray screen.
INSERT, MODULE, and there's our module, and here's the code.
So, I called it ReplaceInPlace and I define one worksheet.
That's the LookupTable.
You’d put your real lookup table worksheet name there, and then my lookup table starts in column A, which is column 1.
So, I go to the very last row in column 1, press the END key and the UP arrow, or, of course, CONTROL+UP arrow would do the same thing, figure out what row it is, and then we're going to go from every row from 2 to FinalRow.
Why 2?
Well, because the headings are in row 1.
So I want to going to replace, starting at row 2 all the way down to the last row, and so, for each row from 2 to FinalRow, the FromValue is what's in column A and the ToValue is what's in column B.
Now if, for some reason, your data was in J and K, then this J would be the 10th column so you put a 10 there, and K would be 11th column, and then, in the Selection, we're going to replace the FromValue to the ToValue.
This is really important here.
xlPart, xlPart -- and that’s an L, not a number 1 -- xlPart which says that will allow us to replace part of the cell because those part numbers are all separated by a linefeed character.
Even though you can't see it, it's there.
So, that should allow us to not accidentally update the wrong thing, and then xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Alright.
So, this is our little macro here.
Let's try it.
We'll take this data, and I don't want to destroy anything so I'm just going to take the original data and copy it over to the right.
Alright.
So, we have our selection there.
Actually, I’m going to start from this point.
CONTROL+BACKSPACE, and then ALT+F8 to get a list of all the macros.
There's our REPLACEINPLACE.
I'll click RUN, and everywhere that it found an item in the LOOKUPTABLE, it replaced that item number with the item, seemingly doing a VLOOKUP, although we're not solving it with a VLOOKUP at all.
Alright.
So, hey, the brand new book that came out -- Power Excel With MrExcel, the 2017 Edition, 617 Excel Mysteries Solved -- all kinds of great new tips in there.
Today's wrap-up: viewer downloads data from a system where each item is separated by an ALT+ENTER, and then needs to do a VLOOKUP at each item, and, you know, why am I doing this; so, the person said, I'm not doing it but I need to keep it this way; and then 40% of the values are not in the table, well, no answer; so I guess they're going to add those items to the table; now, tomorrow, we're going to talk about how to solve this with Power Query, but, today, this macro will work all the way back in all Windows versions of Excel, going back to Excel 2007 at least; so, instead of a VLOOKUP, just a series of find and replace with VBA.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
