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