Lookup values with criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report like this where the name and rates are in column A and the amounts are in column B

colA colB Col
John (Desired ColumnC
rate: $10.00 pay rate
rate: $12.00 pay rate

Susan
rate: $10.00 pay rate
rate: $ 8.75 pay rate
rate: $13.25 pay rate
rate: $ 8.75 pay rate

I want to make column C report "pay rate 1" for the lowest amount and "pay rate 2" for the second lowest, and "pay rate 3" for the third, etc. and if there are two rates the same then they should both have the same C for each individual name from the top of each different rates in column A (as in the above "Desired Column C"). Column C is relative to each person, as in the $10 rate is pay rate 2 for susan and pay rate 1 for John. Column A and B change each week and the number of rates will change for each person each week as well as the amounts (column B). I can get the report so that column d = the name and column e = the rate but I still need column c to = the relative "pay rate x". I need either a vba/macro or formula to perform this. Thanks!
 
Hi
try something like
="pay rate " & RANK(B1,$B$1:$B$3)

--
Regards
Frank Kabel
Frankfurt, Germany
Chad said:
I have a report like this where the name and rates are in column A
Click to expand...
and the amounts are in column B:
colA colB ColC
John (Desired ColumnC)
rate: $10.00 pay rate 1
rate: $12.00 pay rate 2

Susan
rate: $10.00 pay rate 2
rate: $ 8.75 pay rate 1
rate: $13.25 pay rate 3
rate: $ 8.75 pay rate 1

I want to make column C report "pay rate 1" for the lowest amount and
Click to expand...
"pay rate 2" for the second lowest, and "pay rate 3" for the third,
etc. and if there are two rates the same then they should both have the
same C for each individual name from the top of each different rates in
column A (as in the above "Desired Column C"). Column C is relative to
each person, as in the $10 rate is pay rate 2 for susan and pay rate 1
for John. Column A and B change each week and the number of rates will
change for each person each week as well as the amounts (column B). I
can get the report so that column d = the name and column e = the rate
but I still need column c to = the relative "pay rate x". I need
either a vba/macro or formula to perform this. Thanks!
 
That sounds good except that the number of rates per person can vary. for instance, John might have 3 rates this week but 10 rates next week and I need the formula rank only his rates. Is there a way to add criteria to the rank function?
 
Hi
how do you know how many rates John has. How are his rates separated
from the other rates (e.g. do they have a column which states his name,
are there blanks rows, etc.)

--
Regards
Frank Kabel
Frankfurt, Germany
Chad said:
That sounds good except that the number of rates per person can vary.
Click to expand...
for instance, John might have 3 rates this week but 10 rates next week
and I need the formula rank only his rates. Is there a way to add
criteria to the rank function?
 
Back
Top