Ron asks how to count how many times each model occurs in a column. Today, in Episode #1263, use a Pivot Table to quickly solve the problem and display results.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1263, Counting Entries.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
If you have a question for the netcast, please drop me a note Bill@MrExcel.com.
Today's question comes in from Ron.
Ron has this data that somebody sent him and he needs to get a count of how many times each model appears in Column B.
Now, okay advanced filter and then count if that's one way to do it, another way though is a Pivot Table of course.
I'm always going to do the Pivot Table because that's my favorite.
Now, a couple things we have to do.
We have to get rid of this blank row 2.
It's not adding anything.
It's gonna make the Pivot Table tougher to do so, we're going to right click and delete and then up here we're gonna add any kind of a heading. I don't care what it is. I'm just going to call it Sequence.
Just put the tables one heading above every column so now, Insert, Pivot Table, click OK.
Now you see we have a list of the fields.
I'm gonna take the Model field and drag it to Row Labels that gives me each model exactly once and then, I'm going to take any other text field including the model field and drag it to the value suction that will give me for every model exactly how many times it occurred with a grand total at the bottom.
Now, this is a live pivot table if you don't want it to be a pivot table anymore, if you want to drag that somewhere and reuse the data then you might want to copy, copy and then paste values, so you drop down here, that's the paste values icon.
All right, now it's not a Pivot Table anymore.
We just have our simple report easy way to go great way to create a snapshot now.
If you needed... If you needed to have something that's going to constantly update as you add more models, Macro, maybe the formulas would work, but it's not going to catch new models, so probably the snapshot is another the best way to go.
Hey! I wanna thank you Ron for sending that question in.
I want to thank you to stopping by. See next time with another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1263, Counting Entries.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
If you have a question for the netcast, please drop me a note Bill@MrExcel.com.
Today's question comes in from Ron.
Ron has this data that somebody sent him and he needs to get a count of how many times each model appears in Column B.
Now, okay advanced filter and then count if that's one way to do it, another way though is a Pivot Table of course.
I'm always going to do the Pivot Table because that's my favorite.
Now, a couple things we have to do.
We have to get rid of this blank row 2.
It's not adding anything.
It's gonna make the Pivot Table tougher to do so, we're going to right click and delete and then up here we're gonna add any kind of a heading. I don't care what it is. I'm just going to call it Sequence.
Just put the tables one heading above every column so now, Insert, Pivot Table, click OK.
Now you see we have a list of the fields.
I'm gonna take the Model field and drag it to Row Labels that gives me each model exactly once and then, I'm going to take any other text field including the model field and drag it to the value suction that will give me for every model exactly how many times it occurred with a grand total at the bottom.
Now, this is a live pivot table if you don't want it to be a pivot table anymore, if you want to drag that somewhere and reuse the data then you might want to copy, copy and then paste values, so you drop down here, that's the paste values icon.
All right, now it's not a Pivot Table anymore.
We just have our simple report easy way to go great way to create a snapshot now.
If you needed... If you needed to have something that's going to constantly update as you add more models, Macro, maybe the formulas would work, but it's not going to catch new models, so probably the snapshot is another the best way to go.
Hey! I wanna thank you Ron for sending that question in.
I want to thank you to stopping by. See next time with another netcast from MrExcel.
