Macro to capture/count data in a column

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
66
Office Version
  1. 365
Platform
  1. Windows
Good Morning!

I'm tryig to figure out how to create macro that will review a column and list and count values in the field.

Example

The Column will hav "Unit" as defned by House, Apartment, Barn, whatever.

My goal is to try to run something that go through the column and capture all values of like names and count them.

Unit (Column Header)
House
Apartment
Apt
Hus
Barn
Brn
Blah
Blah
Blah

and the output would be

House 1
Apartment 1
Apt 1
Hus 1
Barn 1
Brn 1
Blah 3
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,060
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there.

It looks like a pivot table will do exactly what you want.

Just create a new pivot table, choose your range as the data source, and then drag the column header into the row labels area, and drag it again into the values area.

LMNOPQRS
13
14 Unit (Column Header) Row LabelsCount of Unit (Column Header)
15 House Apartment1
16 Apartment Apt1
17 Apt Barn1
18 Hus Blah3
19 Barn Brn1
20 Brn House1
21 Blah Hus1
22 Blah Grand Total9
23 Blah
24

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:92px;"><col style="width:203px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

You should get something like the above.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,453
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top