PDA

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