Rearrange the order of columns of a block by the value of a certain cell

[waterstride]

Good afternoon Experts!
As in the the above title can find, my question is the next.
Given a block, e.g. 100 column, contains 20 row each (A1:CV20). In the end of a calculation the last cell (in row 20th) get a value.
My "desire" is to get a new block in the end of the calculations where all 100 column will rearranged by the value of the cell in row 20. E.g. at A21:CV40
Now I have Excel 2019 on my PC.
Am I clear enough?
Thnx. in advance!

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[waterstride]

Dear Ali!
Please find attached my sample file, with (probably sufficiant) expenation.

[windknife]

Try this in B11.

=INDEX($B$1:$H$9,ROWS($B$11:B11),MATCH(SMALL($B$9:$H$9,COLUMNS($B$9:B$9)),$B$9:$H$9,0))

copied down and across.

[waterstride]

Thank you so much, this is exactly what I need! ThankYoukicsi.gif

[windknife]

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

[waterstride]

Dear windknife!
Unfortunatly I couldn't addopt properly the formula to my actual worksheet, see attached.
I'm realy sorry to bozher you again.
From the block C1:DZ16 , according the row C16:DZ16 should be assort and copy the content to the yellow field.

[windknife]

Try this in C41.

=IFERROR(INDEX($C$1:$DZ$16,ROWS($C$41:C41),MOD(AGGREGATE(15,6,($C$16:$DZ$16*10^6+COLUMN($C$16:$DZ$16))/($C$16:$DZ$16>0),COLUMNS($C$41:C$41)),10^6)-2),"")

copied down and across.

[waterstride]

Dear windknife!
Regret to say, this solution is only 80%
As you can see, this table contains, a toto friend community tipps and results.
In the block C2:DZ15 there are the bet of the members and in column EG2:EG15 are the results of the bet.
In the TOTO the top score in our country the 13+1
You can see that the basic score is counted at the row 17 and the top 13+1 score calculated and indicated in row 16.
Now my problem is, thet if anybody hit the top 13+1 score, that is not shown in the new, rearranged block.
I tried to follow your function, but my knowledge is not enough for that.
My I get a litle more support from you? Would be highly apprecciated.
Sincerely your. Iván

[windknife]

How about this in C41?

=IFERROR(INDEX($C$1:$DZ$16,ROWS($C$41:C41),MOD(AGGREGATE(15,6,(SUBSTITUTE($C$16:$DZ$16,"13+1",14)*10^6+COLUMN($C$16:$DZ$16))/($C$16:$DZ$16>0),COLUMNS($C$41:C$41)),10^6)-2),"")

copied down and across.

[waterstride]

Dear windknife!
Now it's looks like I was thinking about!
I' will check arround and and marking as closed!
P.s.: would you tell me please what is it stands for in te function, I mean this part: ....10^6.....?

[windknife]

10^6 denotes 10 exponent 6 (1000000)

[waterstride]

Oooooh,ahh!
I'm not brave enough to ask for, that why?!
Now I'm going to copy this new function to a text editor and I will compare to the previous one, to learn a litle bit.

[windknife]

The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.

refer to:

https://support.microsoft.com/en-us/...a-07fb519c362a

[waterstride]

First of all, thank you very much to solve my problem, I houldn't be able to fix it alone.
At second, I have to confess, that I'm not able to follow the logic of your function.
A question to the end. Is it a hard task to make this rearrangement in reverse order? I mean in decreasing order, instead of rising?

[windknife]

change AGGREGATE(15,6... to AGGREGATE(14,6 ...