I have 2500 records. There are 40 unique values in one column. Which values occur most often? It takes just six clicks to solve this problem in Excel.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1948.
What are the most common items in an Excel list?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I have a list of all of the street names in Chicago.
And when this data came down, I was interested in this Suffix field: Place (PL), Street (ST) and so on.
I want to figure out, what the most popular of those were.
Now, if I just needed a list, a unique list of them, from the 2500 street names we have here, I could just copy it over off to the side, and out on the DATA tab choose Remove Duplicates, and click OK.
And that gives me the list of 24 unique values.
But it doesn't tell me, which are the most popular.
And they're not in any particular order, it’s just the order that it founded in this original list.
And so, I want to do something better, want to come up with which ones are actually popular.
So I'm going to choose one cell in the original data, on the INSERT tab choose Pivot Table.
Now, Pivot Tables are usually for adding up numbers and we really don't have any numbers here, but that's okay.
It's great for doing frequency analysis.
I'm going to take that Suffix field and drag it down to the ROWS area.
Now you see, that I have pretty much exactly what I had with removed duplicates, although it's been sorted alphabetically now.
And then, any other text field, and in this case Suffix, is the text field, I want to drag that to the VALUES area.
And it now gives me a Count of how many items… of how many times each item appears.
I’m going to just sort this data Z-to-A, alright, and so we have “AVE” at the top of the list.
Now, my case: I don't need the abbreviations.
I'm just going to type out the most common ones over here, “Place”.
Can't figure out “ER”… What the heck is “ER”?
So I'll skip that one, “Drive”… Or “XR”?
What the heck is “XR”?
But “Court”… Alright, and “Boulevard”.
You can see, “Boulevard” really falls off.
These are the ones, that are really kind of strange.
Although, I'm sitting here in Florida on a Parkway.
So I'm going to include “Parkway” just for the heck of it.
But at least, there we have a nice short list of, you know, not the ones, that I could just think of off the top of my head, but the actual ones, that occur in, well, real life.
At least in the city of Chicago.
Now, this concept would work with whatever data you have, so if you wanted to, you know, figure out which products appeared most often on a list, or which names are most common, you just put together a Pivot Table and drag a text field to the VALUES area, to get a count.
Oh, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1948.
What are the most common items in an Excel list?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I have a list of all of the street names in Chicago.
And when this data came down, I was interested in this Suffix field: Place (PL), Street (ST) and so on.
I want to figure out, what the most popular of those were.
Now, if I just needed a list, a unique list of them, from the 2500 street names we have here, I could just copy it over off to the side, and out on the DATA tab choose Remove Duplicates, and click OK.
And that gives me the list of 24 unique values.
But it doesn't tell me, which are the most popular.
And they're not in any particular order, it’s just the order that it founded in this original list.
And so, I want to do something better, want to come up with which ones are actually popular.
So I'm going to choose one cell in the original data, on the INSERT tab choose Pivot Table.
Now, Pivot Tables are usually for adding up numbers and we really don't have any numbers here, but that's okay.
It's great for doing frequency analysis.
I'm going to take that Suffix field and drag it down to the ROWS area.
Now you see, that I have pretty much exactly what I had with removed duplicates, although it's been sorted alphabetically now.
And then, any other text field, and in this case Suffix, is the text field, I want to drag that to the VALUES area.
And it now gives me a Count of how many items… of how many times each item appears.
I’m going to just sort this data Z-to-A, alright, and so we have “AVE” at the top of the list.
Now, my case: I don't need the abbreviations.
I'm just going to type out the most common ones over here, “Place”.
Can't figure out “ER”… What the heck is “ER”?
So I'll skip that one, “Drive”… Or “XR”?
What the heck is “XR”?
But “Court”… Alright, and “Boulevard”.
You can see, “Boulevard” really falls off.
These are the ones, that are really kind of strange.
Although, I'm sitting here in Florida on a Parkway.
So I'm going to include “Parkway” just for the heck of it.
But at least, there we have a nice short list of, you know, not the ones, that I could just think of off the top of my head, but the actual ones, that occur in, well, real life.
At least in the city of Chicago.
Now, this concept would work with whatever data you have, so if you wanted to, you know, figure out which products appeared most often on a list, or which names are most common, you just put together a Pivot Table and drag a text field to the VALUES area, to get a count.
Oh, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
