Please help with transpose ..

JrExcel · Apr 4th 2017

I have a spreadsheet having following details ..

[TABLE="width: 290"]

[tr]

[td]

Department

[/td]

[TD="align: right"]27-Mar-17
[/TD]
[TD="align: right"] 03-Apr-17
[/TD]
[TD="align: right"] 10-Apr-17
[/TD]

[/tr]

[tr]

[td]

1111-11-0001

[/td]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0120

[/td]

[TD="align: right"]37
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0140

[/td]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0320

[/td]

[td][/td]

[TD="align: right"]12[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0340

[/td]

[TD="align: right"]23.2
[/TD]
[TD="align: right"]48.5[/TD]
[TD="align: right"]82.8[/TD]

[/tr]

[/TABLE]

how to transpose rows & columns to get following results?

[TABLE="width: 223"]

[tr]

[td]

1111-11-0001

[/td]

[TD="align: right"] 27-Mar-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-11-0001

[/td]

[TD="align: right"]03-Apr-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-11-0001

[/td]

[TD="align: right"]10-Apr-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0120

[/td]

[TD="align: right"]27-Mar-17
[/TD]
[TD="align: right"]37[/TD]

[/tr]

[tr]

[td]

1111-15-0120

[/td]

[TD="align: right"]03-Apr-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0120

[/td]

[TD="align: right"]10-Apr-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0140

[/td]

[TD="align: right"]27-Mar-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0140

[/td]

[TD="align: right"]03-Apr-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0140

[/td]

[TD="align: right"]10-Apr-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0320

[/td]

[TD="align: right"]27-Mar-17
[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0320

[/td]

[TD="align: right"]03-Apr-17
[/TD]
[TD="align: right"]12[/TD]

[/tr]

[tr]

[td]

1111-15-0320

[/td]

[TD="align: right"]10-Apr-17[/TD]

[td][/td]

[/tr]

[tr]

[td]

1111-15-0340

[/td]

[TD="align: right"]27-Mar-17[/TD]
[TD="align: right"]23.2[/TD]

[/tr]

[tr]

[td]

1111-15-0340

[/td]

[TD="align: right"]03-Apr-17[/TD]
[TD="align: right"]48.5[/TD]

[/tr]

[tr]

[td]

1111-15-0340

[/td]

[TD="align: right"]10-Apr-17[/TD]
[TD="align: right"]82.8[/TD]

[/tr]

[/TABLE]

Thanks for your help.

Warm Regards,

**Carim** · Apr 4th 2017

Re: Please help with transpose ..

Hello,

Would you mind attaching a sample workbook ... for tests purposes ...

JrExcel · Apr 4th 2017

Re: Please help with transpose ..

Hello Carim,
Please find attached herewith..

Thanks & Regards,

**Carim** · Apr 4th 2017

Re: Please help with transpose ..

Hello,

Attached is your workbook ... with a proposal .. to go from Input ... to the Output ...

HTH

JrExcel · Apr 5th 2017

Re: Please help with transpose ..

Hi Carim,
That is what I was looking for but .. I couldn't find vba code or steps to achieve the same? would you please give me the details?

Thanks
Jr.

**Carim** · Apr 5th 2017

Re: Please help with transpose ..

Hello,

There is no VBA code ...

To go from Input to Output ... the second row in Output sheet uses the Offset() function which, once copied down ... produces your result ...

Hope this clarifies

**alansidman** · Apr 5th 2017

Re: Please help with transpose ..

Look at this thread for a VBA solution

http://www.ozgrid.com/forum/showthread.php?t=203573

JrExcel · Apr 5th 2017

Re: Please help with transpose ..

Hello,
My sheet has approx. 988 rows and 200 columns , how to copy this formula ? please help.
Thanks.

JrExcel · Apr 5th 2017

Re: Please help with transpose ..

Hello Alan,

I will have a look at it. and let you know.
Thanks & Regards,
Jr.

**Carim** · Apr 5th 2017

Re: Please help with transpose ..

Hello,

Your sample input had 3 Columns ... which led the Offset() function to use 3 as argument ...

Whatever your actual number of Columns, just replace 3 by your actual number in all three formulas ... and copy down ...

Hope this will help

JrExcel · Apr 5th 2017

Re: Please help with transpose ..

Quote from Carim;789847

Hello,

Your sample input had 3 Columns ... which led the Offset() function to use 3 as argument ...

Whatever your actual number of Columns, just replace 3 by your actual number in all three formulas ... and copy down ...

Hope this will help

Display More

Ok thanks for your help ... I will try it out.

JrExcel · Apr 5th 2017

Re: Please help with transpose ..

Hello Alan,

I am afraid it don't work .... many columns having no value just didn't get transposed but some columns got copied but 2nd column but not the 1st one?

Thanks & Regards,
Jr.

**Carim** · Apr 5th 2017

Re: Please help with transpose ..

You are welcome ...

JrExcel · Apr 10th 2017

Re: Please help with transpose ..

Hello Carim,
tried offset formula but couldn't understand how it works. I would appreciate it if you could give me description of each three commands?
My worksheet has 800 rows ( column A) and approx. 200 columns. How can I copy formula ( may be using micro or vba code?)

your original code : =

=OFFSET(Input!$A$2,INT((ROW()-2)/3),0)
=OFFSET(Input!$B$1,0,MOD(ROW()-2,3))
=OFFSET(Input!$B$2,INT((ROW()-2)/3),MOD(ROW()-2,3))
Changed code

=OFFSET(Input!$A$2,INT((ROW()-2)/200),0)
=OFFSET(Input!$B$1,0,MOD(ROW()-2,200))
=OFFSET(Input!$B$2,INT((ROW()-2)/200),MOD(ROW()-2,200))

Please help.
Thanks & Regards,
Jr.

**Carim** · Apr 11th 2017

Re: Please help with transpose ..

Quote

My worksheet has 800 rows ( column A) and approx. 200 columns ...

Hello,

When using the Offset() function ... there is no room for an approximate number of columns ...

To be on the safe side, use the Total Number of Columns - 1 ...

HTH

JrExcel · Apr 11th 2017

Re: Please help with transpose ..

Hello Carim,

Thanks for your help ... will try it out.

Warm Regards,
Jr.

**Carim** · Apr 11th 2017

Re: Please help with transpose ..

Hello,

Ideally, using the Offset() function is fine for small spreadsheets ...and for a very large spreadsheet, a macro would be more logical ...to avoid the re-calculation burden ...

Please help with transpose ..

Participate now!

Share

Users Viewing This Thread