Transposing From Horizontal to Vertical with Intervals

[zinkloride]

I've been working on this for days, i still got no solution on what formulas I will put on cells in "To" tab. The excel file is composed of two tabs "To" and "From". We will convert the "From Tab" to "To" format. Hope someone can help on what formula will I put in D3 to V3 and drag down in "To" tab to convert datas from "From" tab. Thanks in advance.

Zink

[hemesh]

hello and welcome to the forum!

do you always have 4 entries of date in trading date column and is it consitent i.e. like 26 , 27, 28, 29 and each one is 4 times

[zinkloride]

sometimes only 2 or 3 as seller A might not be doing business straight one month. but most of the times its 4. thanks for your reply.

zink

[hemesh]

Hello Find the attached !

[hemesh]

find the attached !

In New sheet below enter date you can enter the desired date and data will be sorted accordingly

When heading changes(i.e. after black columum) you need to go to d3 press F2 copy the entire formula press esc key and paste the same to i3
and change
=IFERROR(INDEX(From!$E$5:$CV$122,MATCH($A3&D$1,DATEVALUE(RIGHT(From!$A$5:$A$122,2)&"-"&MID(From!$A$5:$A$122,5,2)&"-"&LEFT(From!$A$5:$A$122,4))&From!$D$5:$D$122&"-"&From!$C$5:$C$122,0),(($B3-1)*COLUMN($D$1))+COLUMN(A$1)),"") red part to I and drag down and to right till black column.

Means every time for new company and transporter you need to change red part to particular column i.e. for first will be A, for second will be I, for third will be N for fourth will be S.

After change hold control and shift together and then press enter key and release all three keys to make it array formula. Then drag down and across till you reach black column




Dec 2013 Sales.xls

[zinkloride]

I don't know why the cell will be left blank after i press control shift enter in entering the array formula. The formula is still there but after i press control shift enter its blank, even in the cells you already have inputted the formulas the cell will be left blank after I do so.

zink

[hemesh]

DID you managed the sheet in the same way I have sent you! There is a formula in column A3 till end see that then all heading from c1 till end check the same. If still problem persist PM me.

[kvsrinivasamurthy]

Pl see the attached file with formula.
In the first row of the "To" sheet a slight change is done to simplify formula.
Formula is dragged from D3 cell across and vertical.

[zinkloride]

Thanks for the replies, now my sheet is working, hehehe. Again thanks for the help.

zink

[kvsrinivasamurthy]

Thanks for the feedback.Pl mark the thread solved.