INDEX MATCH multiple values in same row

[surkdidat]

I need to match A2 and D2 (in a sheet called "5.12") - against Sheet1, (A2 against Column B, and D against Column F)

This will go down for several hundred lines.

I appreciate the easy solution would be to add a column combing the two and then matching that, but the sheet directly gets pulled into an Access database so I cannot fiddle with the format

So in effect (in real terms) I will have a Category Number (for example 12345) which will have 20 or so lines of data in the Master Sheet, one for each specific, (Colour, Weight, Size etc)

If I use Index Match one one value it obviously only picks up the first match. I have tried to do INDEX MATCH MATCH on 2 columns, on 2 values in the same row, but it does not seem to like it.

Any help appreciated!

(For reference I have tried

=INDEX(RawData!I:I,MATCH(A2,RawData!B:B,0),MATCH(D2,RawData!F:F,0))

SHEET 5.12	Column A - Batch	Column B - Cat_Number	Column D - Cat_Detail	Column E (Result of INDEX MATCH)
Row 1	1000	12345	Colour	Blue
Row 2	1000	12345	Weight	1.5
Row 3	1000	12345	Shape	Square
Row 4	1000	23456	Colour	White
Row 5	1000	23456	Weight	2.0
Row 6	1000	23456	Shape	Round

RawData	Column A	Column B - Category No	Column F - Cat Details	Column I - (Data that needs to be pulled from Index Match)
Row 1		12345	Colour	Blue
Row 2		12345	Shape	Square
Row 3		34564	Colour	Purple
Row 4		12345	Weight	1.5
Row 5		23456	Colour	White
Row 6		23456	Weight	2.0
Row 7		34564	Weight	4.0
Row 8		23456	Shape	Round
Row 9		34564	Shape	Triangle

 

[Marcelo Branco]

The main data may not be populated in order, so would need the macro to run to the last row every time, and not stop on the first row/cell that is empty.

Are there blank rows in RawData sheet and/or the destination sheets?

M.

 

[Fluff]

How about

Sub surkdidat()
   Dim Ary As Variant
   Dim Dic As Object
   Dim r As Long, c As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      Ary = .Range("B2:M" & .Range("B" & Rows.Count).End(xlUp).Row)
   End With
   For r = 1 To UBound(Ary)
      If Ary(r, 1) <> "" Then
         Dic(Ary(r, 1) & "|" & Ary(r, 5)) = Application.Index(Ary, r, Array(7, 8, 9, 10, 11))
      End If
   Next r
   With Sheets("Sheet2")
      Ary = .Range("A2:K" & .Range("A" & Rows.Count).End(xlUp).Row)
   End With
   For r = 1 To UBound(Ary)
      If Dic.Exists(Ary(r, 1) & "|" & Ary(r, 4)) Then
         For c = 1 To 5
            Ary(r, c + 6) = Dic(Ary(r, 1) & "|" & Ary(r, 4))(c)
         Next c
      End If
   Next r
   Sheets("Sheet2").Range("A2").Resize(UBound(Ary), 11).Value = Ary
End Sub

Change sheet names to suit.

 

[Marcelo Branco]

Just to test performance with only one target sheet - sheet 5.12 in this case (not sure about performance with more than 400k rows - not tested with as many rows)

Assuming...

RawData

08122021 Testes.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1	Category No				Cat Details			Data_I	Data_J	Data_K	Data_L	Data_M
2	12345				Colour			Blue	jjj1	kkk1	lll1	mmm1
3	12345				Shape			Square	jjj2	kkk2	lll2	mmm2
4	34564				Colour			Purple	jjj3	kkk3	lll3	mmm3
5
6	12345				Weight			1.5	jjj4	kkk4	lll4	mmm4
7	23456				Colour			White	jjj5	kkk5	lll5	mmm5
8
9
10	23456				Weight			2.0	jjj6	kkk6	lll6	mmm6
11	34564				Weight			4.0	jjj7	kkk7	lll7	mmm7
12	23456				Shape			Round	jjj8	kkk8	lll8	mmm8
13	34564				Shape			Triangle	jjj9	kkk9	lll9	mmm9
14
RawData

Sheet 5.12 results in columns G:K

08122021 Testes.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Batch	Cat_Number		Cat_Detail			Col_I	Col_J	COL_K	Col_L	Col_M
2	1000	12345		Colour			Blue	jjj1	kkk1	lll1	mmm1
3
4	1000	12345		Weight			1.5	jjj4	kkk4	lll4	mmm4
5	1000	12345		Shape			Square	jjj2	kkk2	lll2	mmm2
6
7
8	1000	23456		Colour			White	jjj5	kkk5	lll5	mmm5
9	1000	23456		Weight			2.0	jjj6	kkk6	lll6	mmm6
10	1000	23456		Shape			Round	jjj8	kkk8	lll8	mmm8
5.12

Macro

Sub aTestV2()
    Dim dic As Object
    Dim lr As Long, vData As Variant, i As Long
    Dim vResult As Variant
   
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
   
    'Activate RawData
    Sheets("RawData").Activate
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    vData = Range("B2:M" & lr).Value
 
    'Store data in the dictionary
    For i = 1 To UBound(vData, 1)
        If vData(i, 1) <> "" Then
            dic(vData(i, 1) & "|" & vData(i, 5)) = _
                Array(vData(i, 8), vData(i, 9), vData(i, 10), vData(i, 11), vData(i, 12))
        End If
    Next i
   
    'Activate 5.12
    Sheets("5.12").Activate
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    vData = Range("B2:D" & lr).Value
    vResult = Range("G2:K" & lr)
   
    'Get the results from the dictionary
    For i = 1 To UBound(vData, 1)
        If vData(i, 1) <> "" Then
            vResult(i, 1) = dic(vData(i, 1) & "|" & vData(i, 3))(0)
            vResult(i, 2) = dic(vData(i, 1) & "|" & vData(i, 3))(1)
            vResult(i, 3) = dic(vData(i, 1) & "|" & vData(i, 3))(2)
            vResult(i, 4) = dic(vData(i, 1) & "|" & vData(i, 3))(3)
            vResult(i, 5) = dic(vData(i, 1) & "|" & vData(i, 3))(4)
        End If
    Next i
    'Tranfer to columns G:K
    With Range("G2:K" & lr)
        .NumberFormat = "@"
        .Value = vResult
    End With
End Sub

Hope this helps

M.

 

[Fluff]

I would advise against trying my code, if it's not too late, I don't think it likes the use of Index, as it's taking way too long.

 

[Fluff]

This is a lot faster than my original code.

Sub surkdidat()
   Dim Ary As Variant
   Dim Dic As Object
   Dim r As Long, c As Long
 
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      Ary = .Range("B2:M" & .Range("B" & Rows.Count).End(xlUp).Row)
   End With
   For r = 1 To UBound(Ary)
      If Ary(r, 1) <> "" Then
         Dic(Ary(r, 1) & "|" & Ary(r, 5)) = Array(Ary(r, 7), Ary(r, 8), Ary(r, 9), Ary(r, 10), Ary(r, 11))
      End If
   Next r
   With Sheets("Sheet2")
      Ary = .Range("A2:K" & .Range("A" & Rows.Count).End(xlUp).Row)
   End With
   For r = 1 To UBound(Ary)
      If Dic.Exists(Ary(r, 1) & "|" & Ary(r, 4)) Then
         For c = 0 To 4
            Ary(r, c + 7) = Dic(Ary(r, 1) & "|" & Ary(r, 4))(c)
         Next c
      End If
   Next r
   Sheets("Sheet2").Range("A2").Resize(UBound(Ary), 11).Value = Ary
End Sub

Both the above code & Marcelo's take about 1.7secs with 300,000 rows & 9000 rows

 

[Marcelo Branco]

Both the above code & Marcelo's take about 1.7secs with 300,000 rows & 9000 rows

Seems very good

M.

 

[Marcelo Branco]

Fluff
Your code uses Ary(r,7) to Ary(r,11)
Mine, vData(i,8) to vData(i,12)

Are your data in different columns?

M.

 

[Fluff]

Are your data in different columns?

Nope, I just can't count.
With that correction the line should be

         dic(Ary(r, 1) & "|" & Ary(r, 5)) = Array(Ary(r, 8), Ary(r, 9), Ary(r, 10), Ary(r, 11), Ary(r, 12))