• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting data from horizontal to vertical

D

dvsdasari

New Member
Hi,

I'm trying solve this problem since five days but i didn't get correct solutions for this.

Here we have two sheets, one is the data which i downloaded from software.

Second one is format which we need to convert the data in the first sheet.


I need to convert the data in the first sheet to data showing in the second sheet.


Please help me if some body have good solution for this and it'll save lot of time to me.


Following is the example file link:

https://www.dropbox.com/s/d1pirpmcn8nq2rd/PL%20042013.xlsx


Thanks-Sekhar
 
Insert a new Column to left of Consolidated Column A

In the new Column A

A5: =IF(LEFT(C5,12)="Cost centre:",MID(C5,14,3),A4)

Copy down to buttom of the data

Data E12: =SUMPRODUCT((Consolidated!$A$5:$A$1152=DATA!E$4)*(Consolidated!$B$5:$B$1152=DATA!$A12),(Consolidated!$C$5:$C$1152))

Data F12: =SUMPRODUCT((Consolidated!$A$5:$A$1152=DATA!E$4)*(Consolidated!$B$5:$B$1152=DATA!$A12),(Consolidated!$D$5:$D$1152))

Copy down

Then copy across


Data G12: =E12-F12

Custom Format G12 as #,##0 "Dr";#,##0 "Cr"


Refer: https://www.dropbox.com/s/fmwagpbo399e19u/PL%20042013-1_Hui.xlsx
 
Hi Hui,


Thanks for your fast reply.


We cann't change any data in Data sheet of the attached file. We need to convert data like format showing in the consolidated sheet (i.e., Particulars+410 cost centre including debit,credit and Closing balance. Below 410 cost centre - Particulars+513 cost centre including debit,credit and Closing balance.... up to 914 cost centre).


Finally the format sholud be like in the consolidated sheet which i've prepared mannually.


We need to add cost centre wise expenses (debti,credit and closing balance) to particulars in the same columns.


Thanks-Sekhar
 
Sekhar


The consolidated sheet I prepared was exactly the same as what you gave me?


Can the extra column A be placed over on the right hand side out of sight at say Column AA?
 
Hi, dvsdasari!


For another approach using INDEX & INDIRECT functions give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Converting%20data%20from%20horizontal%20to%20vertical%20-%20PL%20042013%20%28for%20dvsdasari%20at%20chandoo.org%29.xlsx


Formulas:


B5, B53, B101,...

=INDICE(DATA!$5:$5;ENTERO((FILA()+47)/48)*3-1) -----> in english: =INDEX(DATA!$5:$5,INT((ROW()+47)/48)*3-1)


B6, B54, B102,..

=INDICE(DATA!$6:$6;ENTERO((FILA()+47)/48)*3-1) -----> in english: =INDEX(DATA!$6:$6,INT((ROW()+47)/48)*3-1)


A8, A56, A104,...

=DATA!$A$8


B8, B56, B104,...

=INDICE(DATA!$8:$8;ENTERO((FILA()+47)/48)*3-1) -----> in english: =INDEX(DATA!$8:$8,INT((ROW()+47)/48)*3-1)


B9, B57, B105,...

=INDICE(DATA!$9:$9;ENTERO((FILA()+47)/48)*3-1) -----> in english: =INDEX(DATA!$9:$9,INT((ROW()+47)/48)*3-1)


D9, D57, D105,...

=INDICE(DATA!$9:$9;ENTERO((FILA()+47)/48)*3+1) -----> in english: =INDEX(DATA!$9:$9,INT((ROW()+47)/48)*3+1)


B10, B58, B106,...

=INDICE(DATA!$10:$10;ENTERO((FILA()+47)/48)*3-1) -----> in english: =INDEX(DATA!$10:$10,INT((ROW()+47)/48)*3-1)


C10, C58, C106,...

=INDICE(DATA!$10:$10;ENTERO((FILA()+47)/48)*3) -----> in english: =INDEX(DATA!$10:$10,INT((ROW()+47)/48)*3)


D10, D58, D106,...

=INDICE(DATA!$10:$10;ENTERO((FILA()+47)/48)*3+1) -----> in english: =INDEX(DATA!$10:$10,INT((ROW()+47)/48)*3+1)


A11:A48, A59:A96, A107:A132,...

=INDICE(DATA!A$11:A$48;RESIDUO(FILA()+47;48)+1+(1-SIGNO(RESIDUO(FILA()+47;48)+1))*48-10) -----> in english: =INDEX(DATA!A$11:A$48,MOD(ROW()+47,48)+1+(1-SIGN(MOD(ROW()+47,48)+1))*48-10)


B11:D48, B59:D96, B107:D132,...

=INDICE(INDIRECTO("DATA!"&RESIDUO(FILA()+47;48)+1+(1-SIGNO(RESIDUO(FILA()+47;48)+1))*48&":"&RESIDUO(FILA()+47;48)+1+(1-SIGNO(RESIDUO(FILA()+47;48)+1))*48);ENTERO((FILA()+47)/48)*3+COLUMNA()-3) -----> in english: =INDEX(INDIRECT("DATA!"&MOD(ROW()+47,48)+1+(1-SIGN(MOD(ROW()+47,48)+1))*48&":"&MOD(ROW()+47,48)+1+(1-SIGN(MOD(ROW()+47,48)+1))*48),INT((ROW()+47)/48)*3+COLUMN()-3)


Procedure:

a) Place formulas in B5,B6,A8,B8,B9,D9,B10,C10,D10,A11,B11,C11,D11

b) Format properly

