Share

EXCEL problem

Avatar

Need help for Sort problem in Excel

What I have.....
    columns
row  T  U
  4    0  1
  5    0  2
  6    3  0
  7    2  3

T column is the formula =countif($B$1:$B$6,U4)
U column is the rogue.
How do I sort T largest to smallest
T column is the frequency rogue result of the T countif formula.
This should be the end result which I cannot get when I try to sort
row T  U
 4   3  0
 5   2  3
 6   0  1
 7   0  2
Everything I tried for sort does not work in Excel.
The example shown is for the last 5 rogues for my P4,rogue b
Note: Row 1 is for the headers.

  Thanks

   GW

Avatar

You could use a combination of the Functions Large, Match and Index to do this in another block of Cells

Here is a setup for this

To Get your First Row

Column 1                Column 2                      Column 3                                                         

4                          Large($B$2:$B$6,1)       Index($C$2:$C$6,Match(Large($B$2:$B$6,1),$B$2:$B$6,0),1,1)

Change the Red 1 to 2,3,4 in the next rows to get your desired order.

Hope this helps

Allen

Avatar
In response to AllenB

The problem with the above formulas is that the match formula will not differentiate when any of the counts in column B are equal.

To counter this add either A2/1000000 or .0001-A2/10000000.

The first will make the larger Row Number largest, the Second will make the lower Row Number Largest.

And use the Round Function in Column 2 >>> Round(Large($B$2:$B$6,1),0)

Allen

TopEnd of thread (1 page)

Welcome Guest

Your last visit: Thu, Sep 24, 2020, 2:58 am

Log In

Log InCancel

Forgot your username?

Forgot your password?