VLOOKUP WEEK is moving along and Bill brings us another use of VLOOKUP combined with the 'Consolidate Function'. Also, Save The Date: March 29th, 2012 beginn...
Transcript of the video:
consolidate and lookup
Hey, welcome back to Mr. Excel Netcast. I’m Bill Jelly. This is episode 1535, Consolidate and LOOKUP.
Hello. We are having a lot of fun with VLOOKUP Week. Check it out vlookupweek.wordpress.com. Check out The O to B LOOKUP. Just absolutely amazing the things that people came up with. I’m going to go back here that my days as a data analyst. Here, I have hundreds of rows of data, customer number, customer name, and some numeric fields. And I want to collapse it down to one row per customer and I’m going to use a command back here on the data tab called Consolidate. And now, CONSOLIDATE only works with a single column of labels. But, I’m going to show you a cool trick here.
We can use VLOOKUP to have it all work. So, CONSOLIDATE, what’s the reference? The reference is all of this data and we’re going to use labels in top row, and left column. Click OK. Here’s what we get. We get one line per customer. There’s the customer number and it’s total all of this up perfectly. Customer name, of course, it couldn’t get because it doesn’t know how to total customer names. So, I’m going to put account number over there. And to fill in customer name =VLOOKUP.
Lookup this account number back in this range, hundreds of data points here. Press F4, comma 2, comma FALSE. But, I don’t care there’s hundreds. Like, for example there’s maybe 50 records that have 4. It does not matter. If this is just going to give me the first one, which gets me that customer. Now, we need to copy that and paste as values. And we now have a nice 27 row dataset instead of hundreds of rows. You can use this for all types of things. So, VLOOKUP in combination with consolidate.
Great way to consolidate that data down to one record per customer. Well, hey, I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel. Just a quick program note. on Thursday, this week, CFO magazine will be doing a netcast devoted to VLOOKUP and all variations of VLOOKUP. Check that out at cfo.com. Click on webcast.
Keywords for this video: 2007, 2010, accounting, Bill Jelen, business, Data, Excel, Excel 2007, Excel 2010, formula, formulas, MrExcel, spreadsheets, technology, tutorial, VLOOKUP, V...
This video is current as of March 28, 2012