compare columns of two workbooks and add to master workbook

[chlin]

Hi everyone,

I have these two workbooks, master workbook and newreport workbook. I need to compare columns A to columns K of sheet1 of master workbook to the same columns A-K of sheet1 in the newreport workbook. If there is a difference found in the newreport workbook which means the master workbook does not contain this data, that row would have to be added to the last row of the master workbook.

Any help is appreciated. Thanks. :)

[SamT]

We need more information about the structure of the Data. Can you sanitize the two workbooks and upload them for us to see exactly how they are organized?

By Sanitize, I mean that all personal and proprietary information is hidden by the use of dummy data.

[bubleeshaark]

More information would be nice. If you know much about VBA, try applying this concept to your data

Option Explicit
Sub Macro1()
Dim lastRow1 As Integer, lastRow2 As Integer, r1 As Integer, r2 As Integer, c As Integer, foundMatch As Integer
Dim addRow As Boolean
On Error GoTo Macro1_Error
lastRow1 = Workbooks("newreport").Sheets("YourSheetNameHere").Range("A1048576").End(xlUp).Row '\\Assumes the last row of data in column A isn't blank
For r1 = 2 To lastRow1 '\\Assuming your first row is 2 (headers in row 1) in Newreport workbook
addRow = True
lastRow2 = Workbooks("master").Sheets("YourSheetNameHere").Range("A1048576").End(xlUp).Row
For r2 = 2 To lastRow2 '\\Assuming your first row is 2 (headers in row 1) in Master workbook
foundMatch = 0
For c = 1 To 11 '\\Columns A to K
If Workbooks("newreport").Sheets("YourSheetNameHere").Cells(r1, c).Value = Workbooks("master").Sheets("YourSheetNameHere").Cells(r2, c).Value Then
foundMatch = foundMatch + 1
End If
Next c
If foundMatch = 12 Then '\\Row Contents Match Exactly!!
addRow = False
Exit For '\\We can stop looking
End If
Next r2
If addRow Then
Workbooks("newreport").Sheets("YourSheetNameHere").Range("A" & lastRow2 + 1 & ":K" & lastRow2 + 1) = Workbooks("newreport").Sheets("YourSheetNameHere").Range("A" & r1 & ":K" & r1)
End If
Next r1
On Error GoTo 0
Exit Sub
Macro1_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") on line " & Erl & " in procedure Macro1 of Module Module1"
End Sub

[chlin]

Hi SamT and bubleeshaark,

Attached herewith the master workbook. The macro is to be run from the master workbook.

[chlin]

Attached the newreport workbook too. Thanks for your help.

[chlin]

Hi bubleeshaark, I have tried your macro. But the columns did not copy over to the master workbook, instead I got a blank line in the newreport workbook? The total amount of rows in the master workbook should be the same as the newreport workbook after running the macro. Any ideas?

[SamT]

It looks to like all records can be differentiated by (Invoice number & Invoice Total quantity.) Is that true?

In the attached, the green rows on Sheet "New Report" are already present in sheet "Master."

The other colored cells show discrepancies in Part # and GRN for a given Invoice #. Are those Typographical Errors? Are they needed to differentiate two records? IOW, can a given Invoice # ever have the same Inv Tot Qty AND different Part # or GRN?

[chlin]

The same invoice no can be differentiated by grn no. and total qty. Each invoice no. may have a few total quantity that is the same. You are right about a invoice having the same part no, the yellow cells are a mistake.

So to conclude, a invoice can have the same inv total qty, but must have the same part. The grn # is generally different for every record except for those highlighted in green which total qty is equal to 1. These are generated by the system. For example, there might be 3 records with the same grn no, but two of which have the total qty which is 1, the last one have the total qty which is 10,000. Hope this makes sense.

[SamT]

Ignore Yellow. Is typographical error.
Blue can occur
Also two of same invoice # with with same qty can occur, but will have different GRN.

So:

(Many means = More than one)
Many Records can have same Inv #
Many Records can have same Qty
Many Records can have same GRN

Unique records must have Unique (Inv# + Qty + GRN) ... Is correct?

More to come after holidays :)

[chlin]

