Hi,

You can do this with either the Data, Subtotal command or with the PivotTable.

My preference would almost always be the pivot table. But I will outline
both methods:

Data, Subtotal:
1. You would sort the data by Account by City - Account being the primary
sort.
2. Then select the data and choose Data, Subtotal,
3. Choose City in the At each change in, leave the default function Sum,
Check the field you want subtotaled, Amount (column BJ).
4. Click OK

With the range still selected
1. Choose Data, Subtotal again
2. Choose Account from At each change in
3. Leave Sum on, leave Amount checked
4. Uncheck Replace current subtotals.
5. click OK

Inserting Blank Lines:

1. Highlight the City column down as far at the data goes
2. Press Ctrl+F, type Total into the Find what box
3. Choose Find All
4. Hold down the Shift key and click the last entry in the Find All window
5. Close the dialog box
6. Press Ctrl++ (control and plus key), select Entire row

=====================
Pivot Table

1. Select your data and choose Data, PivotTable and PivotChart Report
2. Click Next twice
3. In the 3rd step of the Wizard click the Layout button
4. Drag the Account field to the Row area
5. Drag the Name field to the Row area below Acct
6. Drag the State field below the Name field in the Row area
7. Drag the Amount field to the Data area
8. Double-click the City field button in the Row area and click Layout, and
check Insert blank line after each item. Click OK, OK, OK, Finish

This isn't perfect but it may meet your needs.



--
Thanks,
Shane Devenshire


"ATanker62" wrote:

Hello all,

Here is my problem, large data set I have already organized it by state,
city, outergroup number.

AC AH AI BJ
188007 Williams IA 50
188007 Williams IA 126.851
180007 Belgrade MT 110
188111 Russelvile AR 70.014
188111 Texakana AR 169.001
188111 Barstow CA 60.006
188111 Barstow CA 50.108

What I want to do is the following

Insert a blank line between different cities, then a subtotal by the city,
then a total by the group number.

Data to look like this after work is done

AC AH AI BJ
188007 Williams IA 50
188007 Williams IA 126.851
176.851

180007 Belgrade MT 110
110.00

276.851

188111 Russelvile AR 70.014
70.014
188111 Texakana AR 169.001
169.001
188111 Barstow CA 60.006
188111 Barstow CA 50.108
110.108

349.123

Thanks