Delete the Value in Sheet 2 that does not exist in Sheet 1

Hi Everyone , I need your help again .
I have 2 Sheet , Sheet 1 and Sheet 2 and i want delete the value in the sheet2 are not exist in same row cell in Sheet1 For Example

Sheet 1 in A1 We have :

SHeet1 A1

Code:

---

1|1002|25|4|5|85|Apple|Banana

---

and in Sheet2 A1

Code:

---

25|60|90|40|1002|1|Banana

---

I need This result in Sheet 2 :

Code:

---

25|1002|1|Banana

---

I attached The sample Also .

Really appreciate for your time and effort.

Please help me if you . :crying:

Try this

I added a small test sample since your sample data was too similar and it seems to work

Code:

---

Option Explicit
Sub Something()
    Dim wsMain As Worksheet, wsClean As Worksheet
    Dim aryMain As Variant, aryClean As Variant
   
    Dim i As Long, j As Long
    Dim aryPieces As Variant
   
    Set wsMain = Worksheets("Sheet1")
    aryMain = wsMain.Cells(1, 1).CurrentRegion.Value
    Set wsClean = Worksheets("Sheet2")
    aryClean = wsClean.Cells(1, 1).CurrentRegion.Value
       
    For i = LBound(aryMain, 1) To UBound(aryMain, 1)
        aryMain(i, 1) = aryMain(i, 1) & "|"
        aryClean(i, 1) = aryClean(i, 1) & "|"
    Next i
       
    For i = LBound(aryMain, 1) To UBound(aryMain, 1)
       
        aryPieces = Split(aryMain(i, 1), "|")
   
        For j = LBound(aryPieces) To UBound(aryPieces) - 1  '  get extra because added | above
            aryClean(i, 1) = Replace(aryClean(i, 1), aryPieces(j) & "|", vbNullString)
        Next j
   
        aryClean(i, 1) = Left(aryClean(i, 1), Len(aryClean(i, 1)) - 1)
   
    Next i
   
    wsClean.Cells(1, 1).CurrentRegion.Value = aryClean
End Sub

---

Dear Paul_Hossler Appreciate for your help but when run the code it does not not delete in sheet2 , i tested in my Sample that attached in first post.

Thanks for your help and time and effort .

I tested in your sample and it seems to delete, at least based on my understanding of what you wanted



I only looked at the results for Row1

Sheet2 A1 BEFORE has length 949


Attachment 22947

Sheet2 A1 AFTER has length 852

Attachment 22946


The formula bar clearly shows differences




A detailed breakdown shows the deleted pieces in the AFTER which correspond to pieces in Sheet1, Row 1


Attachment 22948


So I guess I don't see what's not working

Provide a specific example and I'll look again

Thanks for explain but i think you do not understand my Mean
In Sheet1 in A2 We have A25G Volvo|A25F Volvo and when run The VBA in Sheet2 , the result in Sheet2 A2 : A25F Volvo |A25G Volvo
Mean it must delete the value that does not exist in sheet 1 , So in Sheet 2 we will see the same value that we have in sheet1 but with different sort .

Hope you understand .

I understand a little better the deleted. So …


Sheet1 A2 = "A25G Volvo|A25F Volvo"

Before:

Sheet2 A2 = "860 Volvo BM|861 Volvo BM|5350 Volvo BM|5350B Volvo BM|5350B 4x4 Volvo BM|5350B 6x4 Volvo BM|A20 Volvo BM|A20 6x4 Volvo BM|A20C Volvo BM|A20C Volvo|A25 Volvo BM|A25 4x4 Volvo BM|A25 6x4 Volvo BM|A25B Volvo BM|A25B 4x4 Volvo BM|A25C Volvo BM|A25C Volvo|A25C 4x4 Volvo|A25C 4x4 Volvo BM|A25D Volvo|A25D 4x4 Volvo|A25E Volvo|A25E 4x4 Volvo|A25F Volvo SN 13001-99999|A25F Volvo SN 320001-|A25G Volvo|A30 Volvo BM|A30C Volvo|A30C Volvo BM|A30D Volvo|A30E Volvo|A30F Volvo SN 12001-99999"