c) Copy A11:D11 down thru row 48

d) Copy A1:D48 down thru row 1152


Just advise if any issue.


Regards!
 
Hi SirJB7,


I'm very thankful for your effort and time.

It is perfectly fine which you prepare with help of formulas.

Please check that there is any other way to do it with easy way like macros or any other procedures.


I've one macro which i'm using (got it from chandoo.org), please look at it and loop this one up to last cost center in data sheet.

=======================

Sub TransferData()

Dim LastRow As Integer


'Where is the last cell with data?

LastRow = Worksheets("410").Range("A65536").End(xlUp).Row

LastRow = Worksheets("513").Range("A65536").End(xlUp).Row

LastRow = Worksheets("514").Range("A65536").End(xlUp).Row

LastRow = Worksheets("515").Range("A65536").End(xlUp).Row

LastRow = Worksheets("518").Range("A65536").End(xlUp).Row

LastRow = Worksheets("519").Range("A65536").End(xlUp).Row

LastRow = Worksheets("537").Range("A65536").End(xlUp).Row

LastRow = Worksheets("538").Range("A65536").End(xlUp).Row

LastRow = Worksheets("541").Range("A65536").End(xlUp).Row

LastRow = Worksheets("542").Range("A65536").End(xlUp).Row

LastRow = Worksheets("543").Range("A65536").End(xlUp).Row

LastRow = Worksheets("544").Range("A65536").End(xlUp).Row

LastRow = Worksheets("545").Range("A65536").End(xlUp).Row

LastRow = Worksheets("547").Range("A65536").End(xlUp).Row

LastRow = Worksheets("554").Range("A65536").End(xlUp).Row

LastRow = Worksheets("613").Range("A65536").End(xlUp).Row

LastRow = Worksheets("616").Range("A65536").End(xlUp).Row

LastRow = Worksheets("617").Range("A65536").End(xlUp).Row

LastRow = Worksheets("627").Range("A65536").End(xlUp).Row

LastRow = Worksheets("646").Range("A65536").End(xlUp).Row

LastRow = Worksheets("665").Range("A65536").End(xlUp).Row

LastRow = Worksheets("711").Range("A65536").End(xlUp).Row

LastRow = Worksheets("715").Range("A65536").End(xlUp).Row

LastRow = Worksheets("904").Range("A65536").End(xlUp).Row

'Transfer data

Sheets("data").Range("a1:d100").Copy Worksheets("410").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,e1:g100").Copy Worksheets("513").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,h1:j100").Copy Worksheets("514").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,k1:m100").Copy Worksheets("515").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,n1:p100").Copy Worksheets("518").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,q1:s100").Copy Worksheets("519").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,t1:v100").Copy Worksheets("537").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,w1:y100").Copy Worksheets("538").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,z1:ab100").Copy Worksheets("541").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,ac1:ae100").Copy Worksheets("542").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,af1:ah100").Copy Worksheets("543").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,ai1:ak100").Copy Worksheets("544").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,aL1:aN100").Copy Worksheets("545").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,aO1:aQ100").Copy Worksheets("547").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,aR1:aT100").Copy Worksheets("554").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,aU1:aW100").Copy Worksheets("613").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,aX1:aZ100").Copy Worksheets("616").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,BA1:BC100").Copy Worksheets("617").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,BD1:BF100").Copy Worksheets("627").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,bG1:bI100").Copy Worksheets("646").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,BJ1:BL100").Copy Worksheets("665").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,BM1:BO100").Copy Worksheets("711").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,BP1:BR100").Copy Worksheets("715").Cells(LastRow + 1, "A")

Sheets("data").Range("a1:a100,BS1:BU100").Copy Worksheets("904").Cells(LastRow + 1, "A")

End Sub

===========================

Thanks-Sekhar
 
Hi, dvsdasari!


Surely it can be done with macros but I chose the alternative of formuls.

Just looking quickly at your posted macro (which I didn't test nor I'd test) from the bunch of "LastRow = Worksheets(..." sentences the only valid is the last one as each one overwrites the previous assigned value. Same consideration for the other group, it'd work if LastRow is equal for all worksheets. Otherwise you should rearrange them in pairs inserting one of each group for each pair.


Regards!
 
Hi SirJB7,


I have just provided the macro which i'm using right now to finish my work, but it is not the benchmark.


You can do what ever the easy way you feel to get the output as you shown in consolidationNEW sheet.


Thanks-Sekhar
 
Hi, dvsdasari!


I think I haven't expressed myself clearly. Let me try in other words.

Hui came up with a formula solution based on SUMPRODUCT function, then when I read your requirement the first thing that crossed my mind was to use INDEX, OFFSET & INDIRECT functions, and that's why I posted an alternative solution so as you bring you more choices.


I would have done it with a macro but in this case I didn't feel oriented towards that direction, it's just that. Then when I saw your posted code, what I first noticed was that there were a lot of LastRow variable assignments that were overriden by the next sentences. I neither test your macro not I would do since I now don't have time to do it and you've got two other solutions available. My comments in the previous post were just intended to offer you general guidelines if it were the case that you had different rows used on each worksheets and you were experiencing loss of data in the consolidation process.


Regards!
 
Hi SirJB7,


Please download the file in the following link and run the two macros, so that you can easily understand what he actual requirement is.


Please run the macro as per below sr.no:


1. Transfer data

2. Copydatawithout headers


https://www.dropbox.com/s/mxijx7ug4f7yc4z/PL%20042013.xlsb


Thanks-Sekhar
 
You must log in or register to reply here.
Back
Top