VBA to delete cells ABC if cells AB are already in another array

[jbesclapez]

Hello there :-

I have a list in A B and C. Let's call this array ArrayOld
I have another list in F G and H. Let's call this ArrayNew
In ArrayNew, I can have data that are not in ArrayOld.

I would like to update the data in ABC with the ones in FBH. But beware of new data...
All cells are alphanumerical data.

Example :
The Table ArrayNew will update ArrayOLD in change the TASTE of "Dog Blue" and "Fish Blue" To "No" and it will add the "Shark Blue Yes"

TABLE ARRAYOLD

Animal	Color	Taste
Dog	Blue	Yes
Dog	White	Yes
Dog	Red	Yes
Fish	Blue	Yes
Fish	White	Yes
Fish	Red	Yes
Turtle	Blue	Yes
Turtle	White	Yes
Turtle	Red	Yes

<tbody>
</tbody>

TABLE ARRAYNEW

Animal	Color	Taste
Dog	Blue	No
Fish	Blue	No
Fish	Red	Yes
Turtle	Blue	Yes
Shark	Blue	Yes

<tbody>
</tbody>

RESULT EXPECTED

Animal	Color	Taste
Dog	Blue	No
Dog	White	Yes
Dog	Red	Yes
Fish	Blue	No
Fish	White	Yes
Fish	Red	Yes
Turtle	Blue	Yes
Turtle	White	Yes
Turtle	Red	Yes
Shark	Blue	Yes

<tbody>
</tbody>

Hope I am clear.

Thanks for your efforts.

 

[MickG]

Try this for Update in columns "A,B,C"

[COLOR="Navy"]Sub[/COLOR] MG15Nov37
[COLOR="Navy"]Dim[/COLOR] RngA [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt, RngF [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] RngA = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] RngF = Range("F1", Range("F" & Rows.Count).End(xlUp))
Ray = Array(RngA, RngF)
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
            ReDim nRay(1 To RngA.Count + RngF.Count, 1 To 3)
[COLOR="Navy"]For[/COLOR] Ac = 0 To 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Ray(Ac)
        Txt = Dn.Value & " " & Dn.Offset(, 1).Value
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            nRay(n, 1) = Dn.Value: nRay(n, 2) = Dn.Offset(, 1).Value: nRay(n, 3) = Dn.Offset(, 2).Value
            .Add Txt, n
        [COLOR="Navy"]Else[/COLOR]
            nRay(.Item(Txt), 3) = Dn.Offset(, 2).Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac
Range("A1").Resize(.Count, 3) = nRay
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jbesclapez]

Try this for Update in columns "A,B,C"

[COLOR=Navy]Sub[/COLOR] MG15Nov37
[COLOR=Navy]Dim[/COLOR] RngA [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Txt, RngF [COLOR=Navy]As[/COLOR] Range, Ray [COLOR=Navy]As[/COLOR] Variant, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] RngA = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]Set[/COLOR] RngF = Range("F1", Range("F" & Rows.Count).End(xlUp))
Ray = Array(RngA, RngF)
    [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
            ReDim nRay(1 To RngA.Count + RngF.Count, 1 To 3)
[COLOR=Navy]For[/COLOR] Ac = 0 To 1
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Ray(Ac)
        Txt = Dn.Value & " " & Dn.Offset(, 1).Value
        [COLOR=Navy]If[/COLOR] Not .Exists(Txt) [COLOR=Navy]Then[/COLOR]
            n = n + 1
            nRay(n, 1) = Dn.Value: nRay(n, 2) = Dn.Offset(, 1).Value: nRay(n, 3) = Dn.Offset(, 2).Value
            .Add Txt, n
        [COLOR=Navy]Else[/COLOR]
            nRay(.Item(Txt), 3) = Dn.Offset(, 2).Value
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]Next[/COLOR] Ac
Range("A1").Resize(.Count, 3) = nRay
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Mick. That is great.
I have double feelings : I am laughing from happiness from that but also I am soooo jealous of your skils.
Where do you find the info about the CreateObject scripting.dictionary. I could have never come up with that.
I was thinking of doing a vlookup and then Row to get the number of the row to delete... then copy paste back... but it was tricky and I was sure someone like you would have a better solution...

Thanks again.

 

[MickG]

You're very welcome , Glad its working for you.
If you feel the urge, have a look here !!
http://www.snb-vba.eu/VBA_Dictionary_en.html