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 beginning at 2PM EDT! Bill will be presenting a VLOOKUP Webcast on CFO.com! Be sure to register for this event! For more information, go to http://www.cfo.com/webcasts/index.cfm/l_eventdetail?webcast=14620943
Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel. Check out the VLOOKUP WEEK 2012 Blog! So many folks have already contributed and continue to contribute -- with new entries flowing through the Inbox every hour or two! The biggest VLOOKUP Event since... VLOOKUP!
Also, Save The Date: March 29th, 2012 beginning at 2PM EDT! Bill will be presenting a VLOOKUP Webcast on CFO.com! Be sure to register for this event! For more information, go to http://www.cfo.com/webcasts/index.cfm/l_eventdetail?webcast=14620943
Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel. Check out the VLOOKUP WEEK 2012 Blog! So many folks have already contributed and continue to contribute -- with new entries flowing through the Inbox every hour or two! The biggest VLOOKUP Event since... VLOOKUP!
Transcript of the video:
Hey, welcome back to Mr. Excel Netcast.
I’m Bill Jelen.
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 Ode to VLOOKUP.
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.
I’m Bill Jelen.
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 Ode to VLOOKUP.
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.
