Excel Question #46

Alex · 03-22-2010, 03:21 PM

For the first one.

Look up pivot tables.

As a simple example:

1. Make sure your state column has a column header (Such as "State")
2. Select Column H
3. Data > Pivot Table and Pivot Chart Report
4. Leave "Microsoft Office Excel list or database" and ""PivotTable" selected.
5. Click Next
6. PivotTable and Pivot Chart Wizard - Step 2 of 3 should show your Column H in the Range field ("$H:$H").
7. Click Next
8. Provide a location where the data can go. Probably you should leave "New worksheet" selected.
9. Click "Layout"
10. Drag "State" into the Row section of the window that pops up. Then drag it into the Data section.
11. Click Ok.
12. Click Finish.

On a new worksheet you should have a list of all the states in the column as well as the count in a separate row. If you want the data free from the pivot table, just copy the cells in the list and paste them to a new location.

03-22-2010, 03:21 PM	#10
Alex . Join Date: Feb 2005 Posts: 13,354	For the first one. Look up pivot tables. As a simple example: 1. Make sure your state column has a column header (Such as "State") 2. Select Column H 3. Data > Pivot Table and Pivot Chart Report 4. Leave "Microsoft Office Excel list or database" and ""PivotTable" selected. 5. Click Next 6. PivotTable and Pivot Chart Wizard - Step 2 of 3 should show your Column H in the Range field ("$H:$H"). 7. Click Next 8. Provide a location where the data can go. Probably you should leave "New worksheet" selected. 9. Click "Layout" 10. Drag "State" into the Row section of the window that pops up. Then drag it into the Data section. 11. Click Ok. 12. Click Finish. On a new worksheet you should have a list of all the states in the column as well as the count in a separate row. If you want the data free from the pivot table, just copy the cells in the list and paste them to a new location.
	Submit to Quotes