View Full Version : [SOLVED:] Delete the Value in Sheet 2 that does not exist in Sheet 1
parscon
09-23-2018, 02:56 AM
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.
parscon
09-27-2018, 01:17 PM
Please help me if you . :crying:
Paul_Hossler
09-28-2018, 07:23 PM
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
parscon
09-29-2018, 06:37 AM
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 .
Paul_Hossler
09-29-2018, 08:05 AM
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
22947
Sheet2 A1 AFTER has length 852
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
22948
So I guess I don't see what's not working
Provide a specific example and I'll look again
parscon
09-29-2018, 08:15 AM
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 .
Paul_Hossler
09-29-2018, 09:04 AM
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
parscon
09-30-2018, 12:33 AM
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
parscon
10-12-2018, 12:00 AM
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.
Paul_Hossler
10-12-2018, 11:45 AM
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
parscon
10-13-2018, 12:02 AM
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
Paul_Hossler
10-13-2018, 06:10 AM
Sorry, I'm confused
Using Sample4 above, this is Sheet1
23015
This is Sheet2 before the macro
23016
And this is Sheet2 after the macro
23017
What should it be?
parscon
10-13-2018, 06:15 AM
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 .
Paul_Hossler
10-13-2018, 02:56 PM
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)
23020
It's doable to sort other ways, but a whole lot of work. I'll think about it
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.