isabelanger
New Member
- Joined
- Feb 27, 2023
- Messages
- 3
- Platform
- Windows
Hi,
I have a table that looks something like this:
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?
I have a table that looks something like this:
ID | Level1 | Level2 |
1 | Size | Tall |
1 | Weight | Heavy |
1 | Colour | Brown |
2 | Size | short |
2 | Weight | light |
2 | Colour | blue |
3 | Size | medium |
3 | Weight | light |
3 | Colour | white |
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?