Consulting

Results 1 to 14 of 14

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

  1. #1
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location

    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
    1|1002|25|4|5|85|Apple|Banana
    and in Sheet2 A1
    25|60|90|40|1002|1|Banana
    I need This result in Sheet 2 :

    25|1002|1|Banana
    I attached The sample Also .

    Really appreciate for your time and effort.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    Please help me if you .

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Try this

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


    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    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 .

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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


    Before.JPG




    Sheet2 A1 AFTER has length 852


    After.JPG


    The formula bar clearly shows differences




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


    Compare.JPG


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

    Provide a specific example and I'll look again
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    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 .

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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



    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



    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    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

  9. #9
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    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.

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think this is it

    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    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
    A25B 4x4 Volvo BM|A25B Volvo BM
    Sheet2
    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
    A25B Volvo BM|A25B 4x4 Volvo BM
    But in your code the result is

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

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Sorry, I'm confused

    Using Sample4 above, this is Sheet1

    Sheet1.JPG


    This is Sheet2 before the macro

    Sheet2Before.JPG


    And this is Sheet2 after the macro


    Sheet2Aftere.JPG


    What should it be?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    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 .

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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)

    Capture.JPG

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •