Excel macro to find all that match key not just the first when looking for differences

[starheartbeam]

I am trying to compare two file. I receive a file once a week. And I need to compare last week’s file to this week’s file. I have them both in the same file on two different tabs. One called Old and the other is called New. I need to look at these two file and see where the Location has changed on the New file and update it on a new tab called New_Location. This needs to be done in a macro as I have this file looking for other changes and all of those are working. I have created a key on both Old and New tab using the Item – State Date. This works great to give me the new locations if an item is mono sourced but if it is multi sourced I am getting items showing up that have not changed because it is whatever it gets to first in the file. Is there a way to code the macro to look at all the lines that fit the key and not just the first one it comes to?
Old

Item	Location	Start Date	Source	FileDate
1234	US	01-APR	MULTI	22-FEB
1234	FR	01-APR	MULTI	22-FEB
9876	CA	01-MAY	MONO	22-FEB
8525	CA	01-JUN	MULTI	22-FEB
8525	US	01-JUN	MULTI	22-FEB
8525	US	01-MAY	MULTI	22-FEB
8525	CA	01-MAY	MULTI	22-FEB

<tbody>
</tbody>

New

Item	Location	Start Date	Source	FileDate
1234	CA	01-APR	MULTI	28-FEB
1234	FR	01-APR	MULTI	28-FEB
9876	US	01-MAY	MONO	28-FEB
8525	CA	01-JUN	MULTI	28-FEB
8525	US	01-JUN	MULTI	28-FEB
8525	US	01-MAY	MULTI	28-FEB
8525	FR	01-MAY	MULTI	28-FEB

<tbody>
</tbody>

Here is what the results should show.
New_Location

Item	Location	Start Date	Source	FileDate
1234	CA	01-APR	MULTI	28-FEB
9876	US	01-MAY	MONO	28-FEB
8525	FR	01-MAY	MULTI	28-FEB

<tbody>
</tbody>

 

[MickG]

Try this for "Old" on sheet1, "New" on sheet2, results on sheet3.

[COLOR="Navy"]Sub[/COLOR] MG06Mar53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
         Txt = Join(Application.Index(Dn.Resize(, 3).Value, 0, Array(1, 2, 3)), ",")
        .Item(Txt) = Empty
    [COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
ReDim Ray(1 To Rng.Count, 1 To 5)
Ray(1, 1) = "Item": Ray(1, 2) = "Location": Ray(1, 3) = "Start Date": Ray(1, 4) = "Source": Ray(1, 5) = "File Date"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
         Txt = Join(Application.Index(Dn.Resize(, 3).Value, 0, Array(1, 2, 3)), ",")
            [COLOR="Navy"]If[/COLOR] Not .exists(Txt) [COLOR="Navy"]Then[/COLOR]
              c = c + 1
                [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
                    Ray(c, Ac) = Dn.Offset(, Ac - 1).Value
                [COLOR="Navy"]Next[/COLOR] Ac
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Sheets("Sheet3").Range("A1").Resize(c, 5)
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick