Dueling Podcast #59 deals with Sorting Summary Data - collected results of a survey. Counting the responses is easy while keeping the results in the original order. Mike and Bill show us how to accomplish the task with Episode #1313.
Transcript of the video:
Hey, it another dueling Excel podcast.
I'm Bill Jelen form MrExcel, Mike Girvin form Excel Is Fun will be joining us.
This is episode 59, Sorted Summary.
All right, Hey, great question here let's say that you have data like this survey results about a New Soda Pop and you want to create this report showing how many times each answer is given, but here's the hard part it has to be in this sequence until the it has to be in this sequence it was going to be very easy Insert, Pivot Table, OK and then what do we have to do we just have to take that field and put it in Row Labels and also take that field and put it down in Sum Values because it's text we automatically get the count, but see this it shows up not in the right sequence.
So, here's what we're going to do.
I'm going to delete this Pivot Table temporarily.
We're going to go back to a spreadsheet where we have the categories in the order that they need to be.
I'm going to create a custom list from that, custom list Excel 2003, Tools, Options go to the sixth tab along the bottom, Excel 2007, hit the office button go to Excel Options, it's right there in the center of the page.
Excel 2010, hit the file button go down to Options, go to Advanced, and then start to look it's somewhere down here I can hardly ever find out why they keep moving this thing around.
I have no idea there it is almost near the bottom Edit Custom Lists.
All right! So, there's my list it's selected A2 to A6.
A2 to A6, import BAM now it understands, Excellent, Good, Like, Not like, Bad as well as it understands, Sunday, Monday, Tuesday, you see that I've added a few other ones you're like A, B, C, D, E, F, that allows me to fill that.
Okay! Now, that we have that custom list set up we only have to set it up once on this computer, then we can go back to our data and again Insert, Pivot Table, OK, Survey Results to Row Labels, Survey Results to Sum Values, check that out because the custom list is there, it automatically respects our list and we have the answers they wanted the word frequency here, easy enough to do we'll just go to Active Field and call that Frequency there we go, press ENTER.
Nice, beautiful, awesome. Mike let's see what you have.
Mike: Thanks MrExcel.
Pivot Table, Custom List, totally awesome that's the winner.
Hey, custom list I, I still just like you MrExcel I can't believe how many times they've moved it, why don't they just decide on some place and keep it?
Hey, let's go ahead and look over here.
Now, I guess one way to do this is we could go since we have in a list over here categories.
MrExcel did the custom list, I'm going to go ahead and use a define name to do this.
Now, I want to name this categories and since there's a name right here, I'm going to highlight the name, and then the range I want that name to represent and I'm going to use the keyboard shortcut Control+Shift+ F3, Control+Shift+F3, it says hey, your name is in the Top row, everything below will be, will have the name categories.
I'm going to click OK, I can quickly check it, I can name box point to the drop-down and then see if Categories, sure enough, i select it and it highlights it.
Now, I'm going to come over here, I'm going to copy this Control+C, Control+V, and then I, I know that there's five categories.
So, I'm going to highlight one, two, three, four, five cells, type an equal sign and then I'm going to hit the F3 key and that's Paste Name and then I'm going to go ahead and double-click categories and I'm going to use Control+Shift and Enter that allows me to put that name in the same order as that other sheet, you can see it's got the name entered as an array, those little curly brackets or Excel telling you that it understands that this is an array when I do Control+Shift+Enter, that's me telling Excel it's an array.
So, really Control+Shift+Enter, I'm telling Excel, curly brackets Excels telling me.
Now, I can just come over here Control+Asterisks to highlight that whole table.
If I want some borders, maybe some color right here Control+B, and now, I can just use COUNTIF, COUNTIF, COUNTIF what well here's the range, click their Control+Shift+down arrow, F4 to lock it and jump the screen back in view comma and the criteria will be one cell to my left relative cell reference, actually I don't need to put that close parenthesis just Control+Enter, that was an extra key I didn't have to hit and then double click and send it down and sure enough we have our labels in the right order our COUNTIF formula.
Wow! Not like wins bad is pretty right up there like not very many people thought this is great.
All right, I'll throw back to MrExcel.
Bill: Hey, yeah! All right, Mike, that was good the COUNTIF function.
Yeah, the thing that I love and I'm sure that people watch this all the time.
You have, you have speak cells on enter turn on all the time.
Don't you? It always catches me you're talking you choose something you hear the computer say, frequency.
That's cool, like I think it would drive me crazy after a while, but I I'm guessing you have an honor.
At least you haven't turned on for the dueling Excel podcast hard to say.
So, hey, hope everyone has a great holiday season here.
We'll see you next time for another dueling Excel podcast from MrExcel Excel is fun.
I'm Bill Jelen form MrExcel, Mike Girvin form Excel Is Fun will be joining us.
This is episode 59, Sorted Summary.
All right, Hey, great question here let's say that you have data like this survey results about a New Soda Pop and you want to create this report showing how many times each answer is given, but here's the hard part it has to be in this sequence until the it has to be in this sequence it was going to be very easy Insert, Pivot Table, OK and then what do we have to do we just have to take that field and put it in Row Labels and also take that field and put it down in Sum Values because it's text we automatically get the count, but see this it shows up not in the right sequence.
So, here's what we're going to do.
I'm going to delete this Pivot Table temporarily.
We're going to go back to a spreadsheet where we have the categories in the order that they need to be.
I'm going to create a custom list from that, custom list Excel 2003, Tools, Options go to the sixth tab along the bottom, Excel 2007, hit the office button go to Excel Options, it's right there in the center of the page.
Excel 2010, hit the file button go down to Options, go to Advanced, and then start to look it's somewhere down here I can hardly ever find out why they keep moving this thing around.
I have no idea there it is almost near the bottom Edit Custom Lists.
All right! So, there's my list it's selected A2 to A6.
A2 to A6, import BAM now it understands, Excellent, Good, Like, Not like, Bad as well as it understands, Sunday, Monday, Tuesday, you see that I've added a few other ones you're like A, B, C, D, E, F, that allows me to fill that.
Okay! Now, that we have that custom list set up we only have to set it up once on this computer, then we can go back to our data and again Insert, Pivot Table, OK, Survey Results to Row Labels, Survey Results to Sum Values, check that out because the custom list is there, it automatically respects our list and we have the answers they wanted the word frequency here, easy enough to do we'll just go to Active Field and call that Frequency there we go, press ENTER.
Nice, beautiful, awesome. Mike let's see what you have.
Mike: Thanks MrExcel.
Pivot Table, Custom List, totally awesome that's the winner.
Hey, custom list I, I still just like you MrExcel I can't believe how many times they've moved it, why don't they just decide on some place and keep it?
Hey, let's go ahead and look over here.
Now, I guess one way to do this is we could go since we have in a list over here categories.
MrExcel did the custom list, I'm going to go ahead and use a define name to do this.
Now, I want to name this categories and since there's a name right here, I'm going to highlight the name, and then the range I want that name to represent and I'm going to use the keyboard shortcut Control+Shift+ F3, Control+Shift+F3, it says hey, your name is in the Top row, everything below will be, will have the name categories.
I'm going to click OK, I can quickly check it, I can name box point to the drop-down and then see if Categories, sure enough, i select it and it highlights it.
Now, I'm going to come over here, I'm going to copy this Control+C, Control+V, and then I, I know that there's five categories.
So, I'm going to highlight one, two, three, four, five cells, type an equal sign and then I'm going to hit the F3 key and that's Paste Name and then I'm going to go ahead and double-click categories and I'm going to use Control+Shift and Enter that allows me to put that name in the same order as that other sheet, you can see it's got the name entered as an array, those little curly brackets or Excel telling you that it understands that this is an array when I do Control+Shift+Enter, that's me telling Excel it's an array.
So, really Control+Shift+Enter, I'm telling Excel, curly brackets Excels telling me.
Now, I can just come over here Control+Asterisks to highlight that whole table.
If I want some borders, maybe some color right here Control+B, and now, I can just use COUNTIF, COUNTIF, COUNTIF what well here's the range, click their Control+Shift+down arrow, F4 to lock it and jump the screen back in view comma and the criteria will be one cell to my left relative cell reference, actually I don't need to put that close parenthesis just Control+Enter, that was an extra key I didn't have to hit and then double click and send it down and sure enough we have our labels in the right order our COUNTIF formula.
Wow! Not like wins bad is pretty right up there like not very many people thought this is great.
All right, I'll throw back to MrExcel.
Bill: Hey, yeah! All right, Mike, that was good the COUNTIF function.
Yeah, the thing that I love and I'm sure that people watch this all the time.
You have, you have speak cells on enter turn on all the time.
Don't you? It always catches me you're talking you choose something you hear the computer say, frequency.
That's cool, like I think it would drive me crazy after a while, but I I'm guessing you have an honor.
At least you haven't turned on for the dueling Excel podcast hard to say.
So, hey, hope everyone has a great holiday season here.
We'll see you next time for another dueling Excel podcast from MrExcel Excel is fun.
