Join All VLOOKUP

Episode 183: Join all VLOOKUP Matches

Bill Jelen:       Hey, welcome back.  It's time for another Dueling Excel Podcast.  I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin for Excel Is Fun.  This is our Episode 183: Join All VLOOKUP Matches.

[Music]

Alright, today's question from Matt.  Can VLOOKUP return all results and join them together with a comma space between each.  For example, 109876 which are these two here, can it return Low Oil comma space Checked on 12/12.  And of course if there were more, it would return more.  Alright, so my solution here is going to be using some VBA.  Alright, so make sure that it’s saved as xlsm or you can't run VBA or xlsb, but not xlsx - xlsx is the one file that can't run VBA.  We’ll press Alt+F11, make sure you're on Dual183 or whatever the name of your workbook is.  Insert Module into the blank module and we're going to paste this code, alright.

Let's take a look at this function GetAll, and here's the ID number that we're looking for and then the range that we want to look.  And we start out, we're going to return a variable called GetAll, so we start out with that being equal to blank blank.  For each cell in my range, if the Cell Value is what we're looking for then we're going to take GetAll = GetAll & “ ” and then the Cell.Offset (0 rows, 1 column), in other words the value that's just next to that ID number because back in VBA, here's the ID number.  If we find the matching ID number we want to go 1 column over.  Now, what if you wanted to go 2 columns over or 3 columns over, well then you change this 0 rows and 1 column to be a 2.  Alright, also check to see if - we don't put a comma space if this is the first one.  So if the GetAll variable is currently “ ” then we won't put the comma space, alright?

So now that we have this function here, watch how easy this is to solve Matt's problem.  We're going to come over here and let's take his IDs, Ctrl+C and paste Ctrl+V like that.  Data, Remove Duplicates, click OK.  So there's a unique list of IDs and then we want to say =getall and we're looking for that value in E2 comma.  Looking through this range over here, I'll press F4.  F4 works just like a regular function.  And again moving Matt's question out of the way, double-click to shoot that down.  It will work. 

And let's just try, let's try something crazy here.  Let's do a Phrase 1 and just put a bunch of them like Phrase 1 through 10.  We’ll sign all these to 109999. Paste, and then paste over here.  Copy that formula down, edit the formula so it goes all the way to the bottom, of course.  Yup.  And it will return all of those Phrases.  Alright, so that's my solution, VBA, a little function there.  Mike, let's see what you have.

Mike Girvin:    Thanks, MrExcel.  GetAll, that is an awesome VBA function.  Alright, I'm going to go over to the sheet right here.  I've already converted it to an Excel table so that when we add records below, hopefully things will update.

Now the first thing I'm going to do this in two parts.  I could do a formula here for extracting a unique list but I want to look at another option: Advanced filter has an extract unique list option and it can be updated.  I'm going to highlight just the ID column data, over to Advanced Filter or I'm going to use the keyboard Alt, A, Q.  Now, Filter list in-place, no way.  I want to Copy it to another location.  It got just the A column and because it's an Excel table that will expand later.  I don't have any criteria, I want to Copy it to D1 and check Unique records only.  Click OK. 

Now, I'm going to come over here, All Comments enter and I'm going to use a function that only works in Excel 2016 Office 365: =TEXTJOIN function.  This function alone is worth getting the latest version of Excel.  This is such a common task that people want to do, join many things together.  Now our delimiter in “, ”, and the great thing about this function is we can tell it to Ignore empty cells.  Now, I can put TRUE, 1 or Leave it, Omit it.  So, I’m going to Leave it, Omit it.  And here's where we need our text.  We're going to use the IF function to filter out and get just the items we want. I’m going to say look through this entire column here: Table Name and then in [ ] the Field name, are any of you = to this relative cell reference, that's the logical test.  If I were to click this and hit the F9 key to evaluate, you could see right now we only have 2 TRUES, Ctrl+Z now I type a comma and with the array of Trues and Falses, now I can give it the items to pick out.  So now, we'll pick out only the items that have a TRUE here from this range.  Comma and I want to make sure to put “ ”  - that will show up as an empty cell in regards to the second argument in TEXTJOIN. 

Now, I'm going to close parenthesis and now the IF function will create that string of Trues and Falses, the actual items from this range will be picked up if it sees it True and all the other items will have that empty cell.   And guess what?  TEXTJOIN will totally ignore all of those empty cells and return just the items that match this ID, and then join it with that delimiter.  Now this is definitely an Array formula that requires the special keystroke Ctrol+Shift+Enter.  The logical test argument holds our Array operation and that argument cannot calculate this Array operation correctly unless we use the keyboard Ctrl+Shift+Enter.  Now I'm going to close parentheses.  Actually we could prove 1 right here in Text 1 if I F9 all this, we could see we get the 2 items, the rest of those empty cells will be ignored.  Ctrl+Z.  Now, let's enter this into the cell with Ctrl+Shift+Enter.  Immediately look up to the Formula Bar.  Those curly brackets are Excel telling you it understood and calculated this as an Array formula.  Now I can double-click and send it down.  That is looking good. 

I'm going to go to the last cell and hit F2 to verify that all the ranges are looking correctly.  Now what I don't want to do is I don't want to hit Enter because that formula after we put it in Edit Mode will only calculate correctly if we use Ctrl+Shift+Enter; or, because we already entered the formula, we can just use the Esc key to revert back to whatever's in the cell before we put it in Edit Mode.

Now, let's test this.  I'm going to click in the last cell down here and hit Tab and then type a new ID, Tab, Tab.  Another new record, Tab, and I can already see I didn't have enough work here.  I am, we're going to put - Perfect and then Enter.  Now, this isn't going to automatically update like if we have a bunch of formulas that we’re counting unique items and then extracting unique items, but no problem.  Watch this.  We can update this list of unique records because we used Advanced Filter and it doesn't matter what cell you start from either because when Advanced Filter is invoked, it memorizes the extract range and the ranges it was originally looking at.  You can click on Advanced Filter or use the keyboard Alt+A+Q.  We do have to select Copy to another location, but look at that.  It totally remembered and expanded to A13 because of the Excel Table feature.  It remembered the extract range.  I do have to check Unique records only but click OK. 

Now, I have to come over and copy this formula down.  And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match.  Alright, throw back to MrExcel.

Bill Jelen:       Hey, Mike, that is awesome.  Alright, wrapping up this episode.   I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing.  It doesn't remember the previous settings.  Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range.  And then TEXTJOIN, a beautiful new function, added an Office 365.  Mike says that alone is a reason to get the latest Office.  I said the TEXTJOIN would be life-changing.  TEXTJOIN is awesome because it can handle Arrays. 

Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells.  Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out.  And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter.  Check that out at Amazon or elsewhere, your favorite bookseller.  And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works.  That is beautiful, alright.

Oh hey, I want to thank everyone for stopping by.  We'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.