Hope I can explain this

pontiff · Aug 5, 2011

200 rows, first column contains a unique text name eg "Smith"
In the "Smith" row for example, the other columns are filled with 3 serial numbers eg 56,58,72

But in the "Jones" row, there are the same serial numbers but in a different order, 72, 58,56 plus 2 other numbers, 88 and 99

This continues down the rows...
My question is, can I sort each row so that one serial number eg. 56 appears in the first column , 58 in the next, etc etc.

Apologies if this is a bit confused....

tweedle · Aug 5, 2011

With a formula; Set H2 to the formula, Copy across to Col L and down.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>smith</TD><TD style="TEXT-ALIGN: right">56</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">72</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">56</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">72</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>jones </TD><TD style="TEXT-ALIGN: right">72</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">56</TD><TD style="TEXT-ALIGN: right">88</TD><TD style="TEXT-ALIGN: right">99</TD><TD> </TD><TD style="TEXT-ALIGN: right">56</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">72</TD><TD style="TEXT-ALIGN: right">88</TD><TD style="TEXT-ALIGN: right">99</TD></TR></TBODY></TABLE>

<TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=IF(ISNA(CHOOSE(RANK(B2,$B2:$F2, 1),$B2,$C2,$D2,$E2,$F2)),"",CHOOSE(RANK(B2,$B2:$F2, 1),$B2,$C2,$D2,$E2,$F2))</TD></TR></TBODY></TABLE>

To do the sorting in place would require vba.

pontiff · Aug 5, 2011

Hi tweedle, thanks for the reply.
It worked until I added another row with the numbers in the order 88,89,58,56,72 which returned 56,72,89,88,58.

markmzz · Aug 5, 2011

Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">smith</td><td style="text-align: right;;">56</td><td style="text-align: right;;">58</td><td style="text-align: right;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">56</td><td style="text-align: right;;">58</td><td style="text-align: right;;">72</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">jones</td><td style="text-align: right;;">72</td><td style="text-align: right;;">58</td><td style="text-align: right;;">56</td><td style="text-align: right;;">88</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;">56</td><td style="text-align: right;;">58</td><td style="text-align: right;;">72</td><td style="text-align: right;;">88</td><td style="text-align: right;;">99</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">gold</td><td style="text-align: right;;">88</td><td style="text-align: right;;">89</td><td style="text-align: right;;">58</td><td style="text-align: right;;">56</td><td style="text-align: right;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;">56</td><td style="text-align: right;;">58</td><td style="text-align: right;;">72</td><td style="text-align: right;;">88</td><td style="text-align: right;;">89</td></tr><tr ><td style="color: #161120;text-align: center;">4</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;">5</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">Sheet3</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>Array 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">H1</th><td style="text-align:left">{=IF(<font color="Blue">COLUMNS(<font color="Red">$B:B</font>)>COUNTIF(<font color="Red">(<font color="Green">$B1:$F1</font>),">0"</font>),"",SMALL(<font color="Red">(<font color="Green">$B1:$F1</font>)*1,COLUMNS(<font color="Green">$B:$F</font>)-COUNTIF(<font color="Green">(<font color="Purple">$B1:$F1</font>),">0"</font>)+COLUMNS(<font color="Green">$B:B</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

pontiff · Aug 7, 2011

Hi Markmzz, that seems to work fine with the numbers used, thanks!!

However..... to throw another spanner in the works, if the 56 in Gold's line is replaced with, say, 21, it stops working. Is it possible for a new column to be created for the new number, so they continue to stay in line?

markmzz · Aug 7, 2011

pontiff said:
Hi Markmzz, that seems to work fine with the numbers used, thanks!!

However..... to throw another spanner in the works, if the 56 in Gold's line is replaced with, say, 21, it stops working. Is it possible for a new column to be created for the new number, so they continue to stay in line?

Pontiff,

Sorry, but I didn't understand what you want.

Could you post two ranges (have and want) with more data?

Markmzz

pontiff · Aug 7, 2011

Sorry I'm not explaining this very well. In the example below using your formula you get the results shown. The Smith row contains all the possible values. The Jones row contains some of those values but not all of them, and what I need is for the Jones 5 to end up in the same column as the Smith 5.
The silver row has the same issue, the 4 and 5 need to line up with the Smith 4 and 5?
Clear as mud? Thanks for all your help so far.

<table width="768" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="12" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">Smith</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">1</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">2</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">3</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">4</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">5</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">1</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">2</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">3</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">4</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Jones</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Gold</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Silver
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>
and I need it to be.....

<table width="768" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="12" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">Smith</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">1</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">2</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">3</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">4</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">5</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">1</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">2</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">3</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">4</td> <td class="xl65" style="border-left:none;width:48pt" width="64" align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Jones</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Gold</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Silver</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">1</td> <td class="xl65" style="border-top:none;border-left:none" align="right">2</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> </tr> </tbody></table>

-- removed inline image ---

shg · Aug 7, 2011

Maybe ...

Code:

      -A-- B C D E- F G- H I J K L M N O P Q R-
  1                        1 2 3 4 5 6 7 8 9 10
  2   Alan 4 2 1 10 7  3   1 2 3 4     7     10
  3   Barb 3 6 5  2 9  4     2 3 4 5 6     9   
  4   Cain 6 8 1  2 9 10   1 2       6   8 9 10
  5   Dana 6 4 1 10 3  9   1   3 4   6     9 10

In I1 and copied to right,

=IFERROR(SMALL($B$2:$G$5,COUNTIF($B$2:$G$5,"<=" & H1) + 1), "")

In I2 and copied acropss and down,

=IF(COUNTIF($B2:$G2, I$1), I$1, "")

markmzz · Aug 7, 2011

Hi Pontiff,

If I understand correctly what you want, then this can help you:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">smith</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">jones</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";"></td><td style="text-align: right;;">3</td><td style=";"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">gold</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">silver</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">5</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;">6</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">Sheet3</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">H1</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B$1,$B1:$F1,0</font>)</font>),"",B$1</font>)</td></tr></tbody></table></td></tr></table><br />
Do some tests and tell us if it work.

Markmzz

pontiff · Aug 8, 2011

Both seem to work great!!

Thanks so much, this will save hours of work!!

Hope I can explain this

pontiff

Board Regular

Excel Facts

tweedle

Well-known Member

pontiff

Board Regular

markmzz

MrExcel MVP

pontiff

Board Regular

markmzz

MrExcel MVP

pontiff

Board Regular

shg

MrExcel MVP

markmzz

MrExcel MVP

pontiff

Board Regular

Similar threads

Forum statistics

Share this page

Hope I can explain this

Board Regular

Excel Facts

Well-known Member

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock