"Consolidate and LOOKUP" - VLOOKUP WEEK Continues!

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 28, 2012 .
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!

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.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back