return missing values

Hi,

I have two worksheets every with two column.
In the first sheet in Column A are 4digit codes, in Column B should be filled in required information(missing indexes) .
Sheet 2 is based on report for these 4 digit codes(column A) with existing indexes for these codes.
The values for the indexes are between 1-9999 and they were not used sequentially.
For example for code 1685 we can see in sheet2 that used indexes are 1,2,5,30,998 so in sheet 1 should be returned 3,4,6.

Can you please suggest vba code that return unused indexes?

Regards,
Guerolito

You didn't mention how you want the result.

Sub test()
    Dim a, i As Long, e, s, dic As Object, msg As String
    Set dic = CreateObject("Scripting.Dictionary")
    With CreateObject("Scripting.Dictionary")
        For Each e In Array("Sheet2", "Sheet1")
            a = Sheets(e).Range("b2").CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If e = "Sheet2" Then
                    If Not .exists(a(i, 1)) Then
                        Set .Item(a(i, 1)) = _
                        CreateObject("Scripting.Dictionary")
                    End If
                    .Item(a(i, 1))(a(i, 2)) = Empty
                Else
                    If Not .exists(a(i, 1)) Then
                        If Not dic.exists(a(i, 1)) Then
                            Set dic(a(i, 1)) = _
                            CreateObject("Scripting.Dictionary")
                        End If
                        dic(a(i, 1))(a(i, 2)) = Empty
                    Else
                        If Not .Item(a(i, 1)).exists(a(i, 2)) Then
                            msg = msg & vbLf & a(i, 1) & " : " & a(i, 2)
                        End If
                    End If
                End If
            Next
        Next
    End With
    If dic.Count <> 0 Then
        For Each e In dic
            For Each s In dic(e)
                msg = msg & vbLf & e & " : " & s
            Next
        Next
    End If
    MsgBox msg, , "Missing index"
End Sub

Thank you Jindon,

The results should be returned in sheet1 column "Index".

Regards,
Guerolito

Can you show me how exactly you want the result?

Result should looks like as in attached example file - sheet1 column index.

Regards,
Gguerolito

You know the result in message box is exactly the same as what is in Sheet1.

So, no need to change Sheet1.

Is that what you want?

I think we are almost there but probably I didn’t explain correctly situation so I’ll try again.
In the example file I filled in Sheet1 “index” with the required indexes manually in order to explain requirements better, but normaly at the beginning I have only codes in Sheet1, index column is blank.
I filter codes using Advanced filter in order to get unique codes. Then I run report for this unique codes to find already used indexes. The result of report is in Sheet2.
And then I need VBA code to return in Sheet1 column “Index”
indexes which have not been used yet.

Regards,
Guerolito

What do you mean by

Originally Posted by Guerolito

indexes which have not been used yet.

indexes which have not been used yet = These are figures between 1-9999 which not exist in combination with respective Code in Sheet2

Regards,
Guerolito

You mean like this?

Option Explicit

Sub test()
    Dim a, i As Long, ii As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("sheet2").Range("b2").CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 1)) Then
            Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
        End If
        dic(a(i, 1))(a(i, 2)) = Empty
    Next
    With Sheets("sheet1").Range("b2").CurrentRegion
        With .Offset(1)
            .Columns(2).ClearContents
            a = .Value
            For i = 1 To UBound(a, 1)
                If dic.exists(a(i, 1)) Then
                    For ii = 1 To 9999
                        If Not dic(a(i, 1)).exists(ii) Then
                            a(i, 2) = ii: dic(a(i, 1))(ii) = Empty
                            Exit For
                        End If
                    Next
                End If
            Next
            .Value = a
        End With
    End With
    Sheets("sheet2").Range("b" & Rows.Count).End(xlUp)(2).Resize(UBound(a, 1), 2).Value = a
End Sub

Thank you jindon
It's very close to the solution.
Just one remark.
Is it possible not to change the returned indexes after every execution of code.
I mean if there is no changes in workbook to return the same indexes after every execution.

Regards,
Guerolito

Just delete one line before End Sub which is

Sheets("sheet2").Range("b" & Rows.Count).End(xlUp)(2).Resize(UBound(a, 1), 2).Value = a

PERFECT!
It seems that works as just like I want.
Now I should understand how the code works.
Again Thank you!
Guerolito