convert multicolumn/multirow table into two structured/formatted columns => feasible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

markx - 19 Sep 2007 15:31 GMT

Hi all,

I know this is an extremely tough one and I don't expect here any final
answer on this - some enlightening guidelines (f. ex. if it's feasible...)
would be already great!

I have a huge excel table (database) that involves child (unique) and parent
(not unique) accounts and is constructed like this:

A B C
D,E,F etc...
child1 parent of this child1 parent of the account on the left
etc... (until the highest level)
child2 parent of this child2 parent of the account on the left
etc... (until the highest level)
child3 parent of this child3 parent of the account on the left
etc... (until the highest level)
child4 parent of this child4 parent of the account on the left
etc... (until the highest level)
child5 parent of this child5 parent of the account on the left
etc... (until the highest level)
child6 parent of this child6 parent of the account on the left
etc... (until the highest level)
child7 parent of this child7 parent of the account on the left
etc... (until the highest level)
child8 parent of this child8 parent of the account on the left
etc... (until the highest level)
child9 parent of this child9 parent of the account on the left
etc... (until the highest level)
...
childn parent of this childn parent of the account on the left
etc... (until the highest level)

I have more than 3000 rows and 15 columns (=levels) of this three-like
structure... (some rows have 15 levels, some others finish as early as after
4 levels, then it's blank)
Based on this, I would like to convert it into an accounting-like scheme (f.
ex. column A = accounts; column B = amounts (or SUM formulas where
appropriate)):

child1
child2
child3
Parent of these 3 child accounts (formatted differently (level 1 parent))
(=SUM of all the relevant childs)
child4
child5
Parent of these 2 accounts (formatted level 1 parent) (=SUM of all the
relevant childs)
Parent of both parents above... (formatted level 2 parents) (=SUM of all the
relevant parents level 1)
etc... (until the end)

Of course if any other structure is easier to achieve (and will make the
same job, i.e. to recreate balance-sheet structure), I'm open to all
suggestions...
Many thanks in advance for any comments on this!

Regards,
Mark

Reply to this Message

Joel - 19 Sep 2007 18:28 GMT

I think this was real easy.
I moved the 3 column data to columns A - c. then I sorted on column c as
first Key and column B as 2nd key. next I compared column b to determine
where each parentt acount started and end. Then I put the sum of the parents
account in column d.

let me know if I'm right or what modification are required.

Sub refomtchild()

RowCount = 1
Do While Cells(RowCount, "A") <> ""
If Not IsEmpty(Cells(RowCount, "E")) Then
Rows(RowCount + 1).Insert
LastCol = Cells(RowCount, Columns.Count). _
End(xlToLeft).Column
Set cutrange = Range(Cells(RowCount, "D"), _
Cells(RowCount, LastCol))
cutrange.Cut Destination:=Cells(RowCount + 1, "A")
End If
RowCount = RowCount + 1
Loop

'sort by parent then child
Range("A2:C" & (RowCount - 1)).Sort _
Key1:=Range("C2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess

'put in subtotals
RowCount = 1

StartRow = 1
Do While Cells(RowCount, "A") <> ""
If Cells(RowCount, "B") <> Cells(RowCount + 1, "B") Then
Cells(RowCount, "D").Formula = "=Sum(A" & StartRow & _
":A" & RowCount & ")"
StartRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End Sub

> Hi all,
>
[quoted text clipped - 56 lines]
> Regards,
> Mark

Reply to this Message

markx - 20 Sep 2007 11:03 GMT

Hi Joel,
Many thanks for your involvement and quick answer!

I've tried your macro and unfortunately the problem is more complex than
this... :-(
Probably I haven't made myself sufficiently clear...

The data (account names) are already in area A1:N3100. The problem is that
for some accounts it will take, let's say, sevel levels to arrive to the
top/final parent account "Total Assets", and for others, they will need only
2 levels to get there. So, in one row columns A to G will be filled in
(child1=>its parent=>parent's parent=>...=>"Total assets" account), and in
another, we'll have only columns A to C filled in (child1=>its
parent=>immediately "Total assets" account). You see? So, we can have the
same account name in several different columns, everything depends of the
depth of a particular asset class.

Second thing is that I don't have the amounts in this table for the moment,
only account names (then, once I arrive to have the accounts structured in
only one column (with the subtotals etc...), I'll add the extraction formula
from external database for every child account (for this, I know how to deal
with...) to the column next to it: so, in column A i plan to have all the
account names, and in column B I'll have the amounts extracted, except for
the subtotals (=parent accounts).

Sorry if I wasn't sufficiently explicit before...
Mark

>I think this was real easy.
> I moved the 3 column data to columns A - c. then I sorted on column c as
[quoted text clipped - 107 lines]
>> Regards,
>> Mark

Reply to this Message

Joel - 20 Sep 2007 11:28 GMT

I still need clrification. If you have

A B C D E F G

What do you want in A - C?
for example
A B C
B C D
C D E
E F G

or maybe just
A B G

I can't tell from your instructions how you want me to split the A - G
columns. Any way is very easy for me. I've done it a hundred times before
and I know that it can be done many many many diffferent ways. I just want
to do it the way you need it done.

> Hi Joel,
> Many thanks for your involvement and quick answer!
[quoted text clipped - 135 lines]
> >> Regards,
> >> Mark

Reply to this Message

markx - 20 Sep 2007 15:15 GMT

Hi Joel,

That's the difficulty - I don't want to simply split the columns, but create
two columns based on relations in the initial table:

So, if simplified, I have f.ex. the following table (only account numbers,
no amounts), where the parent account is always on the right (B1 parent of
A1, C1 parent of B1, and the highest level account is D1 (=no parent account
on the right side next to it):

A1 B1 C1 D1
A2 B1 C1 D1
A3 B1 C1 D1
A4 B2 C1 D1
A5 B2 C1 D1
A6 B3 D1
A7 B3 D1
A8 B3 D1
A9 D1
A10 C1 D1
B1 C1 D1
B2 C1 D1
B3 D1
C1 D1
C2 D1

What I want to achieve is (based on the relationships above) to have, on
another worksheet, in the first column structured listing of all the
accounts (A1...D1), and in the second column (next to it) the adequate
formulas for all the parent accounts (always SUMs), as below:

A1
A2
A3
B1 (=A1+A2+A3)
A4
A5
B2 (=A4+A5)
A6
A7
A8
A9
B3 (=A6+A7+A8)
A10
C1 (=B1+B2+A10)
C2
D1 (=A9+B3+C1+C2)

Still easy :-)?

>I still need clrification. If you have
>
[quoted text clipped - 171 lines]
>> >> Regards,
>> >> Mark

Reply to this Message

Joel - 20 Sep 2007 16:34 GMT

iT JUST KEEPS ON GETTING EASIER ALL THE TIME.

questions:
1)exactly what do you want me to put in the cell for

(=A4+A5)
A4 and A5 are names not numbers!

2) Should the results in one abovve be an amount or a formula.
3) The break down you show is only on column B. Is ther additional break
down on other columns.
4) Can I get the amonts from the last column on each row?

> Hi Joel,
>
[quoted text clipped - 221 lines]
> >> >> Regards,
> >> >> Mark

Reply to this Message

markx - 21 Sep 2007 14:26 GMT

Hi Joel,

Thanks for your questions and your kind follow up...!

So, to recapitulate:

In column "A", there will be account names

In column "B", there will be amounts associated with the accounts written in
column A (The amounts will be retrieved only for first level child accounts,
for all other we need formulas)

1) So, when in my example I write "(=A4+A5)", I want to have the formula
that will sum the amounts associated with the accounts A4 and A5 (that I'll
retrieve later through SQL and VLOOKUP). This formula, (as well as all the
raw amounts extracted later for all the child accounts), will be then in
column B.

To get back to my last example (now I put the rows and columns names, too):

.......column A.............column B

1 A1 <left blank, numbers retrieved later>
2 A2 <left blank>
3 A3 <left blank>
4 B1 insert formula =SUM(B1:B3)
5 A4 <left blank>
6 A5 <left blank>
7 B2 insert formula =SUM(B5:B6)
8 A6 <left blank>
9 A7 <left blank>
10 A8 <left blank>
11 A9 <left blank>
12 B3 insert formula =SUM(B8:B10)
13 A10 <left blank>
14 C1 insert formula =B4+B7+B13
15 C2 <left blank>
16 D1 insert formula =SUM(B11:B12)+SUM(B14:B15)
...

2) So, to answer your second point, for all the parent accounts, yes, I
would like to have formulas

3) The goal is to have only two columns at the end, and ideally all the
relationships should be analyzed and taken into account, although I can
arrange to have all the accounts listed in column A (and their parents in
column B) => so, yes, we can admit that as input we'll have only two columns
(column A = children, and column B = parents, BUT OF COURSE the same account
can be both parent for one account and then child for another(!): C1 will be
parent for A10, but child for D1...

4) As told before, the amounts for all the child accounts can easily be
retrieved later.

Hope it's still easy for you J

Many thanks once again for your time spent on this matter!