After:

Sheet2 A2 = "A25G Volvo"

since "A25G Volvo" is the only piece in Sheet2 A2

Quote:

---

the result in Sheet2 A2 : A25F Volvo |A25G Volvo

---

There is no exact match for "A25F Volvo" in Sheet2 A2, but there are these:

"

A25F Volvo SN 13001-99999|A25F Volvo SN 320001-"

Matching SN's is difficult



I don't understand the "with different sort"

Do you want the pieces in each cell in Sheet2 sorted Low-High?


Try this which seems to do the exact matching and deletes

Code:

---

Option Explicit
Sub Something3()
    Dim wsMain As Worksheet, wsClean As Worksheet
    Dim aryMain As Variant, aryClean As Variant
   
    Dim i As Long, j As Long
    Dim aryPieces As Variant
   
    Set wsMain = Worksheets("Sheet1")
    aryMain = wsMain.Cells(1, 1).CurrentRegion.Value
    Set wsClean = Worksheets("Sheet2")
    aryClean = wsClean.Cells(1, 1).CurrentRegion.Value
       
    For i = LBound(aryMain, 1) To UBound(aryMain, 1)
        aryMain(i, 1) = aryMain(i, 1) & "|"
        aryClean(i, 1) = aryClean(i, 1) & "|"
    Next i
       
    'In Sheet1 in A2 We have A25G Volvo|A25F Volvo and when run The VBA in Sheet2,
    'the result in Sheet2 A2 : A25F Volvo |A25G Volvo
    'Mean it must delete the value that does not exist in sheet 1,
    'So in Sheet 2 we will see the same value that we have in sheet1 but with different sort .

    For i = LBound(aryMain, 1) To UBound(aryMain, 1)
       
        aryPieces = Split(aryClean(i, 1), "|")
        For j = LBound(aryPieces) To UBound(aryPieces) - 1  '  get extra because added | above
            If InStr(aryMain(i, 1), aryPieces(j)) = 0 Then aryPieces(j) = vbNullString
        Next j
   
        aryClean(i, 1) = Join(aryPieces, "|")
       
        Do While InStr(aryClean(i, 1), "||") > 0
            aryClean(i, 1) = Replace(aryClean(i, 1), "||", "|")
        Loop
       
        If Right(aryClean(i, 1), 1) = "|" Then
            aryClean(i, 1) = Left(aryClean(i, 1), Len(aryClean(i, 1)) - 1)
        End If
        If Left(aryClean(i, 1), 1) = "|" Then
            aryClean(i, 1) = Right(aryClean(i, 1), Len(aryClean(i, 1)) - 1)
        End If
   
    Next i
   
    wsClean.Cells(1, 1).CurrentRegion.Value = aryClean
End Sub

---

Man You are Great This is the VBA i need . You are excellent and really appreciate got your great work and time and effort . . You saved me

Dear Paul_Hossler just there is a small problem :
In Sheet1 I have
A25C Volvo BM|A25C 4x4 Volvo BM


in Sheet2
A25C Volvo BM|A25C Volvo|A25C 4x4 Volvo|A25C 4x4 Volvo BM

The result must be in Sheet2

A25C Volvo BM|A25C 4x4 Volvo BM

but now it will
A25C Volvo BM|A25C Volvo|A25C 4x4 Volvo|A25C 4x4 Volvo BM


I want the exact mean
A25C Volvo BM|A25C 4x4 Volvo BM

A25C 4x4 Volvo and A25C 4x4 Volvo BM are different


Thank you again for all your help.

I think this is it

Code:

---

