Compare 2 excel sheets 1

[sineados]

Hi,

I get an excel file everymonth which is cumulative so it contains the previous months details plus this months. Is there any way I can compare the two files so that I can then delete the ones I have previously dealt with. It has to be a cumulative file because its a 3rd party product.

Its really only this months I am interested in but can't delete incase something slipped in and if I delete the ones before I might miss it.

thanks,

 

[SkipVought]

Hi,
What's the data definition?

Do you have a Date field to key off?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Adnane]

i guess that all the monthly files have the same format and columns so why not to try to find an ID column and vlookup the recent value in the old ones. the unmatched ones will be you current month data (or the most recent)

hope this helps

 

[SkipVought]

You're not really telling me very much. I would have HOPED that all the files have the same format.

That's NOT the questions I asked!

What are the Key Data elements?

Is/Are there date field(s)?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mwapnitsky]

I use the following code. Let me know if it helps.

Sub Highlight_Differences()
'
' Highlight_Differences Macro

'
' Keyboard Shortcut: Ctrl+h
'
Message = "This macro will compare two worksheets. It will create a new " & _
"worksheet called ""Diffs"". " & Chr(10) & _
"If a worksheet called ""Diffs"" already exists, you must delete or rename it."

ans = MsgBox(Message, vbOKCancel)
If ans = 2 Then End 'cancel was issued
base = InputBox("Enter the name of the BASE worksheet.&quot
new1 = InputBox("Enter the name of the NEW worksheet.&quot

Application.ScreenUpdating = False
Sheets(new1).Select
Sheets(new1).Copy Before:=Sheets(1)
Sheets(new1 & " (2)&quot.Select
Sheets(new1 & " (2)&quot.Name = "Diffs"
ec = ActiveCell.SpecialCells(xlLastCell).Address
For Each xcell In Range("a1", ec)
r = xcell.Address
Range(r).Select
Sheets(base).Select
b = Range(r).Value
Sheets("Diffs&quot.Select
d = Range(r).Value
If b <> d Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

End If

 

[sineados]

I tried the code and it seems to work fine. I need to get them in to the same order though but its certainly a step forward.

Its an invoice file so the identifying field is the invoice number. I can see that there are 2 new invoices for this month but there might be new items for some previous invoices which I need to look at as well. Our bill cycle overlaps so I don't want to thrall through all of June/July and August in September if you follow me.

i was hoping to highlight the differences so I could see at a glance any changes

thanks,