MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP Each Alt+Entered Value


October 02, 2017 - by Bill Jelen

VLOOKUP Each Alt+Entered Value

How to do a calculation (such as VLOOKUP) for each item that has been Alt+Entered in a cell.

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2150 vlookup each alt entered value in
  • each cell hey welcome back to the mr.
  • excel NetCast
  • I'm bill gel and 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 four items in this order and
  • they're all separated by all to enter
  • and that only two here and six here and
  • so on I went back the person who sent
  • this sounds like well this is a really
  • bad way to store this data why are you
  • doing this and I'm like I'm not doing it
  • this is the way that the data is
  • downloaded I said is okay if I split it
  • out in a separate separate rows no you
  • need to keep it this way
  • all right so there's no good way to do a
  • vlookup for each individual item and
  • tomorrow I'm tomorrow's episode 21 51
  • I'm gonna show you how we can use a
  • brand new feature and 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 lookup
  • table 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 lookup table I said what
  • do you want to do there and no response
  • to that question so I'm just gonna leave
  • them as they are if I if I don't find a
  • match all right so what I have here is I
  • have she called lookup table and you'll
  • see that my file right now is stored is
  • XLS X and I'm gonna 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 say 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 F
  • 11 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 call it replace in
  • place and I define one worksheet that's
  • the lookup table you bleed your real
  • lookup table worksheet name there and
  • then my lookup table starts in column a
  • which is column one so I go to the very
  • last row and call one press the end
  • key in the up arrow or course control up
  • arrow would do the same thing figure out
  • what row it is and then we're gonna go
  • from every row from two to five over why
  • - well because the headings are Row one
  • so I'm gonna replace starting a row -
  • all the way down to the last row and so
  • for each row from two to flaner row the
  • from value is what's in column a and the
  • to value is what's in column B now for
  • some reason your data was in J and K and
  • then this J would be the tenth column so
  • you put a 10 there and K would be 11 -
  • column and then in the selection we're
  • gonna replace the from value to the to
  • value this is really important or here
  • excel part exhale part and that's an L
  • not a look not a number one excel part
  • which says the learner allow us to
  • replace part of the cell because those
  • part numbers are all separated by a line
  • feed 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 Excel by rows match case false
  • search for my at false replace for my
  • false next die all right so this is our
  • little macro here let's try it I'll take
  • this data and I don't want to destroy
  • anything so I'm just gonna take the
  • original data and copy it over to the
  • right alright so we have our selection
  • there actually I'm gonna start from this
  • point ctrl backspace and then alt f8 to
  • get a list of all the macros
  • there's our replace in place I'll click
  • run and everywhere that it found an item
  • in the lookup table it replaced that
  • item number with the item seemingly
  • doing a vlookup although we're not
  • solving it with a vlookup at all all
  • right so hey the brand-new book that
  • came out power excel with MrExcel the
  • 2017 edition 617 excel mystery solved
  • all kinds of great new tips in there
  • today's wrap-up viewer downloads data
  • from a system where each tip 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 I need to
  • keep it this way and then 40 percent of
  • the values that not on the table
  • well no answer so I guess they're gonna
  • add those items to the table now
  • tomorrow we're gonna 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 fine and
  • replace with VBA hey I want to thank you
  • for stopping by I'll see you next time
  • for another net cast from MrExcel

Download File

Download the sample file here: Podcast2150.xlsm

Title Photo: ivabalk / Pixabay


Bill Jelen is the author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.