Comparing Spreadshhet data
 

I am new to Advanced Excel functions and appreciate any need help with a
project. I have 2 sheets that need to be compared. Both sheets only consist
of 2 columns labeled ISBN and COST. Some data will be unique to each sheet,
while other ISBN data is not. When a match of the ISBN (column 1) is found
in both sheets, I need to then compare the COST of that ISBN on each sheet
and I would like the cheaper COST's row deleted. I could also have the
output onto a new sheet of the unique ISBN and its COST, without duplicates.
Ive been studying for days on creating a complicated function and am just
not getting anywhere, even though I have learned some other useful functions.
TIA .

Comparing Spreadshhet data
 

My current 2 sheets have the following sample;
Sheet1
ISBN PRICE
9876543210 4.5 Matches on other sheet, but lower, I dont want
this price, outputted to sheet3
8976563524 4.0 <-unique to sheet1, would like it ouput to sheet3
7562547362 5.25 <-unique to sheet1, would like it ouput to sheet3

sheet2
ISBN PRICE
1234567890 5.35 <-unique to sheet2, would like it ouput to sheet3
9876543210 4.6 <-this matches but is higher so I would like
this output to sheet3

Thanks
Denim

"Denim" wrote:

I am new to Advanced Excel functions and appreciate any need help with a
project. I have 2 sheets that need to be compared. Both sheets only consist
of 2 columns labeled ISBN and COST. Some data will be unique to each sheet,
while other ISBN data is not. When a match of the ISBN (column 1) is found
in both sheets, I need to then compare the COST of that ISBN on each sheet
and I would like the cheaper COST's row deleted. I could also have the
output onto a new sheet of the unique ISBN and its COST, without duplicates.
Ive been studying for days on creating a complicated function and am just
not getting anywhere, even though I have learned some other useful functions.
TIA .

Comparing Spreadshhet data
 

You could join the ISBN list from Sheet2 to the list from Sheet1 and
put this in Sheet3 - there you could use Advanced Filter to get rid of
the duplicates (Debra Dalgleish shows how he

http://www.contextures.com/xladvfilter01.html

under the section Filter Unique Records).

Assuming your unique records are in column A of Sheet3, then in B2 of
Sheet3 you would have a formula like this:

=MAX(IF(ISNA(MATCH(A2,Sheet1!A:A,0)),0,VLOOKUP(A2, Sheet1!A:B,
2,0)),IF(ISNA(MATCH(A2,Sheet2!A:A,0)),0,VLOOKUP(A2 ,Sheet2!A:B,2,0)))

and copy this down for as many rows as are used in column A. This
should give you what you want.

Hope this helps.

Pete

On Feb 25, 10:16 pm, Denim wrote:
My current 2 sheets have the following sample;
Sheet1
ISBN PRICE
9876543210 4.5 Matches on other sheet, but lower, I dont want
this price, outputted to sheet3
8976563524 4.0 <-unique to sheet1, would like it ouput to sheet3
7562547362 5.25 <-unique to sheet1, would like it ouput to sheet3

sheet2
ISBN PRICE
1234567890 5.35 <-unique to sheet2, would like it ouput to sheet3
9876543210 4.6 <-this matches but is higher so I would like
this output to sheet3

Thanks
Denim



"Denim" wrote:
I am new to Advanced Excel functions and appreciate any need help with a
project. I have 2 sheets that need to be compared. Both sheets only consist
of 2 columns labeled ISBN and COST. Some data will be unique to each sheet,
while other ISBN data is not. When a match of the ISBN (column 1) is found
in both sheets, I need to then compare the COST of that ISBN on each sheet
and I would like the cheaper COST's row deleted. I could also have the
output onto a new sheet of the unique ISBN and it's COST, without duplicates.
I've been studying for days on creating a complicated function and am just
not getting anywhere, even though I have learned some other useful functions.
TIA .- Hide quoted text -

- Show quoted text -