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:

Row01/Col01	Col02	Col03	Col04	Col05	Sheet1
Row02	Name01	110	120	130
Row03	Name02	210	220	230
Row04	Name03	310	320	330
*************	********	********	********	********

<tbody>
</tbody>

Row01/Col01	Row02	Row03	Row04	Sheet2
Col02	Name01	Name02	Name03
Col03	110	210	310
Col04	120	220	320
Col05	130	230	330
*************	********	********	********

<tbody>
</tbody>

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.