Option Explicit
Sub Something4()
    Dim wsMain As Worksheet, wsClean As Worksheet
    Dim aryMain As Variant, aryClean As Variant, aryMainPieces As Variant
    Dim sClean As String
   
    Dim i As Long, j As Long
   
    Set wsMain = Worksheets("Sheet1")
    aryMain = wsMain.Cells(1, 1).CurrentRegion.Value
    Set wsClean = Worksheets("Sheet2")
    aryClean = wsClean.Cells(1, 1).CurrentRegion.Value
       
    For i = LBound(aryMain, 1) To UBound(aryMain, 1)
        aryMain(i, 1) = aryMain(i, 1) & "|"
        aryClean(i, 1) = aryClean(i, 1) & "|"
    Next i
       
    'In Sheet1 in A2 We have A25G Volvo|A25F Volvo and when run The VBA in Sheet2,
    'the result in Sheet2 A2 : A25F Volvo |A25G Volvo
    'Mean it must delete the value that does not exist in sheet 1,
    'So in Sheet 2 we will see the same value that we have in sheet1 but with different sort .
    For i = LBound(aryMain, 1) To UBound(aryMain, 1)
       
        sClean = vbNullString
        aryMainPieces = Split(aryMain(i, 1), "|")
       
        For j = LBound(aryMainPieces) To UBound(aryMainPieces) - 1  '  get extra because added | above
            If InStr(aryClean(i, 1), aryMainPieces(j) & "|") > 0 Then
                sClean = sClean & aryMainPieces(j) & "|"
            End If
        Next j
   
        aryClean(i, 1) = sClean
       
        If Right(aryClean(i, 1), 1) = "|" Then
            aryClean(i, 1) = Left(aryClean(i, 1), Len(aryClean(i, 1)) - 1)
        End If
       
    Next i
   
    wsClean.Cells(1, 1).CurrentRegion.Value = aryClean
End Sub

---

Please check the Sample File , The result of sheet2 me must be same as sheet3.
In your code it will change the type of sort , it must only delete …

Sheet1

Code:

---

A25B 4x4 Volvo BM|A25B Volvo BM

---

Sheet2

Code:

---

860 Volvo BM|861 Volvo BM|5350 Volvo BM|5350B Volvo BM|5350B 4x4 Volvo BM|5350B 6x4 Volvo BM|A20 Volvo BM|A20 6x4 Volvo BM|A20C Volvo BM|A20C Volvo|A25 Volvo BM|A25 4x4 Volvo BM|A25 6x4 Volvo BM|A25B Volvo BM|A25B 4x4 Volvo BM|A25C Volvo BM|A25C Volvo|A25C 4x4 Volvo|A25C 4x4 Volvo BM|A25D Volvo|A25D 4x4 Volvo|A25E Volvo|A25E 4x4 Volvo|A25F Volvo|A25G Volvo|A30 Volvo BM|A30C Volvo|A30C Volvo BM|A30D Volvo|A30E Volvo|A30F Volvo|A30G Volvo|A35 Volvo BM|A35C Volvo|A35C Volvo BM|A35D Volvo|A35E Volvo|A35E FS Volvo|A35F Volvo|A35F FS Volvo|A35G Volvo|A35G FS Volvo|A40 Volvo BM|A40 Volvo|A40D Volvo|A40E Volvo|A40E FS Volvo|A40F Volvo|A40F FS Volvo|A40G Volvo|A40G FS Volvo|A45G Volvo|A45G FS Volvo|A60H Volvo|T450D Volvo

---

The Result must be

Code:

---

A25B Volvo BM|A25B 4x4 Volvo BM

---

But in your code the result is

Code:

---

A25B 4x4 Volvo BM|A25B Volvo BM

---

If you see in sheet2 first is A25B Volvo BM and after it A25B 4x4 Volvo BM

Sorry, I'm confused

Using Sample4 above, this is Sheet1

Attachment 23015


This is Sheet2 before the macro

Attachment 23016


And this is Sheet2 after the macro


Attachment 23017


What should it be?

Please download my previous sample and run your VBA code . and compare with sheet3 and you will see the different .
Really thanks for your help and effort .

You didn't say that you wanted the pieces sorted in each cell

This is the result of sorting using the normal collating sequence which puts numbers (4X4 Volvo BM) ahead of letters (Volvo BM)

Attachment 23020

It's doable to sort other ways, but a whole lot of work. I'll think about it