Sorting and Formulas

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!!!
 
B

Billy Liddel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top