Update values from one column to another

[Pedro Costa]

Hi,
I have a table with 99 columns and I have to copy the values of the
column where the first row has the higher value. In this example I
have to copy the third the contents of the third one to a new one.

15 20 30 25 5 6
------------------------------------------------------------------------------------
10 15 17 16 18 19
20 30 34 32 36 38
40 60 68 64 72 76
80 120 136 128 144 152
160 240 272 256 288 304
320 480 544 512 576 608
640 960 1088 1024 1152 1216
1280 1920 2176 2048 2304 2432


Through the MAX formula I know which column I have to copy. But, how
can I copy the values to a new column?

Thanks in advance for your help?

Pedro Costa

 

[smartin]

Hi,
I have a table with 99 columns and I have to copy the values of the
column where the first row has the higher value. In this example I
have to copy the third the contents of the third one to a new one.

15 20 30 25 5 6
------------------------------------------------------------------------------------
10 15 17 16 18 19
20 30 34 32 36 38
40 60 68 64 72 76
80 120 136 128 144 152
160 240 272 256 288 304
320 480 544 512 576 608
640 960 1088 1024 1152 1216
1280 1920 2176 2048 2304 2432


Through the MAX formula I know which column I have to copy. But, how
can I copy the values to a new column?

Thanks in advance for your help?

Pedro Costa

Assume your data sheet in Sheet1!A1:CLx (that's 90 columns by x rows).

In Sheet2 A1:

=MATCH(MAX(Sheet1!$A$1:$CL$1),Sheet1!$A$1:$CL$1,0)
returns the column number where the max was found (not the max value itself)

In Sheet2 A3 and fill down to row x:
=INDEX(Sheet1!$A3:$CL3,1,A$1)