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'm not so clear about what is needed. Ideally you provide a link to your workbook. Otherwise, someone willing to assist might need to recreate it to develop and test the necessary code. Put the file on Box, Dropbox, 1Drive, Google Drive or Sharepoint. Provide a link to the workbook using the link icon at the top of the message box.

Consider using Mr Excel's excellent add-in called XL2BB which enables you to post a portion of a worksheet. With that add-in you can show relevant data in a usable form. That way someone willing to help you does not have to create fake data or guess what your data looks like. See XL2BB - Excel Range to BBCode for details.

 

[jmk15315]

So, I don't know if what I am trying to accomplish is even possible. I understand the "Compare" ability as it pertains to two workbooks, but the two sheets are not the same data, cell-to-cell. Trying to figure out how I can compare the data to determine two outputs. First, the part numbers that are present in one sheet but not the other, and second, return a value for the difference in the quantity of those part numbers that match but the quantities do not.

 

[alansidman]

Load each table to Power Query Editor.
Then Join the tables in a full outer join using the part number as the common identifier.

let
    Source = Table.NestedJoin(Table2, {"Column1"}, Table3, {"Column1"}, "Table3", JoinKind.FullOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Column1", "Column2", "Column3"}, {"Column1.1", "Column2.1", "Column3.1"})
in
    #"Expanded Table3"

Column1	Column2	Column3	Column1.1	Column2.1	Column3.1
40310	HDMI to DVI-D Cable 15Meter	1	40310	HDMI to DVI-D Cable 15Meter	2
41057	SPLITTER,2-PORT HDMI	1	41057	SPLITTER,2-PORT HDMI	1
			41251	BTK gy / Blank grommet, small, IP54	6
			45237	KVT 80|6 gy / Cable gland, split IP54	1
39932	KT 4/6 GROMMETS, GRAY, QTY-4/6MM CABLE DIA	2

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[OaklandJim]

I wish that I knew Power Query better. I've never used it so I'm feeling around in the dark with it. It may be an excellent solution.

Did the suggested approach using Power Query work? If not I'm pretty sure that I can assist.

If you still want assistance from me then I have a few questions.

1. When you say "Delta" quantity -- is the Delta = Master qty. - Job qty. or Job qty. - Master qty.?

2.a. If Delta = Master qty. - Job qty. then what if Job qty. > Master qty.?
2.b. If Delta = Job qty - Master qty. then what if Master qty. > Job qty.?

3. What if a part number is in the Job data but not in the Master data?

 

[jmk15315]

I wish that I knew Power Query better. I've never used it so I'm feeling around in the dark with it. It may be an excellent solution.

Did the suggested approach using Power Query work? If not I'm pretty sure that I can assist.

If you still want assistance from me then I have a few questions.

1. When you say "Delta" quantity -- is the Delta = Master qty. - Job qty. or Job qty. - Master qty.?

2.a. If Delta = Master qty. - Job qty. then what if Job qty. > Master qty.?
2.b. If Delta = Job qty - Master qty. then what if Master qty. > Job qty.?

3. What if a part number is in the Job data but not in the Master data?

I am in dire need to teach myself how to use Power Query, just need to find the time. I am always open to suggestions.

 

[jmk15315]

I wish that I knew Power Query better. I've never used it so I'm feeling around in the dark with it. It may be an excellent solution.

Did the suggested approach using Power Query work? If not I'm pretty sure that I can assist.

If you still want assistance from me then I have a few questions.

1. When you say "Delta" quantity -- is the Delta = Master qty. - Job qty. or Job qty. - Master qty.?

2.a. If Delta = Master qty. - Job qty. then what if Job qty. > Master qty.?
2.b. If Delta = Job qty - Master qty. then what if Master qty. > Job qty.?

3. What if a part number is in the Job data but not in the Master data?

Sorry, I should have included my response in my first reply.

1. When you say "Delta" quantity -- is the Delta = Master qty. - Job qty. or Job qty. - Master qty.? This would be a +/- Delta. The "Master" would be the base, but if there are more/fewer needed after design, then the Order would need to be adjusted.

2.a. If Delta = Master qty. - Job qty. then what if Job qty. > Master qty.?
2.b. If Delta = Job qty - Master qty. then what if Master qty. > Job qty.?

3. What if a part number is in the Job data but not in the Master data? If a part number is present in the "Design" but is not in the "Master" then it would be added to the order, likewise, if the part is not present in the "Design" but is in the "Master" then it would, then it would be omitted.

 

[OaklandJim]

Sorry, The names seemed to have changed. We are dealing with "Master," "Design" and "Order"?

So negative values are ok if the qty in Master < qty in Design?

So I am clear...

If part number is in Design but not Master then it is included in Order. If the part number is in the Master but not in Design then it is not included?

 

[OaklandJim]

I don't why this is confusing to me. Here is what I have now. I can change it.

If the part number is in Master but not in Design then do not include it.

If the part number is in Design and not in Master then include it. Delta is zero.

If the part number is in both then include it. Delta is Design qty. minus Master qty.

Is THAT correct?

 

[jmk15315]

I don't why this is confusing to me. Here is what I have now. I can change it.

If the part number is in Master but not in Design then do not include it.

If the part number is in Design and not in Master then include it. Delta is zero.

If the part number is in both then include it. Delta is Design qty. minus Master qty.

Is THAT correct?

Almost correct, Last bullet Example (Master Part=4, Design Part=5, then Order Qty is 5), if (Master Part=4, Design Part=2, then Order Qty is 2)