Comparing two lists

[pgarcia]

I have to list that change daily.
A B C D E F G
info info $ C1-D1 $ info info

When D1 brings back a negative amount, I write "down" in that cell and then
shift the date to the right of it down. Redo the formula to cell-cell. When
it comes back with positive amount, I write "up" in that cell and then shift
the date to the left of it down.
I need the up and downs to compare from yesterdays data.

This is taking way to long. How can I shorten the process?
Example:
A B C(formula) D E
185027 $6.40 $- $6.40 CIRCOR ENERGY PRODUCTS
down $6.40 MONTAPLAST OF NORTH AMERICA
down $6.35 MONARCH HYDRAULICS
down $6.35 SONUS NETWORKS
93247 $6.34 $(0.01) $6.35 UNITED REFRIGERATION
1465 $6.30 $(0.04) $6.34 ACME CRYOGENICS
499512 $6.23 $(0.09) $6.32 WOOD GROUP TURBO POWER

 

[JLatham]

You don't say what you do if the values in C and D are the same (going by the
long example, not the first setup you described). What if B1-D1 = 0?

A quick 'fix' for the formula in C would be this formula in row 1
=IF(B1-D1<0,"down",IF(B1-D1>0,"up",B1-D1))
fill that formula down the sheet. That will do your typing of 'down' and
'up' for you as a start.

For me, seeing an example of 'before' and 'after' that includes changes in
both directions, and no change would help figure out how to attack the second
half of your problem. But whatever it is, I'm pretty sure that it's going to
involve an added helper column with a formula in it to see the results of the
"down"/"up" testing and probably a test of the results of the "down"/"up"
test in the previous row to figure out whether to virtually continue to pull
the values on down the sheet or get a new value from a change from "down" to
"up" or from "up" to "down".

 

[pgarcia]

Can I email you an example?

You don't say what you do if the values in C and D are the same (going by the
long example, not the first setup you described). What if B1-D1 = 0?

A quick 'fix' for the formula in C would be this formula in row 1
=IF(B1-D1<0,"down",IF(B1-D1>0,"up",B1-D1))
fill that formula down the sheet. That will do your typing of 'down' and
'up' for you as a start.

For me, seeing an example of 'before' and 'after' that includes changes in
both directions, and no change would help figure out how to attack the second
half of your problem. But whatever it is, I'm pretty sure that it's going to
involve an added helper column with a formula in it to see the results of the
"down"/"up" testing and probably a test of the results of the "down"/"up"
test in the previous row to figure out whether to virtually continue to pull
the values on down the sheet or get a new value from a change from "down" to
"up" or from "up" to "down".

 

[JLatham]

Sure, remove spaces from this and it'll get to me. Send the Excel file as a
regular email attachment:
HelpFrom @ jlathamsite.com