J
JC
I have two groups of data to compare. The first group is listed in
columnar fashion, like this:
item# price
0001 100
0001 150
0001 50
0001 100
0002 100
0002 150
0002 50
0002 100
i.e, each item number has 4 separate prices associated with it.
The comparison data is listed in row format, like this:
item# price price price price
0001 100 150 50 100
0002 100 150 50 100
I am trying to use conditional formatting or a formula to compare the
price data. Right now, I have the row-based data listed to the right of
the column-based data on the worksheet, so the column-based data list
is much longer than the row-based list (i.e. it takes 4 lines to list
the price data for each item in columns, but only 1 line to list it in
rows.)
A B C D E F G H
1 item# price item# price price price price comparison
2 0001 100 0001 100 150 50 100
3 0001 150 0002 100 150 50 100
4 0001 50 0003 100 150 50 100
5 0001 100
6 0002 100
7 0002 150
8 0002 50
9 0002 100
10 0003 100
I can create formulas, etc. to reference the correct data, but this
only works for a single line. When I copy the formula, however, the
relative reference automatically adjusts to compare a single row down
from the above reference, instead of looking 3 rows down to where the
next item# starts in the columnar-based data.
I.e., if I write a formula in H3 to compare D3 with B6, then copy this
cell/formula to the next cell down (H4), it will compare D4 (correct)
with B4, instead of B10 (which is what I want to campare it to).
Anyone know a way around this, or is it a built in limitation of
Excel's cell referencing functions? If the explanation is unclear I
would be happy to explain further.
columnar fashion, like this:
item# price
0001 100
0001 150
0001 50
0001 100
0002 100
0002 150
0002 50
0002 100
i.e, each item number has 4 separate prices associated with it.
The comparison data is listed in row format, like this:
item# price price price price
0001 100 150 50 100
0002 100 150 50 100
I am trying to use conditional formatting or a formula to compare the
price data. Right now, I have the row-based data listed to the right of
the column-based data on the worksheet, so the column-based data list
is much longer than the row-based list (i.e. it takes 4 lines to list
the price data for each item in columns, but only 1 line to list it in
rows.)
A B C D E F G H
1 item# price item# price price price price comparison
2 0001 100 0001 100 150 50 100
3 0001 150 0002 100 150 50 100
4 0001 50 0003 100 150 50 100
5 0001 100
6 0002 100
7 0002 150
8 0002 50
9 0002 100
10 0003 100
I can create formulas, etc. to reference the correct data, but this
only works for a single line. When I copy the formula, however, the
relative reference automatically adjusts to compare a single row down
from the above reference, instead of looking 3 rows down to where the
next item# starts in the columnar-based data.
I.e., if I write a formula in H3 to compare D3 with B6, then copy this
cell/formula to the next cell down (H4), it will compare D4 (correct)
with B4, instead of B10 (which is what I want to campare it to).
Anyone know a way around this, or is it a built in limitation of
Excel's cell referencing functions? If the explanation is unclear I
would be happy to explain further.