Hi SamT, yes originally the unique records is (Inv# + Qty + GRN). But after checking through the records, I discovered a few scenarios, so there aren't any more unique records anymore. There might be two of the exact same record. I have attached the new workbook and added a few more rows from row 21 onwards.

[SamT]

Invoice #

Date

Invoice Price

Quantity

GRN #



1

580038852


0.318
6,000
668021606||45656774


2

580038852

0.318
-6,000
668021606||45656774


3

580038852


0.318
6,000
668021606||45656774


4

580038852


-85.36
1
668021606||45656774



5

580038852

85.36
1
668021606||45656774


6

580038852


0.
1
668021606||45656774


7

580038852

0.
1
668021606||45656774


8

580038845


1.68
9,000
668035113||45656767


9

580038845

1.68
-9,000
668035113||45656767


10

580038845


1.68
9,000
668035113||45656767


11

580038845

1.68
9,000
668035113||45656767




While sorting out invoice records that indicate a Cancellation, (Rows 1+2, 4+5, 8+9,) is possible by comparing all four values, that table tells me that you have a serious Business Procedures problem.

Taking lines 1, 2, & 3. I think that the Order Entry Person entered qty 6000, then canceled the order, then reinstated the order. Programmatically, there is no way to distinguish to original (+6000) from the reinstatement (also +6000.)

in re lines 6 & 7, even I don't have a clue what is going on, and no programer will be able to decide how to handle this situation.

About lines 8, 9, 10, & 11: 8 & 9 are a cancellation, but do 10 & 11 indicate that the customer ordered 18,000 or is it a data entry error? In any case, no program can sort them properly.

What I think has happened in your company is that someone has "Paved the cow path (http://blogs.perficient.com/bpe/2012/03/02/dont-pave-the-cow-path/)" and now it needs a good overhaul.

[chlin]

Hi SamT, I have looked through the data again and realised that there is another column called Line Item in the hidden columns. So the unique records should be Inv# + Line Item + GRN. Each invoice can have different part nos as well. I have attached the amended workbook. Sorry for the confusion earlier.

[chlin]

Any idea how to compare the three columns and add the records to the master workbook?

[SamT]

Chlin,

I've been in a mental funk for the last few days. I have made some progress on the problem.

I am working on the assumption that you don't need to reference the GRN because no line item on a given invoice should ever change the GRN after it has been once assigned. If this is incorrect, let me know.

[chlin]

Hi SamT,

I checked the records indeed there is little need to reference the GRN. However, there was just two lines that were the exception. To play safe, I think it is better to include the GRN.

Thanks for all your help. :content: It is nearing the weekend, just relax a bit.

[snb]

Sub M_snb()
sn = Sheets("Master").Cells(1).CurrentRegion
sq = Sheets("New Report").Cells(1).CurrentRegion

For j = 1 To UBound(sn)
sn(j, 1) = Join(Application.Index(sn, j), "_")
Next
For j = 1 To UBound(sq)
c00 = c00 & vbLf & Join(Application.Index(sq, j), "_")
Next

For j = 2 To UBound(sn)
If InStr(c00 & vbLf, vbLf & sn(j, 1) & vbLf) = 0 Then Sheets("New Report").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(sn, 2)) = Sheets("Master").Cells(j, 1).Resize(, UBound(sn)).Value
Next
End Sub

[chlin]

Hi Snb,

Thanks for your reply. Is this code for comparing the entire Range A-K? Actually, I will need the macro to compare columns G, L & K for both workbooks and there are data after the last column which is L in the master workbook that I need to retain.

[chlin]

Hi snb, can you change the macro to compare columns G,L & K instead? Thanks.

[SamT]

chlin,

snb's code will be much faster and smaller than anytihing I can write. However if you just cannot use his code even with his help, (a very unlikely possibility,) let me know.

[snb]

This code compares columns G (7), K(11) and L (12)
I deleted the empty row 1 in each worksheet.


Sub M_snb()
sn = Sheets("New Report").Cells(1).CurrentRegion
sq = Sheets("Master").Cells(1).CurrentRegion

For j = 2 To UBound(sq)
c00 = c00 & vbLf & sq(j, 7) & "_" & sq(j, 11) & "_" & sq(j, 12)
Next

For j = 2 To UBound(sn)
If InStr(c00 & vbLf, vbLf & sn(j, 7) & "_" & sn(j, 11) & "_" & sn(j, 12) & vbLf) = 0 Then Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(sn, 2)) = Sheets("New Report").Cells(j, 1).Resize(, UBound(sn)).Value
Next
End Sub

[chlin]

Hi snb & SamT, thanks a lot for your help.

snb,

I am able to get this to work after changing this line

If InStr(c00 & vbLf, vbLf & sn(j, 7) & "_" & sn(j, 11) & "_" & sn(j, 12) & vbLf) = 0 Then Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(sn, 2)) = Sheets("New Report").Cells(j, 1).Resize(, UBound(sq).Value


I am not sure how it works though. Is it possible to only compare and add the data for which the Inv date is after a certain date eg. dates later than 16-11-2013? This is because both the files are quite huge, and I need to compare the current dates only.

[snb]

amended code:


Sub M_snb()
sn = Sheets("New Report").Cells(1).CurrentRegion
sq = Sheets("Master").Cells(1).CurrentRegion

For j = 2 To UBound(sq)
c00 = c00 & vbLf & sq(j, 7) & "_" & sq(j, 11) & "_" & sq(j, 12)
Next

For j = 2 To UBound(sn)
If InStr(c00 & vbLf, vbLf & sn(j, 7) & "_" & sn(j, 11) & "_" & sn(j, 12) & vbLf) = 0 Then Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(sn, 2)) = Sheets("New Report").Cells(j, 1).Resize(, UBound(sn,2)).Value
Next
End Sub

I think you must be able to filter the records > 16-11-2013 and apply the previous code to those records.

[chlin]

Hi snb, i have tried filtering the dates and then running the macro. But it didn't work as the macro was still comparing all the data, not just the filtered data. Is there a way to incorporate the date into the macro or can it compare just the visible cells? Appreciate your help on this.

[chlin]

Hi snb, is it possible to amend the macro to compare dates after 16/11/2013? I am not sure how to filter the records so that the previous macro would work.