Ascending order formula.

[serge]

In row A1:J1 I have the numbers from 0 to 9 . Then below, in row 3 as example I have 7-6-2-1-18-13-3-5-55-271 which are reference to row 1.Now what I need if possible is to rearrange row 1 in an ascending order according of row 3.See the way it should be in L3:U3.

And row 4 as 2nd example.Thank you in advance for any help.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	0	1	2	3	4	5	6	7	8	9
2
3	7	6	2	1	18	13	3	5	55	271		3	2	6	7	1	0	5	4	8	9
4	33	12	1	4	11	10	3	2	9	13		2	7	6	3	8	5	4	1	9	0
5
Sheet2

 

[markmzz]

Hi Serge,

This is another formula (with a small modification of Njimack's formula):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>FV</th><th>FW</th><th>FX</th><th>FY</th><th>FZ</th><th>GA</th><th>GB</th><th>GC</th><th>GD</th><th>GE</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6844</td><td style="text-align: center;;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">18</td><td style="text-align: center;;">13</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">55</td><td style="text-align: center;;">271</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">6845</td><td style="text-align: center;;">33</td><td style="text-align: center;;">12</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">11</td><td style="text-align: center;;">10</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;">13</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">9</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6846</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6847</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">FV6844</th><td style="text-align:left">=INDEX(<font color="Blue">$Y$1:$AH$1,MATCH(<font color="Red">SMALL(<font color="Green">$Y6844:$AH6844,COLUMNS(<font color="Purple">$A:A</font>)</font>),$Y6844:$AH6844,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[serge]

Hi Peter,

Thank you for answering, and the explanation, you solve my problem again, you've been helping me in the past and again tonight.

I really do appreciate it.

Best Regards...

 

[serge]

Hi Markmzz,

Thanks for your help also, I just tried your formula and it works perfectly fine for my data.

I'm just wondering how many different formulas can be made to get the same result ?

You guys are amazing and a big help for me.

Serge.

 

[Peter_SSs]

Hi Markmzz,

Thanks for your help also, I just tried your formula and it works perfectly fine for my data.

I'm just wondering how many different formulas can be made to get the same result ?

Well, all three formulas are very much the same really, especially the last two. However, I'll contend that the markmzz formula again is less robust. After that formula is populated in columns FV:GE, if any of the columns A through J get deleted, the results of some at least of these formulas will go astray. (Try deleting, say, column B and observe the results).

The advantage of using the cells these formulas are actually in to count the columns is that you don't get that problem unless the column(s) the formulas actually occupy are deleted. I contend that is much less likely and if it does happen, you probably have done it deliberately and know you are wrecking these particular formulas.

 

[markmzz]

Well, all three formulas are very much the same really, especially the last two. However, I'll contend that the markmzz formula again is less robust. After that formula is populated in columns FV:GE, if any of the columns A through J get deleted, the results of some at least of these formulas will go astray. (Try deleting, say, column B and observe the results).

The advantage of using the cells these formulas are actually in to count the columns is that you don't get that problem unless the column(s) the formulas actually occupy are deleted. I contend that is much less likely and if it does happen, you probably have done it deliberately and know you are wrecking these particular formulas.

Peter_SSs,

Maybe this?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>FU</th><th>FV</th><th>FW</th><th>FX</th><th>FY</th><th>FZ</th><th>GA</th><th>GB</th><th>GC</th><th>GD</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6844</td><td style="text-align: center;;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">18</td><td style="text-align: center;;">13</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">55</td><td style="text-align: center;;">271</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">6845</td><td style="text-align: center;;">33</td><td style="text-align: center;;">12</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">11</td><td style="text-align: center;;">10</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;">13</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">9</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6846</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6847</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td><td style=";">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">FU6844</th><td style="text-align:left">=INDEX(<font color="Blue">$X$1:$AG$1,MATCH(<font color="Red">SMALL(<font color="Green">$X6844:$AG6844,COLUMNS(<font color="Purple">$X:X</font>)</font>),$X6844:$AG6844,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Peter_SSs]

Peter_SSs,

Maybe this?

I'd be silly to disagree, practically it's the same as what I've posted in posts #9 & #10

 

[markmzz]

I'd be silly to disagree, practically it's the same as what I've posted in posts #9 & #10

post #9
=INDEX($A$1:$J$1,MATCH(SMALL($A3:$J3,COLUMNS($L3:L3)),$A3:$J3,0))

post #10

=INDEX($Y$1:$AH$1,MATCH(SMALL($Y6844:$AH6844,COLUMNS($FV6844:FV6844)),$Y6844:$AH6844,0))

my way:
=INDEX($X$1:$AG$1,MATCH(SMALL($X6844:$AG6844,COLUMNS($X:X)),$X6844:$AG6844,0))

Sorry, but not the same. No need the number of the row.

Markmzz

 

[Peter_SSs]

I'd be silly to disagree, practically it's the same as what I've posted in posts #9 & #10

By practically, I meant in practice or in use. That is, they all exhibit the same robustness to insertion or deletion of columns that I had been referring to and all operate in the same manner.

You are right that yours doesn't contain any row reference and that makes it marginally shorter to type. Considering it only had to be typed once (or could have been Copy/Pasted) I don't see that as a big issue, certainly in comparing the 'sameness' of the structure and operation of the formulas. And I ceratinly did not claim mine to be better than yours.

 

[markmzz]

By practically, I meant in practice or in use. That is, they all exhibit the same robustness to insertion or deletion of columns that I had been referring to and all operate in the same manner.

You are right that yours doesn't contain any row reference and that makes it marginally shorter to type. Considering it only had to be typed once (or could have been Copy/Pasted) I don't see that as a big issue, certainly in comparing the 'sameness' of the structure and operation of the formulas. And I ceratinly did not claim mine to be better than yours.

Anyway, the most important thing is that Serge had the problem solved.

Note: Not mine formula, but yes the Njimack's formula with a small modification mine (as I had previously posted).

Markmzz

 

[Peter_SSs]

Anyway, the most important thing is that Serge had the problem solved.

I agree, no point in continuing to 'split hairs'.