Comparing data in two excel workbooks and creating a new file removing duplicates.

[andy.bentley]

Hi,

Have been asked by a friend to look at this and I’m struggling. They have an 'invoice statement' with invoice numbers (column A) and a 'invoice paid' again with invoice numbers (column B). I want to have a separate workbook 'Statement II' which will be an update on 'invoice statement' but with rows removed where they appear in 'invoice paid'.

Is this a case of using a macro or is there any other simple way?

Thanks in advance.

 

[JLGWhiz]

If you mean that they want a workbook of only the open invoices, it could be done by VBA.

 

[andy.bentley]

Thank you JLGWhiz

How would I go about this?

 

[JLGWhiz]

Thank you JLGWhiz

How would I go about this?

do they really want a new workbook, or just a different worksheet? I have no idea what volume of data is being addressed, but it would seem that a different sheet in the same workbook would be more convenient than having to open a different workbook to check open invoices.

 

[andy.bentley]

Well i'm sure the statement data could be written to a new sheet, however the information duplicated (to be removed from the original statement excel woorkbook) would be in a seperate workbook. An open invoice sheet wouldn't be a problem.

Thanks again

 

[JLGWhiz]

Well i'm sure the statement data could be written to a new sheet, however the information duplicated (to be removed from the original statement excel woorkbook) would be in a seperate workbook. An open invoice sheet wouldn't be a problem.

Thanks again

I don't understand the "Rows removed". What columns of data from "Invoice Statement" do you want to appear in the new sheet? A,B,C etc.

 

[andy.bentley]

I don't understand the "Rows removed". What columns of data from "Invoice Statement" do you want to appear in the new sheet? A,B,C etc.

Well the statement basically needs duplicating but with all the rows of data removed which appear in the paid workbook (its a very weird system that he has going) personally i would have it all in once workbook, multiple sheets if needed but still.

I just need to search the 'statement file' for any invoice numbers that are also in the 'paid file' then creat a new sheet (or workbook if easier) to show any open or outstanding invoices, so the ideal output is a shortened version of the original statement but without having to manually go through potentially 8,000 rows and comparing them

Appreciate your time and help here.

 

[JLGWhiz]

This will require a third sheet to be named "Open Invoices". It compares the invoice numbers in Col A of the statement to the ones in Paid and if not fount in paid, it adds to sheet 3 Open.

Code:
Sub lime()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, rng As Range, lr1 As Long, lr2 As Long
Set sh1 = Sheets("Invoice Statement")
Set sh2 = Sheets("Invoice Paid")
Set sh3 = Sheets("Open Invoice")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr1)
x = 2
For Each c In rng
If WorksheetFunction.CountIf(Range("B2:B" & lr2), c.Value) = 0 Then
c.EntireRow.Copy sh3.Range("A" & x)
x = x + 1
End If
Next
End Sub
Code:

 

[andy.bentley]

This will require a third sheet to be named "Open Invoices". It compares the invoice numbers in Col A of the statement to the ones in Paid and if not fount in paid, it adds to sheet 3 Open.

Code:
Sub lime()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, rng As Range, lr1 As Long, lr2 As Long
Set sh1 = Sheets("Invoice Statement")
Set sh2 = Sheets("Invoice Paid")
Set sh3 = Sheets("Open Invoice")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr1)
x = 2
For Each c In rng
If WorksheetFunction.CountIf(Range("B2:B" & lr2), c.Value) = 0 Then
c.EntireRow.Copy sh3.Range("A" & x)
x = x + 1
End If
Next
End Sub
Code:

Thank you for this, i'm getting my friend to resend me the sheets with the invoice number and other details in the same columns on both. Do i need to create sheet 3 or will the macro / code do this.... also i'm using excel 2007, if its a macro, where is this command hiding these days????

Thanks for all your help today.

 

[andy.bentley]

Ok the code you supplied worked however i've noticed that the paid file has the invoice number in multiple times as the payments are being seperated adding tax seperately. It seems the formula ignors everything becuase there is more than one payment against each invoice.