Regards,

Mark

> iT JUST KEEPS ON GETTING EASIER ALL THE TIME.
>
[quoted text clipped - 266 lines]
>> >> >> Regards,
>> >> >> Mark

Reply to this Message

Joel - 21 Sep 2007 18:10 GMT

this is my 1st try. there are some features that I need clairifed. try code
and make comments. macro takes data on Sheet1 and put the re-formatted data
on Sheet2

1) I didn't didn't know if you wantedcolumns past Col C included so I left
them in
2) Instead of Sum, I used a SUMIF. Made code simpler. Used Column C as
criteria for doing the sum
3) I added a function AccountValue which you can eventually use to put
amounts into the sheets. This function can be re-written to lookup amounts
in each account.

Sub test()

Sh1RowCount = 1
Sh2RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount).Value <> ""
AccountName = .Range("A" & Sh1RowCount).Value
'search if account name already exists in Col A
If Sh1RowCount = 1 Then
.Rows(Sh1RowCount).Copy Destination:= _
Sheets("Sheet2").Rows(Sh2RowCount)
Sh2RowCount = Sh2RowCount + 1
Else
With Sheets("Sheet2")
Set Sh2AccountName = .Range(.Cells(1, "A"), _
.Cells(Sh2RowCount, "A"))
Set c = Sh2AccountName.Find(what:=AccountName, _
LookIn:=xlValues)
If c Is Nothing Then

Sheets("Sheet1").Rows(Sh1RowCount).Copy _
Destination:=.Rows(Sh2RowCount)
Sh2RowCount = Sh2RowCount + 1

End If
End With
End If
If .Range("C" & Sh1RowCount).Value <> _
.Range("C" & Sh1RowCount + 1).Value Then

NewAccountName = .Range("C" & Sh1RowCount).Value
With Sheets("Sheet2")
Set Sh2AccountName = .Range(.Cells(1, "A"), _
.Cells(Sh2RowCount, "A"))
Set c = Sh2AccountName.Find(what:=NewAccountName, _
LookIn:=xlValues)
If c Is Nothing Then
Sheets("Sheet1").Rows(Sh1RowCount).Copy _
Destination:=.Rows(Sh2RowCount)
.Range("A" & (Sh2RowCount)).Value = NewAccountName
.Range("C" & (Sh2RowCount)).ClearContents
Sh2RowCount = Sh2RowCount + 1
End If
End With
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With

NewAccountName = Sheets("Sheet1"). _
Range("C" & Sh1RowCount).Value
With Sheets("Sheet2")
Set Sh2AccountName = .Range(.Cells(1, "A"), _
.Cells(Sh2RowCount, "A"))
Set c = Sh2AccountName.Find(what:=NewAccountName, _
LookIn:=xlValues)
If c Is Nothing Then
Sheets("Sheet1").Rows(Sh1RowCount).Copy _
Destination:=.Rows(Sh2RowCount)
.Range("A" & (Sh2RowCount)).Value = NewAccountName
.Range("C" & (Sh2RowCount)).ClearContents
Sh2RowCount = Sh2RowCount + 1
End If
End With

'fill in formulas
With Sheets("Sheet2")

LastRowSh2 = Sh2RowCount - 1
Set Sh2ColCRange = _
.Range(.Cells(1, "C"), .Cells(LastRowSh2, "C"))
For Each cell In Sh2ColCRange
If IsEmpty(cell) Then
cell.Offset(0, -1).Formula = _
"=Sumif(C1:C" & LastRowSh2 & "," & _
cell.Offset(0, 2) & ",B1:B" & LastRowSh2 & ")"
Else
cell.Offset(0, -1).Formula = _
"=AccountValue(" & cell.Offset(0, -2) & ")"

End If
Next cell
End With
End Sub
Function AccountValue(AccountName)
AccountValue = "TBD"
End Function

> Hi Joel,
>
[quoted text clipped - 259 lines]
> >> >> >> left
> >> >> >> etc... (until the highest level)

Reply to this Message