simple question

[alhurayas]

hi all I have simple question

in sheet1 I have all my data
and sheet 2 to have the same date but the ROWS should be converted to COLUMNS

thanks you very much for any the help

 

[alhurayas]

Mr mrkmzz thank you
I tried it but did not work and I used Ctrl+Shift+Ente

 

[markmzz]

Mr mrkmzz thank you
I tried it but did not work and I used Ctrl+Shift+Ente

Hi Alhurayas,

Here it works. Look at this:

Layout:

[TABLE="width: 223"]
<tbody>[TR]
[TD="class: xl66, width: 70, bgcolor: #D9D9D9"]Row01/Col01
[/TD]
[TD="class: xl66, width: 47, bgcolor: #D9D9D9"]Col02
[/TD]
[TD="class: xl66, width: 47, bgcolor: #D9D9D9"]Col03
[/TD]
[TD="class: xl66, width: 47, bgcolor: #D9D9D9"]Col04
[/TD]
[TD="class: xl66, width: 47, bgcolor: #D9D9D9"]Col05
[/TD]
[TD="class: xl67, width: 40, bgcolor: transparent"]Sheet1
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]Row02
[/TD]
[TD="class: xl65, bgcolor: transparent"]Name01
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]130
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]Row03
[/TD]
[TD="class: xl65, bgcolor: transparent"]Name02
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]210
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]220
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]230
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]Row04
[/TD]
[TD="class: xl65, bgcolor: transparent"]Name03
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]310
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]320
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]330
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*************
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 70, bgcolor: transparent"]Row01/Col01
[/TD]
[TD="class: xl65, width: 47, bgcolor: transparent"]Row02
[/TD]
[TD="class: xl65, width: 47, bgcolor: transparent"]Row03
[/TD]
[TD="class: xl65, width: 47, bgcolor: transparent"]Row04
[/TD]
[TD="class: xl66, width: 40, bgcolor: transparent"]Sheet2
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Col02
[/TD]
[TD="class: xl65, bgcolor: transparent"]Name01
[/TD]
[TD="class: xl65, bgcolor: transparent"]Name02
[/TD]
[TD="class: xl65, bgcolor: transparent"]Name03
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Col03
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]210
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]310
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Col04
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]220
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]320
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Col05
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]130
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]230
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]330
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*************
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Array formula (use Ctrl+Shift+Enter and not only Enter):

In Sheet2 and cell A1

A1-> =INDEX(IF(Sheet1!$A$1:$E$4="","",Sheet1!$A$1:$E$4),COLUMNS($A1:A1),ROWS(A$1:A1))

Do some tests with my layout and tell me if the array formula work.


Markmzz

 

[alhurayas]

mr markzz thank you very very much it worked , but how to change it to be 100 ROWS and 100 COLUMNS


thank you

 

[markmzz]

mr markzz thank you very very much it worked , but how to change it to be 100 ROWS and 100 COLUMNS


thank you

Try this:

Array formula (use Ctrl+Shift+Enter and not only Enter):

In Sheet2 and cell A1 

A1-> =INDEX(IF(Sheet1!$A$1:$CV$100="","",Sheet1!$A$1:$CV$100),COLUMNS($A1:A1),ROWS(A$1:A1))

Do some tests.

Markmzz

 

[alhurayas]

thank you mr Markmzz
it worked but it needed a lot of time to calculate because I have more than 500 rows and column and I set it to 1000 for future use is an easy way

 

[alhurayas]

my system hang up

 

[markmzz]

thank you mr Markmzz
it worked but it needed a lot of time to calculate because I have more than 500 rows and column and I set it to 1000 for future use is an easy way

my system hang up

Hi Alhurayas,

Sorry about that.

For that amount of rows and columns (500 - 1,000) my formula isn't suitable.

I don't know another way to speed up the processing of your data.

Lets wait for another user.

Markmzz

 

[alhurayas]

thank you very much Mr Markmzz

 

[Person93]

=INDEX((Sheet1!$A$1:$C$3),COLUMN(),ROW())

Repalce $A$1:$C$3 with the section of Sheet 1 that you need to transpose.