Merging Files, Is This Possible

[rochdalemark]

Ok I don't know if this is possible and if it is, how would I go abou
it.

Lets say I have 2 excel files.
The first one has 4 columns:- REF, DESCRIPTION, PRICE, SIZE
The second has the same 4 columns but some content changes.
I would like to merge the newer file with the older one with th
folowing formula applied:-

1)If there is a row containing a REF that is in the old file but no
the new then that row should be deleted.
2)If there is a row containing a REF that is is the new file but no
the old, then that ro should be added.
3)I there is a row containing a REF in the old file and in the new fil
then the price cell needs to be copied from the new file.

If you can think of a way to do this or even a better option of doin
this then please let me know. The job in question is for when i ge
updated price lists from suppliers and I want to merge them into ou
guide, but you could be talking 1000's of items for each supplier, an
we generaly use our own descriptions so dont want those changing bac
to what the supplier has set theirs as.

Any help would be much apreciated

 

[Dave Peterson]

First, just a word of warning. If the customer can change
prices/descriptions/sizes, what stops them from changing the reference, too?

Maybe by mistake--an extra space/dash/comma?????

I don't think I'd delete the existing data without reviewing it first.

I think I'd put a few extra columns in the Old worksheet.

1. Type of change (add/change/delete)
2. Old value that changed
3. Time/date (just for logging purposes).

then I could apply data|filter|autofilter to that whole range and inspect what
changed. (I'd be hesitant to trust any output without double/triple checking!)

If this sounds ok, then try this macro against a copy of the old worksheet
(it'll be updated in place). Or don't save if it if it's wrong.

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim oldWks As Worksheet
Dim destCell As Range
Dim myCell As Range
Dim myOldRng As Range
Dim myNewRng As Range
Dim res As Variant

Set newWks = ActiveWorkbook.Worksheets("newsheet")
Set oldWks = ActiveWorkbook.Worksheets("oldsheet")

'headers in Row 1 of both sheets????
With oldWks
Set myOldRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With newWks
Set myNewRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Application
If .Evaluate(.Max(.CountIf(myNewRng, myNewRng))) > 1 _
Or .Evaluate(.Max(.CountIf(myOldRng, myOldRng))) > 1 Then
MsgBox "Please clean up duplicates!"
Exit Sub
End If
End With

For Each myCell In myOldRng.Cells
res = Application.Match(myCell.Value, myNewRng, 0)
If IsError(res) Then
myCell.Offset(0, 4).Value = "Deleted from New"
End If
Next myCell

For Each myCell In myNewRng.Cells
res = Application.Match(myCell.Value, myOldRng, 0)
If IsError(res) Then
With oldWks
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
destCell.Resize(1, 4).Value _
= myCell.Resize(1, 4).Value
destCell.Offset(0, 4).Value = "Added in New"
destCell.Offset(0, 6).Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
Else
Set destCell = myOldRng(res)
If destCell.Offset(0, 2).Value = myCell.Offset(0, 2).Value Then
'do nothing
Else
destCell.Offset(0, 5).Value = destCell.Offset(0, 2).Value
destCell.Offset(0, 2).Value = myCell.Offset(0, 2).Value
destCell.Offset(0, 4).Value = "Changed in New"
destCell.Offset(0, 6).Value _
= Format(Now, "mm/dd/yyyy hh:mm:ss")
End If
End If
Next myCell

End Sub

I assumed that the data started in A2 (headers in row 1).

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm