VBA Index match with two variables

isabelanger

New Member
Joined
Feb 27, 2023
Messages
3
Platform
  1. Windows
Hi,
I have a table that looks something like this:
IDLevel1Level2
1​
SizeTall
1​
WeightHeavy
1​
ColourBrown
2​
Sizeshort
2​
Weightlight
2​
Colourblue
3​
Sizemedium
3​
Weightlight
3​
Colourwhite

Where the same ID will appear in multiple rows but every time with a different tag on the next two columns.
I am trying to build a new table with only the sizes for every case.
For this example my original table is on cells a:c and i want to build the table on columns J:K
I have written the following code but it doesn't work:

Sub test()
Dim wsSheet1 As Worksheet
Set wsSheet1 = ThisWorkbook.Sheets("Sheet1")
Dim lastRow As Long
lastRow = wsSheet1.Cells(wsSheet1.Rows.Count, "J").End(xlUp).Row

Dim i As Long
For i = 1 To lastRow
wsSheet1.Cells(i, 10).Value = WorksheetFunction.Index(wsSheet1.Range("C:C"), WorksheetFunction.Match("Size" & wsSheet1.Range("B:B"), 0), WorksheetFunction.Match(wsSheet1.Cells(i, 10), wsSheet1.Range("a:a"), 0))
Next i

End Sub

I want to use Index-Match as my actual table is very big. Could anyone help me?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi and welcome to MrExcel.

I did not understand which columns you want as a result, so I give you 4 options:

Three result columns
VBA Code:
Sub Build_New_Table_V1()
'Three result columns
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 3)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = a(i, 3)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub


Two columns A and B
VBA Code:
Sub Build_New_Table_V2()
'Two columns A and B

  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub

Two columns A and C
VBA Code:
Sub Build_New_Table_V3()
'Two columns A and C

  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 3)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub

Two columns B and C
VBA Code:
Sub Build_New_Table_V4()
'Two columns B and C

  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 2)
      b(k, 2) = a(i, 3)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub
 
Upvote 0
Hi and welcome to MrExcel.

I did not understand which columns you want as a result, so I give you 4 options:

Three result columns
VBA Code:
Sub Build_New_Table_V1()
'Three result columns
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 3)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = a(i, 3)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub


Two columns A and B
VBA Code:
Sub Build_New_Table_V2()
'Two columns A and B

  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub

Two columns A and C
VBA Code:
Sub Build_New_Table_V3()
'Two columns A and C

  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 3)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub

Two columns B and C
VBA Code:
Sub Build_New_Table_V4()
'Two columns B and C

  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 2) = "Size" Then
      k = k + 1
      b(k, 1) = a(i, 2)
      b(k, 2) = a(i, 3)
    End If
  Next
 
  Range("J2").Resize(k, UBound(b, 2)).Value = b
End Sub
Thank you for getting back to me so fast.
I actually want to have a Code that performs this formula that I have on K2:


So that it looked for a match for the ID value and for Size, and returned the corresponding value of column C.
1677517848322.png

I will always have a the ID list of what I want to search on column J, and my reference table is from A to C; so the new table just needs to be completed.
Thanks!
 

Attachments

  • 1677517692909.png
    1677517692909.png
    23 KB · Views: 7
  • 1677517727497.png
    1677517727497.png
    15.9 KB · Views: 6
  • 1677517746205.png
    1677517746205.png
    22.8 KB · Views: 6
  • 1677517815991.png
    1677517815991.png
    17.1 KB · Views: 8
Upvote 0
I actually want to have a Code that performs this formula that I have on K2:
Here's a way to make the macro faster by using a dictionary and arrays:
VBA Code:
Sub Build_New_Table_V5()
  Dim a As Variant, b As Variant
  Dim i As Long, nRow As Long
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  b = Range("J2:K" & Range("J" & Rows.Count).End(xlUp).Row).Value
 
  For i = 1 To UBound(b)
    dic(b(i, 1)) = i
  Next
 
  For i = 1 To UBound(a)
    If dic.exists(a(i, 1)) Then
      If a(i, 2) = "Size" Then
        nRow = dic(a(i, 1))
        b(nRow, 2) = a(i, 3)
      End If
    End If
  Next
 
  Range("J2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
 
Upvote 0
Solution
Here's a way to make the macro faster by using a dictionary and arrays:
VBA Code:
Sub Build_New_Table_V5()
  Dim a As Variant, b As Variant
  Dim i As Long, nRow As Long
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
  b = Range("J2:K" & Range("J" & Rows.Count).End(xlUp).Row).Value
 
  For i = 1 To UBound(b)
    dic(b(i, 1)) = i
  Next
 
  For i = 1 To UBound(a)
    If dic.exists(a(i, 1)) Then
      If a(i, 2) = "Size" Then
        nRow = dic(a(i, 1))
        b(nRow, 2) = a(i, 3)
      End If
    End If
  Next
 
  Range("J2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
It worked thanks!
 
Upvote 1

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top