Comparing Multiple Sheets to Get a Result Page

[ryanb4614]

Hello. I have two pricelist I receive from my wholesale companies/distributors. In the sample, I would like to know if there is a way I can compare the pricing to the matching UPC from Wholesale 1 and Wholesale 2. On the "Results" sheet put the Cost different in $ and by the % also the company (Wholesale 1 or Wholesale 2) of who is cheaper. Now not all the UPC will match so if it can just only compare the same UPC that the wholesalers both have. This would help me so much! If it is possible.

***Also on the wholesale sheets I will have the manufacture name listed for each product in a separate column. If it is Wholesaler 1 that is cheaper highlight yellow that row on the results? If that can carry over to the result page!

[walruseggman]

I put the found UPC numbers in column D of your results sheet. This was not something indicated on your sample, but I would think it is information you would want.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

FWIW:

Please Login or Register  to view this content.

[ryanb4614]

Worked great on test worksheet but when I put in all the products in 8,000-9,000 it freezes up. I let it run for an hour. Anything else I can do? VLOOKUP?

[walruseggman]

Who's code are you referring to?

Absolutely no disrespect to JOHN H. DAVIS, but if you had 8-9,000 UPCs on each sheet, the code he provided would, i believe, be looping through 64 million + values.

Does the code I provided also freeze up for you?

[ryanb4614]

I tried both codes. I could't get your code to work properly. Nothing would show when all the products were listed. It would run but no results.

[AB33]

Another option:

[ryanb4614]

Another option:

I am getting a scripting error: Set Dic = CreateObject("scripting.dictionary")

[ryanb4614]

Actual file can be found HERE

[JOHN H. DAVIS]

Ryan:

My code will freeze up with that amount of data. Sorry, I didn't consider files size when I proposed it.

@walrsueggman: Good catch.

[ryanb4614]

No problem I appreciate the effort. Its just so hard going through this a few times a year. It would be great if I can run some code or formula, there are a lot of products !

[walruseggman]

Try this. Took about 1 minute to run, and I got about 3800 matches in you Actual File.

If you run this on other files, make sure your worksheet names in the code (lines 2 - 4) match your workbook's worksheet names.

Please Login or Register  to view this content.

[AB33]

I am getting a scripting error: Set Dic = CreateObject("scripting.dictionary")

Do you have a Mac? The code will not work on a Mac.

[ryanb4614]

Worked great thank you so much!!!

[walruseggman]

Great! Please mark this thread as SOLVED, and any reputation points (the * in the lower left of the posts), is appreciated.

[ryanb4614]

Try this. Took about 1 minute to run, and I got about 3800 matches in you Actual File.

If you run this on other files, make sure your worksheet names in the code (lines 2 - 4) match your workbook's worksheet names.

Please Login or Register  to view this content.

Is there a way I can get the UPC to show up next to the name on the results page?

[walruseggman]

Add this after the "cost2 =" line

Please Login or Register  to view this content.