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.

try

http://www.j-walk.com/ss/excel/usertips/tip073.htm

hth
regards from Brazil
Marcelo

"JC" escreveu:

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.

Thanks for the link Marcelo, but it doesn't really address my issue. I
am having trouble with the relative cell references rather than the
comparison of the data between the two data sets.

Marcelo wrote:
try

http://www.j-walk.com/ss/excel/usertips/tip073.htm

Not sure what kind of comparison formula you had in mind, so I just did a
sample equality comparison that returns yes or no answers. So in H2, you can
put:
=IF(D2=OFFSET($B$2,(ROW()-2)*4,0),"yes","no")
That formula compares D2 with B2.
And if you copy the formula down to H3 and H4, the comparison will be D3
with B6, and D4 with B10.

-Simon

"JC" wrote:

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.