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

Hello,
I don't know you but I wnt to pay someone to write a simple
accounting program using excel for my new business. I think it is easy for a
person familiar with excel programming. Please tell em if you can help. My
email

Please don't spam thread with your advertisement.

Barb Reinhardt

"Cornflicks" wrote:

Hello,
I don't know you but I wnt to pay someone to write a simple
accounting program using excel for my new business. I think it is easy for a
person familiar with excel programming. Please tell em if you can help. My
email

This looks like you could use Data|subtotals (twice).

Or if you really only want the summary, you may want to learn about pivottables.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

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

--

Dave Peterson

Hi,

Not proper subtotals but this gives the data layout and sum you want

Sub standard()
Dim MyRange
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 2).Value < Cells(x - 1, 2).Value Then
Rows(x).EntireRow.Insert
End If
Next
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
Set MyRange = Range("D1:D" & lastrow)
For Each c In MyRange
total = total + c.Value
If c.Offset(1, 0).Value = "" Then
c.Offset(1, 1).Value = total
total = 0
End If
Next
End Sub

Mike

"Dave Peterson" wrote:

This looks like you could use Data|subtotals (twice).

Or if you really only want the summary, you may want to learn about pivottables.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

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

--

Dave Peterson

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