Compare two worksheets in separate workbooks.

[jmk15315]

I have been asked to create VBA code to compare information from sheet 1 in "Workbook-A" with the information from sheet 1 in "Workbook-B". I have a couple of roadblocks and am hoping someone here can help me out.

Issue 1: I have two columns I need to compare.
- Column "A" (Part Number)
- Column "C" (Quantity)

Issue 2: Part numbers are sorted alphanumerically in the files.

File #1 is the "Master File"
File #2 is the current job configuration

File #2 will contain the same part numbers as well as new numbers or even cases when the new job does not require parts identified in the "Master File" but they may not be in the same cell as the master.

After comparing them, I need to have a new file created that will show all parts combined from Files #1 & #2 and the differences.

For example:
File #1 Rows 1-3 shows:

39932	KT 4/6 GROMMETS, GRAY, QTY-4/6MM CABLE DIA	2
40310	HDMI to DVI-D Cable 15Meter	1
41057	SPLITTER,2-PORT HDMI	1

While File #2 Rows 1-4 shows:

40310	HDMI to DVI-D Cable 15Meter	2
41057	SPLITTER,2-PORT HDMI	1
41251	BTK gy / Blank grommet, small, IP54	6
45237	KVT 80|6 gy / Cable gland, split IP54	1

The newly created file would need to have the files compared and show the delta of the quantity.
Example #1: The part number in File-1, R-1 does not match File-2, R-1, or any other part number in column "A", so I would need the report to generate a "0" for the delta in the quantity.
Example #2: The part number in File-1, R-2 matches the info in File-2, R-1, however, the quantity is different so I would want the return to show the delta of "1"
Example #3: The part in File #1, R-3 matches File-1, R-2 the the delta would be "0"
Example #4: The part numbers in File-1 R-3,4 are not present in File-2, so I would need the information for all columns in these rows be added to the created file.

The "Master" and "Job" files would be in the same format so the new file would need to be the same format as well.

I do not know if this is even possible, but hoping someone far smarter than I am can help me create a macro to accomplish this task.

 

[OaklandJim]

I think that I am close but I am still a bit confused. Might you provide a bit more data -- three or more entries -- for Master and Design with the result needed in Order? Ideally in the Order data you include a brief description of why the value is what it is. What I have now is pretty limited for testing.

 

[OaklandJim]

You did not tell me what to do if there is a negative Delta qty?

Here is a scenario from the data: for the Cable part (40310) the qty in Design is two and qty in Master is one. Master qty - Design qty = minus one.