Transpose & offset data from a table

[Eva]

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?

 

[Bob Bridges]

Eva, I can't tell exactly what you're saying. I THINK you have data laid out
in multiple lines and now you want to change the layout so that multiple
lines are combined into one. Here's what I don't know:

1) What's supposed to happen to the two cols labeled "#"? Do you need them
preserved, or discarded?

2) Your starting format has an unpredictable number of debits and credits,
and in the output you need a fixed number of debit and credit columns. 2a)
Did you really want the target layout to be set up Db, Cr, Db, Db rather than
(for instance)? Wouldn't it be more convenient just to show debits with
positive numbers and credits with negatives (or of course vice versa)? or
perhaps Db, Db, Db, Cr, Cr, Cr? And 2b) no matter what the target layout,
what do you want done when a starting # has more debits or credits than the
target layout can handle? What, for instance, should be done with the second
credit in #1958, in your example? Do you just want to start a second line?

Depending on your answer to 2a, chances are it's going to be simpler to do
this in VBA than with worksheet functions.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Transpose data.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_21_10a.xlsx

 

[Eva]

Hi
Thank you for taking your time. These are my answers:
1) I don't need cols labeled "#".
2) My starting format has an unpredictable number of debits and credits same
as output
2) I should be fine if we can do it with cr and dr positive and negative
numbers
2) Db, Db, Db, Cr, Cr, Cr...etc is also fine
2b) I would like to have as many debits and credits as I have in the table
2b) The second credit (#1958 )should be there as well in a separate column -
I don't want to to start a second line

...I am not so good with VBA...I started to learn more about it, but I need
more time.

 

[Billy Liddel]

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]

Hi
Thank you for taking your time to make a pivot table for me. I analyzed it
carefully and I did the same thing and it works! I am really grateful!
It is really awesome.

 

[Eva]

Thank you. Macro works fine

 

[Billy Liddel]

Glad to help Eva

Thanks for the response.

Peter