Today's Dueling Excel looks at what the I.T. Department is sending down. It's an ugly report, each day, and we want to collapse multiple lines - for each person - into a single line. But it is more complicated than that; we have to Concatenate First Name and Last Name, plus a Lookup to figure out which Column should be reported. Follow along with Episode #1429 as Bill 'MrExcel' Jelen employs a PowerPivot Solution and Mike 'ExcelisFun' Girvin employs a Formula to solve this week's challenge.
Transcript of the video:
Hey, welcome back. It's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. Mike Girvin from Excel is fun will be joining us.
This is episode 83: lookup and summarize.
Wow, this is a doozy today, Mike.
We have someone who's getting a file every single day.
It has a list of all of the students who are failing a class.
We have last name, first name, the name of the course, teacher, and some other things like that.
Now, these headings up here, including the merged record in A2, that's completely evil.
I don't like that at all.
The person is trying to create this report.
So, all of the last name, first name and then check this out; like to figure out the math column asks us to search for geometry or math or calculus or algebra-- painful.
The problem that we have is-- okay, first of all, we can't change failing kids.
It's coming in the way that it's coming in, right?
Some guy in IT creates that and we're just stuck with it.
He also wants to collapse this down to a single line.
All right, so, you know, this is a tough one.
I initially thought of a macro, but I'm going to try something crazy.
I'm going to try PowerPivot.
Let's see if PowerPivot can solve this.
I like PowerPivot especially because it might provide a way to save that Failing Kids out to the hard drive every day.
Open PowerPivot, refresh the data and have the report just update.
First thing I did is I took unique list of courses and created a lookup table rather than have that formula.
This seemed like the way to go.
I mean, hey, you only offer new courses once a year.
So, you only have to update this table once a year.
Let's create this table and use that.
We're going to go over to PowerPivot, PowerPivot window.
Rather than paste data in or link data in, I'm going to pretend like Excel is a foreign system and say that it's-- there is pull data in from an Excel file, click Next-- where is that file?
I saved it out in C, Failing Kids, there it is.
Although I have to tell you, I got rid of that row one and row two.
We're going to have to do that every day.
Before we open this, preview and filter.
There's a lot of columns that I really don't need.
I do need last name, first name.
Probably don't need grade.
I do need course, of course.
Don't need a lot of-- someone had to check everything.
I am going to leave one numeric column there.
That would be the grade in the course, even though they didn't want to show that.
I just want something pivotable that I can report.
So, uncheck all of this and click OK.
Click finish.
Success.
461 rows.
Good.
We can now add a new column in PowerPivot.
Equal first name, ampersand, quote, space, quote, ampersand, last name and we'll rename that just to be name.
This is good.
PowerPivot is learning that this is one of the rules.
Every time we import, they're going to add that column in automatically.
Beautiful, right?
Back to Excel.
Here's my lookup table.
I want to create under PowerPivot a linked table from this.
Click OK, so there's my course, there's my MapsTo and we're going to define a link from course here to course on the other sheet.
This is one of the powerful things about PowerPivot.
So, from failing kids, course to table 1 course, click create.
All right, PivotTable, new worksheet.
Okay, what are we going to do?
We're going to have the last name going down the left-hand side, put that in row labels.
We're going to have the percentage in sum values area and finally the MapsTo going across the top.
See as I now have a report that looks almost like what we wanted to look like, except for we have these numbers instead of Xs.
If we really want the Xs there, then I'm going to go in, I'm going to format this just like I would a regular pivot table.
Field settings where you use a custom number format and a custom number format is going to say, if it's positive, I want an X in quotes.
If it's negative, I want nothing.
If it's zero, I want nothing.
Click OK.
Click OK.
We now have that report.
Names down the left-hand side, each name only once.
The Xs if someone is failing two classes like the fictional Alan Haynes.
That is going to show up with both Xs.
That's it.
Mike, I'm sure you're going to come up with some formula that’s going to make my head spin, but that’s how I do it.
Mike, let’s see what you have.
Mike: Thanks MrExcel.
I love the PowerPivot especially the way it was connected to that file.
So, every time you drop a new file down there, you could just update the PowerPivot.
I'm just going to-- actually, I want to take a look here.
What the person who sent this in did, they were trying to do approximate match, right, looking for the word geometry, math, calc in this math category creating this wild formula to put these Xs.
Now, a stroke of genius that MrExcel had is he said, forget that approximate.
If you're getting this file every single day, then the classes are more or less set.
So, why not just set up here and add an extra column with the category?
Then you can either use PowerPivot to connect the tables or-- I'm going to use VLOOKUP.
So, the way I'm going to solve this, I’m not going to use some wild formula to extract a unique list.
I'm just going to add two extra columns to this dataset; one that combines the names, just like MrExcel did, and one to do a VLOOKUP based on this to give me my category.
Then it's a simple pivot table.
So, I'm going to insert-- right-click the A and the B and insert.
All right, then I'm going to call this name and this one will be category, whether it's math or English or whatever.
I'm going to highlight that and do the format painter.
All right, now, same formula as MrExcel.
We're going to say, hey, give me the first name.
Now, that's one thing on the formula.
I'm going to use Shift 7, which is ampersand.
That's going to join a second thing, which is a space in double quotes, a second ampersand and then I'm going to grab the third thing, right?
Now I have that.
Now, I'm going to do my VLOOKUP based on this class over here.
So =VLOOKUP.
In essence, I'm going to look up all these names and so, we'll have a category, not of this specific class name, but of the category.
I'm going to come over here.
Now, I'm going to click table array here.
Now, I've inserted this.
Since this would be a daily process, you'd have to, you know, put this in there.
If it's in a different workbook, just open it and click on the workbook.
Then you can highlight the table and do a workbook reference.
I'm going to highlight, not the labels at the top, but just the first couple of records.
Ctrl Shift down arrow, F4 to lock it.
Now, up here you can see it has the sheet reference name and the cell references lock.
Now, I'm going to type a comma here.
If I were to click back on that other sheet doing a sheet reference, it would change the sheet reference.
So, I'm going to work up here.
Column index, there's just two, comma and then we can do exact MATCH for-- we're looking upwards.
All, right Ctrl enter.
Now, I have my two formulas.
I'm going to highlight-- well, so there's one formula.
Here's another formula, right?
So two completely different formulas.
I can go ahead and highlight both of them since they are stuffed to the right.
I can just double-click and send it down.
Now, I'm going to Ctrl down arrow just to check out and there we go.
Now, we can just do a simple pivot table.
Now, there are some-- I don't want to just do a single cell and not pay attention.
I don't want this up here.
So, I'm actually going to highlight this.
I actually, only need two letter grade.
I'm going to Ctrl Shift down arrow.
Now, I have my data set.
Go up to insert pivot table, pivot table, new workbook--worksheet.
Click OK.
Now, all right.
I'm going to drag name to row-- let's see if I can do this.
Okay and category to column.
Then now I have-- it looks just like the one MrExcel did after he did his pivot-- PowerPivot.
I'm going to drag letter grade.
Now, this is a letter, a text.
Usually, we’re dragging numbers to values, but when you drag any text it'll count by default.
All right, so, that's the basic setup and then you can do exactly-- still lots of MrExcel’s trick here.
I'm going to right-click and go down to value field settings.
I'm going to click on number format and go to custom.
Now, I'm going to just put an X in parenthesis because I don't think they'll ever be any negative numbers.
So, that'll work just fine.
X. Click OK.
Click OK.
Now we see all those Xs.
Maybe I want to adjust the report look here.
So, report, I'm going to say show in tabular and then grand totals-- how about off for rows and columns?
There I have my unique list of names with Xs in all the categories for which type of classes they have failed.
All right, send it back to MrExcel.
Bill: I thought you were going to get the point for some wild formula, but instead you get the point for knowing that when you create the custom number format, that blank cells are going to not get the format at all.
You put that X in there without any semicolons like, oh, you're going to get an X everywhere, but of course not.
If there is no value, you get nothing.
That’s great.
That's why I love these dueling Excel podcasts.
I learned things every week and I hope everyone else did too.
I want to thank everyone for stopping by.
We'll see you next week for another netcast from MrExcel and Excel is Fun.
I'm Bill Jelen from MrExcel. Mike Girvin from Excel is fun will be joining us.
This is episode 83: lookup and summarize.
Wow, this is a doozy today, Mike.
We have someone who's getting a file every single day.
It has a list of all of the students who are failing a class.
We have last name, first name, the name of the course, teacher, and some other things like that.
Now, these headings up here, including the merged record in A2, that's completely evil.
I don't like that at all.
The person is trying to create this report.
So, all of the last name, first name and then check this out; like to figure out the math column asks us to search for geometry or math or calculus or algebra-- painful.
The problem that we have is-- okay, first of all, we can't change failing kids.
It's coming in the way that it's coming in, right?
Some guy in IT creates that and we're just stuck with it.
He also wants to collapse this down to a single line.
All right, so, you know, this is a tough one.
I initially thought of a macro, but I'm going to try something crazy.
I'm going to try PowerPivot.
Let's see if PowerPivot can solve this.
I like PowerPivot especially because it might provide a way to save that Failing Kids out to the hard drive every day.
Open PowerPivot, refresh the data and have the report just update.
First thing I did is I took unique list of courses and created a lookup table rather than have that formula.
This seemed like the way to go.
I mean, hey, you only offer new courses once a year.
So, you only have to update this table once a year.
Let's create this table and use that.
We're going to go over to PowerPivot, PowerPivot window.
Rather than paste data in or link data in, I'm going to pretend like Excel is a foreign system and say that it's-- there is pull data in from an Excel file, click Next-- where is that file?
I saved it out in C, Failing Kids, there it is.
Although I have to tell you, I got rid of that row one and row two.
We're going to have to do that every day.
Before we open this, preview and filter.
There's a lot of columns that I really don't need.
I do need last name, first name.
Probably don't need grade.
I do need course, of course.
Don't need a lot of-- someone had to check everything.
I am going to leave one numeric column there.
That would be the grade in the course, even though they didn't want to show that.
I just want something pivotable that I can report.
So, uncheck all of this and click OK.
Click finish.
Success.
461 rows.
Good.
We can now add a new column in PowerPivot.
Equal first name, ampersand, quote, space, quote, ampersand, last name and we'll rename that just to be name.
This is good.
PowerPivot is learning that this is one of the rules.
Every time we import, they're going to add that column in automatically.
Beautiful, right?
Back to Excel.
Here's my lookup table.
I want to create under PowerPivot a linked table from this.
Click OK, so there's my course, there's my MapsTo and we're going to define a link from course here to course on the other sheet.
This is one of the powerful things about PowerPivot.
So, from failing kids, course to table 1 course, click create.
All right, PivotTable, new worksheet.
Okay, what are we going to do?
We're going to have the last name going down the left-hand side, put that in row labels.
We're going to have the percentage in sum values area and finally the MapsTo going across the top.
See as I now have a report that looks almost like what we wanted to look like, except for we have these numbers instead of Xs.
If we really want the Xs there, then I'm going to go in, I'm going to format this just like I would a regular pivot table.
Field settings where you use a custom number format and a custom number format is going to say, if it's positive, I want an X in quotes.
If it's negative, I want nothing.
If it's zero, I want nothing.
Click OK.
Click OK.
We now have that report.
Names down the left-hand side, each name only once.
The Xs if someone is failing two classes like the fictional Alan Haynes.
That is going to show up with both Xs.
That's it.
Mike, I'm sure you're going to come up with some formula that’s going to make my head spin, but that’s how I do it.
Mike, let’s see what you have.
Mike: Thanks MrExcel.
I love the PowerPivot especially the way it was connected to that file.
So, every time you drop a new file down there, you could just update the PowerPivot.
I'm just going to-- actually, I want to take a look here.
What the person who sent this in did, they were trying to do approximate match, right, looking for the word geometry, math, calc in this math category creating this wild formula to put these Xs.
Now, a stroke of genius that MrExcel had is he said, forget that approximate.
If you're getting this file every single day, then the classes are more or less set.
So, why not just set up here and add an extra column with the category?
Then you can either use PowerPivot to connect the tables or-- I'm going to use VLOOKUP.
So, the way I'm going to solve this, I’m not going to use some wild formula to extract a unique list.
I'm just going to add two extra columns to this dataset; one that combines the names, just like MrExcel did, and one to do a VLOOKUP based on this to give me my category.
Then it's a simple pivot table.
So, I'm going to insert-- right-click the A and the B and insert.
All right, then I'm going to call this name and this one will be category, whether it's math or English or whatever.
I'm going to highlight that and do the format painter.
All right, now, same formula as MrExcel.
We're going to say, hey, give me the first name.
Now, that's one thing on the formula.
I'm going to use Shift 7, which is ampersand.
That's going to join a second thing, which is a space in double quotes, a second ampersand and then I'm going to grab the third thing, right?
Now I have that.
Now, I'm going to do my VLOOKUP based on this class over here.
So =VLOOKUP.
In essence, I'm going to look up all these names and so, we'll have a category, not of this specific class name, but of the category.
I'm going to come over here.
Now, I'm going to click table array here.
Now, I've inserted this.
Since this would be a daily process, you'd have to, you know, put this in there.
If it's in a different workbook, just open it and click on the workbook.
Then you can highlight the table and do a workbook reference.
I'm going to highlight, not the labels at the top, but just the first couple of records.
Ctrl Shift down arrow, F4 to lock it.
Now, up here you can see it has the sheet reference name and the cell references lock.
Now, I'm going to type a comma here.
If I were to click back on that other sheet doing a sheet reference, it would change the sheet reference.
So, I'm going to work up here.
Column index, there's just two, comma and then we can do exact MATCH for-- we're looking upwards.
All, right Ctrl enter.
Now, I have my two formulas.
I'm going to highlight-- well, so there's one formula.
Here's another formula, right?
So two completely different formulas.
I can go ahead and highlight both of them since they are stuffed to the right.
I can just double-click and send it down.
Now, I'm going to Ctrl down arrow just to check out and there we go.
Now, we can just do a simple pivot table.
Now, there are some-- I don't want to just do a single cell and not pay attention.
I don't want this up here.
So, I'm actually going to highlight this.
I actually, only need two letter grade.
I'm going to Ctrl Shift down arrow.
Now, I have my data set.
Go up to insert pivot table, pivot table, new workbook--worksheet.
Click OK.
Now, all right.
I'm going to drag name to row-- let's see if I can do this.
Okay and category to column.
Then now I have-- it looks just like the one MrExcel did after he did his pivot-- PowerPivot.
I'm going to drag letter grade.
Now, this is a letter, a text.
Usually, we’re dragging numbers to values, but when you drag any text it'll count by default.
All right, so, that's the basic setup and then you can do exactly-- still lots of MrExcel’s trick here.
I'm going to right-click and go down to value field settings.
I'm going to click on number format and go to custom.
Now, I'm going to just put an X in parenthesis because I don't think they'll ever be any negative numbers.
So, that'll work just fine.
X. Click OK.
Click OK.
Now we see all those Xs.
Maybe I want to adjust the report look here.
So, report, I'm going to say show in tabular and then grand totals-- how about off for rows and columns?
There I have my unique list of names with Xs in all the categories for which type of classes they have failed.
All right, send it back to MrExcel.
Bill: I thought you were going to get the point for some wild formula, but instead you get the point for knowing that when you create the custom number format, that blank cells are going to not get the format at all.
You put that X in there without any semicolons like, oh, you're going to get an X everywhere, but of course not.
If there is no value, you get nothing.
That’s great.
That's why I love these dueling Excel podcasts.
I learned things every week and I hope everyone else did too.
I want to thank everyone for stopping by.
We'll see you next week for another netcast from MrExcel and Excel is Fun.
