Formula to compare two sets of similar data on two sheets

Hey everyone!

I'm pretty new to excel formulas, and was looking for some help with a fairly complicated formula. Basically I have two sets of data on two separate sheets. The data is similar to this:

Sheet 1:
Product Name1 | Sell Price1
Milk | $1.05
Cheese | $2.44
Eggs | $0.49

Sheet 2:
Product Name2 | Sell Price2
Milk | $1.62
Lamp | $44.99
Eggs | $3.99

Basically, a lot of the items that are in the first sheet are not in the second sheet, and vice-versa. What I want to do is end up with something like this:

Sheet 1:
Product Name1 | Sell Price1 | Sell Price2
Milk | $1.05 | $1.62
Cheese | $2.44 | $0.00
Eggs | $0.49 | $3.99

Is there an easy way to do this? Or am I making this more complicated than it actually is?

Thanks everyone!

Hi
this is easy if all the product descriptions are identical ie "milk" not "skim milk" or "free range eggs"
is that the case, or will there be some variations?

They should be exactly identical with very few exceptions. The main issue is that it's two lists that are each easily over 16,000 rows, so I'd much rather have 100 exceptions to fix rather than have to go and compare the two lists manually.

For reference though, are Regular Expressions supported in excel scripts?

Hi
not sure what you mean by regular expressions - can you explain

try this macro. It assumes your two lists are in columns A and B of sheets 1 and 2. It copies the content of the first sheet into the destination cell (cell A1 in sheet 3) then looks in all the values in column A in sheet 2. If it finds a match, it copies the value in column B of sheet 2 into column 3 of sheet 3. If not, it adds the content of the cell from sheet 2 at the end of the list on sheet 3.

Please Login or Register  to view this content.

Hey! The script is running. I'm assuming it's a VBscript and that it should take a while to process. While it was running I was considering something: sheet A has a 3rd column that has relevant information -- Quantities of the products. Is there a simply way to shift that over to Sheet 3?

Thanks!

Hi
assuming the quantities are in column C in sheets 1 and 2, this variation should copy them into columns 5 and 6 of sheet 3:

Please Login or Register  to view this content.

pl see the attached file.

It looks like these two functions effectively are equal

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks everyone! You've been insanely helpful! You took an 80+ hour project and turned it into about 3 hours of Excel run time. That's amazing beyond belief.

I have one more request, if anyone's up for it. I have a new set of data that needs to be compared. Basically I have an additional variable that need to be considered, in this case a Category. I've attached a spreadsheet as an example.

In Sheet 1, you'll see our inventory with 3 Variables (Name, Category and Quantity). In Sheet 2, we have similar variables (Name, Category and Sell Price). I would like for it to end up with all of the Inventory of Sheet1 on Sheet3 with the Sell Price, which is dependent on the category matching up between Sheet1 and Sheet2. A good example of this is Carcassone by Rio Grande does not appear on the Sell List, so it's value is 0, where Carcassone by Asmodee does appear, so it's value is equal to the respective value on Sheet2.

If I need to add additional information, please let me know.

Thank you all very much!
-Rob

pl see the attached file with formula.