How to arrange this ?

[john65442]

Hi :-

i have this excel file which contain's two Columns A & B

they are like this :-

Column A +++ Column B

eze e +++ Not Enough Data
fred +++ 1,998,600
e revo +++ 10,700
ivete sangalo +++ 13,300
g e patterson +++ 4,800
e tuning +++ 14,000
jonas brothers +++ 487,700


image :-



Uploaded with ImageShack.us


and i want them to become arranged similar to this way :-



Column A +++ Column B


fred +++ 1,998,600
jonas brothers +++ 487,700
e tuning +++ 14,000
ivete sangalo +++ 13,300
e revo +++ 10,700
g e patterson +++ 4,800
eze e +++ Not Enough Data

is there a formula to do this ?


Thank you .

 

[Just Jan]

C1 =IF(ISNUMBER(B1),1,0) copy down column
Sort all 3 columns using col B then C both Descending

 

[john65442]

i did not understand how to do it .

could you please give me precise explanation

 

[Taul]

When you entered the numbers did you manually type the comma (1,998,600) or did you just enter the number (1998600) and the format the cells to show commas?, I will assume the latter.
The idea of entering a formula in column ”C” is to determine if the contents of “B” is actually a number or Text.
The result of the formula should be “0” for Text and “1” for the numbers. If your formula result in “C” is different, check you actually entered values.

The next stage is to select the whole range A1:C7 and use the ribbon (or menu at the top) go to Data – Sort and in the first range choose sort by “Column B – Values – Largest to smallest”
Add a level and choose sort by “Column C” – Values – Smallest to Largest”

Back-up plan, take a look here
http://www.homeandlearn.co.uk/excel2007/excel2007s3p1.html

 

[Matty]

Hi,

The simplest way to do this would be via Sort menu. Note, however, that Excel always considers text higher than any numerical value, so "eze e" would be always at the top.

If you're after a formula way, try this...

Excel 2007 and beyond:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:94px;"><col style="width:111px;"><col style="width:12px;"><col style="width:94px;"><col style="width:111px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Names</td><td>Data</td><td>
</td><td>Names</td><td>Data</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>eze e </td><td> Not Enough Data</td><td>
</td><td>fred </td><td style="text-align:right; ">1,998,600</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>fred </td><td style="text-align:right; ">1,998,600</td><td>
</td><td>jonas brothers </td><td style="text-align:right; ">487,700</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>e revo </td><td style="text-align:right; ">10,700</td><td>
</td><td>e tuning </td><td style="text-align:right; ">14,000</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>ivete sangalo </td><td style="text-align:right; ">13,300</td><td>
</td><td>ivete sangalo </td><td style="text-align:right; ">13,300</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>g e patterson </td><td style="text-align:right; ">4,800</td><td>
</td><td>e revo </td><td style="text-align:right; ">10,700</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>e tuning </td><td style="text-align:right; ">14,000</td><td>
</td><td>g e patterson </td><td style="text-align:right; ">4,800</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>jonas brothers </td><td style="text-align:right; ">487,700</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Formula in E2 is:

=IFERROR(LARGE(B$2:B$8,ROWS(E$2:E2)),"")

Array formula in D2 is:

=IF(N(E2),INDEX(A$2:A$8,SMALL(IF(B$2:B$8=E2,ROW(B$2:B$8)-ROW(B$2)+1),COUNTIF($E$2:E2,E2))),"")

Array formulas require committing with CTRL+SHIFT+ENTER.

If you're using Excel 2003 or a previous version, for the first formula, use:

=IF(ROWS(E$2:E2)<=COUNT(B$2:B$8),LARGE(B$2:B$8,ROWS(E$2:E2)),"")

Note that both these solutions are not perfect in as much as "eze e" appears in the wrong place in the first solution (the Sort) and does not appear at all in the second solution. Better to give "eze e" a numerical value like 0 so that things work as desired.

Matty