Sorting and Formulas

  • Thread starter Thread starter Sean12
  • Start date Start date
S

Sean12

I am trying to sort via auto filter. In column A, I have a part description,
in column B I have a COUNTIFS function which counts the instances of Column A
that are subject to some criteria. Whenever I sort in decending order by
column B, with this data:

T 5
U 4
V 6
W 3
X 2
Y 1

it sorts column A by column B, but then it updates column B to a new number
that is associated to the row where the item in column A used to be, like
this:

V 5 (what W used to be, in the formula it references B2)
T 4 (what U used to be, in the formula it references B3)
U 6 (what V used to be, in the formula it references B1)
W 3
X 2
Y 1

How can I get the formula to move with the data in column A? I have tried
using different combinations of $, and none of them worked. I have to do
this multiple times and I would rather not use the paste special, values
function.

Please help!!!
 
You will need to recreate the data in sorted form using formulas. Say your
codes and scores are in the range F6 to G11

List the codes in rankng order:
=INDEX($F$6:$F$11,MATCH(LARGE($G$6:$G$11,ROWS($1:1)),$G$6:$G$11,0))

and copy down six rows.

lookup the scores
=VLOOKUP(I6,$F$6:$G$11,2,0)

and copy down six rows

HTH
Peter
 
Back
Top