Macro to count items and create summary

[TBR1316]

Hi all, I'm new to this forum and haven't been able to find an answer to my question. I'm currently working with a an excel file that looks something like this:

Column A Column B
1Review Question 1-10 Essay
1Review Question 1-11 Essay
1Review Question 1-12 Essay
2Exercise 01-01A Matching
2Exercise 01-02A FITB
4Exercise 01-01B FITB
4Exercise 01-02B Matching
3Exercise 01-01B FITB
3Exercise 01-02B Matching
1Exercise 02-01A FITB
1Exercise 02-02A FITB
1Exercise 02-03A FITB

I have to filter these spreadsheets to get counts for each of the different "types" in Column B and then keep a record of that count somewhere else. I'm wondering if a macro might allow me to 1) count the occurrences of a value in column B and 2) create a summary of the values and the counts. Something like this:

Essay 3
Matching 3
FITB 6

This is just a portion of the spreadsheet and the spreadsheets can vary greatly in terms of the number of items listed. Ideally, I'd like the macro to create a new worksheet called "Summary" and list all of the item types and counts there. Also, the items listed above in Column B are only a sampling of the available values - providing a list of what might occur in that column, wouldn't really work.

Any ideas? I'm stumped - but I know there has to be a more efficient way to do this than how I'm currently doing it.

Thanks in advance for your help!!

[simarui]

this seems to be working:

Please Login or Register  to view this content.

as attached.

also note - you can only run this once - if you need something that can be run multiple times without duplicating counts on each value i'd suggest adding a clearcontents line immediately after setting output = summary.

***second edit***
i just added that line... it probably should be there, delete it if you don't want it for some reason.

[TBR1316]

I think this is working. Thanks so much!!

[simarui]

glad i could help, don't forget to mark the post as solved