I am trying to compare 2 groups of data and tell me what rank the item is now
compared to what it was 4 hours ago. Any help is much appreciated as I have
been unsuccessful trying to do on my own.

Morning Report
A1 B1 D1
1 Pepsi2 1
2 Coke1 2
3 BigRed3 3

Mid-Day Report (I paste starting in cell J1 and compare)
J1 K1 M1
1 Coke1 2
2 BigRed3 3
3 Pepsi2 1

My list is an average of 200 products so I want to compare K1 to B1:B200 and
tell me where it used to be ranked. (As an added bonus I would like to have
the new ranking show in RED if it dropped in rankings as the Pepsi2 did
above.)

Any help is VERY much appreciated.
David

This LOOKS like a ranking issue, but it's actually just a VLOOKUP or INDEX.
In M1, put this formula:

=VLOOKUP(K1,$B$1:$C$200,2,FALSE)

....and copy down. This simply brings over the rankings from the earlier
listing by matching the product name.

Now I was asssuming the "rank" was in column C. If the rank is actually in
column A, then use this instead:

=INDEX($A$1:$A$200,MATCH(K1,$B$1:$B$200,0))

(what is column C, in that case?)

To get the rankings that have dropped to light up, use conditional formatting.

Highlight the J:M columns and open FORMAT CONDITIONAL FORMATTING

Condition1: Formula Is: =$M1<$J1
FORMAT Pattern yellow

Now anytime an item drops in rank, the whole 3-cell set will light up.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

I am trying to compare 2 groups of data and tell me what rank the item is now
compared to what it was 4 hours ago. Any help is much appreciated as I have
been unsuccessful trying to do on my own.

Morning Report
A1 B1 D1
1 Pepsi2 1
2 Coke1 2
3 BigRed3 3

Mid-Day Report (I paste starting in cell J1 and compare)
J1 K1 M1
1 Coke1 2
2 BigRed3 3
3 Pepsi2 1

My list is an average of 200 products so I want to compare K1 to B1:B200 and
tell me where it used to be ranked. (As an added bonus I would like to have
the new ranking show in RED if it dropped in rankings as the Pepsi2 did
above.)

Any help is VERY much appreciated.
David

JBeaucaire, the INDEX function worked PERFECT!!!! That is what I have been
trying to figure out on my own for a week now. That is awesome!

Now, since you asked about Column "C", I will ask my next question.

Column "C" is a (Revenue) column. So if I wanted to show how much the
dollars changed from the previous 4 hours is there a way to include the
calculation in the INDEX formula? (C and L would be the revenue column)

Thanks again for you help to this point. Its a HUGE help.
David

"JBeaucaire" wrote:

This LOOKS like a ranking issue, but it's actually just a VLOOKUP or INDEX.
In M1, put this formula:

=VLOOKUP(K1,$B$1:$C$200,2,FALSE)

...and copy down. This simply brings over the rankings from the earlier
listing by matching the product name.

Now I was asssuming the "rank" was in column C. If the rank is actually in
column A, then use this instead:

=INDEX($A$1:$A$200,MATCH(K1,$B$1:$B$200,0))

(what is column C, in that case?)

To get the rankings that have dropped to light up, use conditional formatting.

Highlight the J:M columns and open FORMAT CONDITIONAL FORMATTING

Condition1: Formula Is: =$M1<$J1
FORMAT Pattern yellow

Now anytime an item drops in rank, the whole 3-cell set will light up.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

I am trying to compare 2 groups of data and tell me what rank the item is now
compared to what it was 4 hours ago. Any help is much appreciated as I have
been unsuccessful trying to do on my own.

Morning Report
A1 B1 D1
1 Pepsi2 1
2 Coke1 2
3 BigRed3 3

Mid-Day Report (I paste starting in cell J1 and compare)
J1 K1 M1
1 Coke1 2
2 BigRed3 3
3 Pepsi2 1

My list is an average of 200 products so I want to compare K1 to B1:B200 and
tell me where it used to be ranked. (As an added bonus I would like to have
the new ranking show in RED if it dropped in rankings as the Pepsi2 did
above.)

Any help is VERY much appreciated.
David

Aww...you can do it! It's the exact same thing. INDEX the values, look them
with a MATCH to bring over the old value, then subtract the new one to get
the difference.

=INDEX($C$2:$C$4,MATCH(K2,$B$2:$B$4,0))-L2

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

JBeaucaire, the INDEX function worked PERFECT!!!! That is what I have been
trying to figure out on my own for a week now. That is awesome!

Now, since you asked about Column "C", I will ask my next question.

Column "C" is a (Revenue) column. So if I wanted to show how much the
dollars changed from the previous 4 hours is there a way to include the
calculation in the INDEX formula? (C and L would be the revenue column)

Thanks again for you help to this point. Its a HUGE help.
David

"JBeaucaire" wrote:

This LOOKS like a ranking issue, but it's actually just a VLOOKUP or INDEX.
In M1, put this formula:

=VLOOKUP(K1,$B$1:$C$200,2,FALSE)

...and copy down. This simply brings over the rankings from the earlier
listing by matching the product name.

Now I was asssuming the "rank" was in column C. If the rank is actually in
column A, then use this instead:

=INDEX($A$1:$A$200,MATCH(K1,$B$1:$B$200,0))

(what is column C, in that case?)

To get the rankings that have dropped to light up, use conditional formatting.

Highlight the J:M columns and open FORMAT CONDITIONAL FORMATTING

Condition1: Formula Is: =$M1<$J1
FORMAT Pattern yellow

Now anytime an item drops in rank, the whole 3-cell set will light up.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

I am trying to compare 2 groups of data and tell me what rank the item is now
compared to what it was 4 hours ago. Any help is much appreciated as I have
been unsuccessful trying to do on my own.

