Thank you. Macro works fine :)
--
Greatly appreciated
Eva


"Billy Liddel" wrote:

Eva

You seem to have the data set up sensible already but you need to fill in
the First Id so that each row has the ID against it. Assuming that the blank
lines should have the last id sabove then this macro will add the lines for
you.

Sub FillIds()
Dim LRows as Long, i as long

LRows = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To LRows
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1) = Cells(i - 1, 1)
End If
Next i
End Sub

The macro is copied into the VB Editor. ALT + F11, Insert, Module and paste
the code into the module.

Make sure that you are in the sheet with the table and From the VB editor
press F5 to run the code, or, From the sheet Press ALT + F8 and select the
code and click Run.

You can now use a Pivot table or Data Subtotals with a Sum in each change in
Id.

SUb Totals look like this:

Id Acc# Dr Cr
1271 1030 4.67
1271 2200 0.27
1271 6050 4.41
1271 Total 4.68 4.67
1288 1030 7.87
1288 6090 7.87
1288 Total 7.87 7.87
1617 1030 61.9


And the Pivot Table looks like this.

Acc#
Id Data 1000 1030 2200
1271 Sum of Dr 0.27
Sum of Cr 4.67
1288 Sum of Dr
Sum of Cr 7.87

The Grand Total in the PT are on the Right.

Make sure that Sum is selected and not count.

If you really want the data transposed you will need a macro so post back or
start a new thread.

HTH
Peter


"Eva" wrote:

I have the following table and I would like to have each item on one row

# Acc# Dr Cr # Count Info
1271 1030 4.67 1 3 Cr
2200 0.27 1 3 Dr
6050 4.41 1 3 Dr
1288 1030 7.87 2 2 Cr
6090 7.87 2 2 Dr
1617 1030 61.9 3 4 Cr
2200 2.59 3 4 Dr
6050 4.11 3 4 Dr
6550 55.2 3 4 Dr
1958 1000 45.9 4 5 Cr
1000 39.2 4 5 Cr
2200 4.01 4 5 Dr
6310 37.4 4 5 Dr
6630 43.7 4 5 Dr


There is info that can help with determining items
Count - checks how many items should be transposed
Info - provides the information wheather is hould be under Cr or Dr accont

This is what I need as the result

DrAc DrAm CrAc CrAm DrAc DrAm DrAc DrAm
2200 0.27 1030 4.67 6050 4.41
6090 7.87 1030 7.87
2200 2.59 1030 61.9 6050 4.11 6550 55.2
2200 4.01 1000 45.9 6310 37.4 6630 43.7
etc
Can you help?
--
Greatly appreciated
Eva