Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatted columns = feasible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatted c
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 "markx" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatted columns
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 "Joel" wrote in message ... 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 "markx" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatt
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. "markx" wrote: 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 "Joel" wrote in message ... 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 "markx" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatt
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 :-)? "Joel" wrote in message ... 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. "markx" wrote: 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 "Joel" wrote in message ... 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 "markx" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatt
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? "markx" wrote: 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 :-)? "Joel" wrote in message ... 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. "markx" wrote: 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 "Joel" wrote in message ... 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 "markx" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatt
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 "Joel" wrote in message ... 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? "markx" wrote: 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 :-)? "Joel" wrote in message ... 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. "markx" wrote: 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 "Joel" wrote in message ... 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 "markx" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert multicolumn/multirow table into two structured/formatt
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 "markx" wrote: 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 "Joel" wrote in message ... 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? "markx" wrote: 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 :-)? "Joel" wrote in message ... 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. "markx" wrote: 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 "Joel" wrote in message ... 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 "markx" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to convert a table of data to columns only | Excel Discussion (Misc queries) | |||
VLOOKUP with Structured Reference to Table Header | Excel Worksheet Functions | |||
How to convert formatted date into text. | Excel Worksheet Functions | |||
Convert a formatted table back to a list | Excel Discussion (Misc queries) | |||
Data in table, may need to convert to columns with OFFSET? | Excel Discussion (Misc queries) |