Morning Report
A1 B1 D1
1 Pepsi2 1
2 Coke1 2
3 BigRed3 3

Mid-Day Report (I paste starting in cell J1 and compare)
J1 K1 M1
1 Coke1 2
2 BigRed3 3
3 Pepsi2 1

My list is an average of 200 products so I want to compare K1 to B1:B200 and
tell me where it used to be ranked. (As an added bonus I would like to have
the new ranking show in RED if it dropped in rankings as the Pepsi2 did
above.)

Any help is VERY much appreciated.
David

:-) Actually this is the exact formula I created. The problem is this so I
thought I did something wrong.

The old 'earning' was $13.99 and the new is $14.63. This is an increase of
$0.64 but what I get is -$0.64 so it looks backwards. Is there a way to
reverse the output so it shows an increase not a decrease?

"JBeaucaire" wrote:

Aww...you can do it! It's the exact same thing. INDEX the values, look them
with a MATCH to bring over the old value, then subtract the new one to get
the difference.

=INDEX($C$2:$C$4,MATCH(K2,$B$2:$B$4,0))-L2

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

JBeaucaire, the INDEX function worked PERFECT!!!! That is what I have been
trying to figure out on my own for a week now. That is awesome!

Now, since you asked about Column "C", I will ask my next question.

Column "C" is a (Revenue) column. So if I wanted to show how much the
dollars changed from the previous 4 hours is there a way to include the
calculation in the INDEX formula? (C and L would be the revenue column)

Thanks again for you help to this point. Its a HUGE help.
David

"JBeaucaire" wrote:

This LOOKS like a ranking issue, but it's actually just a VLOOKUP or INDEX.
In M1, put this formula:

=VLOOKUP(K1,$B$1:$C$200,2,FALSE)

...and copy down. This simply brings over the rankings from the earlier
listing by matching the product name.

Now I was asssuming the "rank" was in column C. If the rank is actually in
column A, then use this instead:

=INDEX($A$1:$A$200,MATCH(K1,$B$1:$B$200,0))

(what is column C, in that case?)

To get the rankings that have dropped to light up, use conditional formatting.

Highlight the J:M columns and open FORMAT CONDITIONAL FORMATTING

Condition1: Formula Is: =$M1<$J1
FORMAT Pattern yellow

Now anytime an item drops in rank, the whole 3-cell set will light up.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

I am trying to compare 2 groups of data and tell me what rank the item is now
compared to what it was 4 hours ago. Any help is much appreciated as I have
been unsuccessful trying to do on my own.

Morning Report
A1 B1 D1
1 Pepsi2 1
2 Coke1 2
3 BigRed3 3

Mid-Day Report (I paste starting in cell J1 and compare)
J1 K1 M1
1 Coke1 2
2 BigRed3 3
3 Pepsi2 1

My list is an average of 200 products so I want to compare K1 to B1:B200 and
tell me where it used to be ranked. (As an added bonus I would like to have
the new ranking show in RED if it dropped in rankings as the Pepsi2 did
above.)

Any help is VERY much appreciated.
David

JBeaucaire, my mistake. You had it on the nose. I reversed my criteria so it
was returning what I was asking for.

Thanks again for your help.
David

"Big_Tater" wrote:

:-) Actually this is the exact formula I created. The problem is this so I
thought I did something wrong.

The old 'earning' was $13.99 and the new is $14.63. This is an increase of
$0.64 but what I get is -$0.64 so it looks backwards. Is there a way to
reverse the output so it shows an increase not a decrease?

"JBeaucaire" wrote:

Aww...you can do it! It's the exact same thing. INDEX the values, look them
with a MATCH to bring over the old value, then subtract the new one to get
the difference.

=INDEX($C$2:$C$4,MATCH(K2,$B$2:$B$4,0))-L2

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

JBeaucaire, the INDEX function worked PERFECT!!!! That is what I have been
trying to figure out on my own for a week now. That is awesome!

Now, since you asked about Column "C", I will ask my next question.

Column "C" is a (Revenue) column. So if I wanted to show how much the
dollars changed from the previous 4 hours is there a way to include the
calculation in the INDEX formula? (C and L would be the revenue column)

Thanks again for you help to this point. Its a HUGE help.
David

"JBeaucaire" wrote:

This LOOKS like a ranking issue, but it's actually just a VLOOKUP or INDEX.
In M1, put this formula:

=VLOOKUP(K1,$B$1:$C$200,2,FALSE)

...and copy down. This simply brings over the rankings from the earlier
listing by matching the product name.

Now I was asssuming the "rank" was in column C. If the rank is actually in
column A, then use this instead:

=INDEX($A$1:$A$200,MATCH(K1,$B$1:$B$200,0))

(what is column C, in that case?)

To get the rankings that have dropped to light up, use conditional formatting.

Highlight the J:M columns and open FORMAT CONDITIONAL FORMATTING

Condition1: Formula Is: =$M1<$J1
FORMAT Pattern yellow

Now anytime an item drops in rank, the whole 3-cell set will light up.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Big_Tater" wrote:

I am trying to compare 2 groups of data and tell me what rank the item is now
compared to what it was 4 hours ago. Any help is much appreciated as I have
been unsuccessful trying to do on my own.

Morning Report
A1 B1 D1
1 Pepsi2 1
2 Coke1 2
3 BigRed3 3

Mid-Day Report (I paste starting in cell J1 and compare)
J1 K1 M1
1 Coke1 2
2 BigRed3 3
3 Pepsi2 1

My list is an average of 200 products so I want to compare K1 to B1:B200 and
tell me where it used to be ranked. (As an added bonus I would like to have
the new ranking show in RED if it dropped in rankings as the Pepsi2 did
above.)

Any help is VERY much appreciated.
David