Index/Match formula to VBA

[Drawleeh]

I have a spreadsheet that I have set up to work with formulas currently but I would like some help creating it within VBA.

I have column A and B which are entry columns, one contains a number the other contains a country ISO code. Then in a separate column I concatenate these 2 columns into one column via =A2&" "&B2

Then I use a INDEX/MATCH formula =IFERROR(INDEX($U$2:$U$249, MATCH(0, INDEX(COUNTIF($I$1:I1, $U$2:$U$249), 0, 0), 0)), "") to get a list of unique results in a third Column indexed from the concatenated list.

How do I go about changing this to VBA? Any help would be appreciated.

 

[Drawleeh]

Inserting an imagine to clarify the above.

 

[lrobbo314]

Either of these should work.

Book1 (version 2).xlsb
	A	B	C	D
1	Code	Origin	Concatenate	Unique Code
2	32131000	GB	32131000 GB	32131000 GB
3	96082000	GB	96082000 GB	96082000 GB
4	86082000	GB	86082000 GB	86082000 GB
5	96081010	JP	96081010 JP	96081010 JP
6	48025890	JP	48025890 JP	48025890 JP
7	96082000	JP	96082000 JP	96082000 JP
8	32131000	JP	32131000 JP	32131000 JP
9	96082000	GB	96082000 GB	96081010 GB
10	96082000	GB	96082000 GB
11	96081010	GB	96081010 GB
12	48025890	JP	48025890 JP
13	96082000	JP	96082000 JP
Sheet4

Cell Formulas
Range	Formula
C2:C13	C2	=A2&" "&B2

Sub UNIK()
Dim r As Range:         Set r = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(AR)
        If Not .exists(AR(i, 1)) Then .Add AR(i, 1), 1
    Next i
    Debug.Print .Count
    r.Offset(, 1).Resize(.Count).Value2 = Application.Transpose(.keys)
End With
End Sub

Sub IM()
Dim r As Range:     Set r = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)

With r.Offset(, 1)
    .Formula = "=IFERROR(INDEX(" & r.Address & ", MATCH(0, INDEX(COUNTIF($D$1:D1, " & r.Address & "), 0, 0), 0)), """")"
    .Value2 = .Value2
End With
End Sub

 

[Drawleeh]

Either of these should work.

Book1 (version 2).xlsb
	A	B	C	D
1	Code	Origin	Concatenate	Unique Code
2	32131000	GB	32131000 GB	32131000 GB
3	96082000	GB	96082000 GB	96082000 GB
4	86082000	GB	86082000 GB	86082000 GB
5	96081010	JP	96081010 JP	96081010 JP
6	48025890	JP	48025890 JP	48025890 JP
7	96082000	JP	96082000 JP	96082000 JP
8	32131000	JP	32131000 JP	32131000 JP
9	96082000	GB	96082000 GB	96081010 GB
10	96082000	GB	96082000 GB
11	96081010	GB	96081010 GB
12	48025890	JP	48025890 JP
13	96082000	JP	96082000 JP
Sheet4

Cell Formulas
Range	Formula
C2:C13	C2	=A2&" "&B2

Sub UNIK()
Dim r As Range:         Set r = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(AR)
        If Not .exists(AR(i, 1)) Then .Add AR(i, 1), 1
    Next i
    Debug.Print .Count
    r.Offset(, 1).Resize(.Count).Value2 = Application.Transpose(.keys)
End With
End Sub

Sub IM()
Dim r As Range:     Set r = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)

With r.Offset(, 1)
    .Formula = "=IFERROR(INDEX(" & r.Address & ", MATCH(0, INDEX(COUNTIF($D$1:D1, " & r.Address & "), 0, 0), 0)), """")"
    .Value2 = .Value2
End With
End Sub

They both do provide a index/match outcome however would it be possible to reduce the number of columns and remove column C, so the concatenation of column A and B occurs only within the code and that data is then index/matched?

 

[Fluff]

How about

Sub Drawleeh()
   Dim Ary As Variant
   Dim r As Long
   
   Ary = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         .Item(Ary(r, 1) & " " & Ary(r, 2)) = Empty
      Next r
      Range("C2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub