VBA, require for the remaining part list.

[motilulla]

Hello,

I got part list in column "A" starting from cell A3 down to A1001 max numbers are within 3 digits which is 999.

In the column "B" starting from cell B3 there is part sell entry, which could be with duplicate numbers and may be started from B3 and continue till B5005 (I mean it can be longer than part list)

I need VBA, which can give me the remaining part list in the column "C" starting from the cell C3

Part nº	Part Sell	Remaining
Part nº	Part Sell	Part List
1	1	22
17	17	68
22	65	72
65	1	75
68	259	86
72	17	98
75	999	100
86	90	107
90	137	124
98	265	138
100	65	139
107		140
124		145
137		152
138		157
139		210
140		242
145		249
152		251
157		257
210		267
242		268
249		269
251		300
257		301
259		402
265		407
267		408
268		420
269		590
300		790
301		820
402
407
408
420
590
790
820
999

For example the sample image is attached.

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[footoo]

There's some code to do what you want here : Compare Two Lists with VBA — Excel Dashboards VBA

 

[footoo]

Taken from the said site and adjusted to suit your data :

Sub Compare1()    'Excel VBA to compare 2 lists.
Dim ar As Variant
Dim var()
Dim i As Long
Dim n As Long
ar = [A3:B3].Resize(ActiveSheet.UsedRange.Rows.Count - 2)
ReDim var(1 To UBound(ar, 1), 1 To 1)
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(ar, 1)
        .Item(ar(i, 2)) = Empty
    Next
    For i = 1 To UBound(ar, 1)
        If Not .exists(ar(i, 1)) Then
            n = n + 1
            var(n, 1) = ar(i, 1)
        End If
    Next
End With
[C3].Resize(n).Value = var
End Sub

 

[motilulla]

Taken from the said site and adjusted to suit your data :

Sub Compare1()    'Excel VBA to compare 2 lists.
Dim ar As Variant
Dim var()
Dim i As Long
Dim n As Long
ar = [A3:B3].Resize(ActiveSheet.UsedRange.Rows.Count - 2)
ReDim var(1 To UBound(ar, 1), 1 To 1)
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(ar, 1)
        .Item(ar(i, 2)) = Empty
    Next
    For i = 1 To UBound(ar, 1)
        If Not .exists(ar(i, 1)) Then
            n = n + 1
            var(n, 1) = ar(i, 1)
        End If
    Next
End With
[C3].Resize(n).Value = var
End Sub

footoo, your VBA have solved my query. It worked fine.

Thank you very much for your help and time.

Have a great weekend

Kind Regards
Moti