Index match statement with multiple criteria in VBA

[sjk1193]

So what I am trying to do is if data in the DATA tab in columns 1 & 5 match with columns 1 & 2 in the INDEX tab then take the data in the 3 adjacent columns and place it in the UPLOAD tab. After this is done I need to go to the next row and do the same

Example.

if Robert def in the DATA tab matches def Robert in the INDEX tab then I need to take FFFF, H, 8527 and place it in the UPLOAD tab

I basically need to do this for all data in the DATA tab



INDEX

1	2	3	4	5
abc	Augusta	AAAA	H	123
abc	Palmaire	BBBB	H	65464
abc	Tamaz	CCCC	H	4545
abc	ZCR	DDDD	H	453
abc	Moir	EEEE	C	453453
def	Robert	FFFF	H	8527
def	JCA	GGG	C	786
def	Caren	SDEFSDF	C	000
def	Nelson	SDFVB	C	111
def	Levy	DFD	C	222

<tbody>
</tbody>

DATA

1	2	3	4	5
Augusta	13	Q	00	abc
Tamaz	41	Q	11	abc
JCA	45	Q	22	def
Robert	453	Q	33	def
ZCR	63	Q	44	abc
Caren	98	Q	55	def
Moir	85	Q	66	abc
Palmaire	48	Q	77	abc
Levy	45	Q	88	def
Nelson	36	Q	99	def

<tbody>
</tbody>

UPLOAD

1	2	3	4	5	6	7	8	9
		FFFF	H	8527

<tbody>
</tbody>

 

[Fluff]

How about

Sub Getdata()
   Dim Cl As Range
   Dim Dws As Worksheet, Iws As Worksheet
   Dim Vu As String
   Dim Itm As Variant
   Dim i As Long
   
   Set Dws = Sheets("Data")
   Set Iws = Sheets("Index")
   With CreateObject("scripting.dictionary")
      For Each Cl In Dws.Range("A2", Dws.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Offset(, 4).Value & "|" & Cl.Value) = Empty
      Next Cl
      For Each Cl In Iws.Range("A2", Iws.Range("A" & Rows.Count).End(xlUp))
         Vu = Cl.Value & "|" & Cl.Offset(, 1).Value
         If .Exists(Vu) Then .Item(Vu) = Array(Cl.Offset(, 2).Value, Cl.Offset(, 3).Value, Cl.Offset(, 4).Value)
      Next Cl
      For Each Itm In .Items
         If IsArray(Itm) Then
            i = i + 1
            Sheets("Input").Range("C" & i).Resize(, 3).Value = Itm
         End If
      Next Itm
   End With
End Sub

 

[sjk1193]

Thank you!!
Would you mind commenting on what each section does though because I'm pretty sure I follow when I enter it into VBA but I just want to make sure so that I am not misleading myself

 

[Fluff]

Best idea is for you comment the code & post it back here, as I have no idea what your knowledge level is & therefore how much detail to go into.
That way I can correct any misunderstandings & comment anything that you don't understand.

 

[sjk1193]

I'm just starting to learn VBA so as much detail as possible would help. I normally F8 through the lines of code to see if i can follow whats going on and then google stuff I'm not sure about

 

[Fluff]

As I said, it's best if you comment it as best you can & post back

 

[sjk1193]

Dim Cl As Range
   Dim DATA As Worksheet, CIndex As Worksheet, FIndex As Worksheet
   Dim Vu As String
   Dim Itm As Variant
   Dim i As Long
   
   Set DATA = Sheets("DATA")
   Set CIndex = Sheets("Client Index")
   Set FIndex = Sheets("Fund Index")
   
   With CreateObject("scripting.dictionary")
      'looks end range in DATA tab
      For Each Cl In DATA.Range("A2", DATA.Range("A" & Rows.Count).End(xlUp))
        'scrolls through columns in DATA tab ascending order
         .Item(Cl.Offset(, 4).Value & "|" & Cl.Value) = Empty
      Next Cl
      'looks end range in Client Index  tab
      For Each Cl In CIndex.Range("A2", CIndex.Range("A" & Rows.Count).End(xlUp))
         Vu = Cl.Value & "|" & Cl.Offset(, 1).Value
         If .Exists(Vu) Then .Item(Vu) = Array(Cl.Offset(, 2).Value, Cl.Offset(, 3).Value, Cl.Offset(, 4).Value)
      Next Cl
      'Pasts all the matching data in Upload tab
      For Each Itm In .Items
         If IsArray(Itm) Then
            i = i + 1
            Sheets("Upload").Range("C" & i).Offset(1, 0).Resize(, 3).Value = Itm
         End If
      Next Itm
   End With

I'm just not sure how it searches and matches or what the "|" is

 

[Fluff]

Ok, added more comments

   Dim Cl As Range
   Dim DATA As Worksheet, CIndex As Worksheet, FIndex As Worksheet
   Dim Vu As String
   Dim Itm As Variant
   Dim i As Long
   
   Set DATA = Sheets("DATA")
   Set CIndex = Sheets("Client Index")
   Set FIndex = Sheets("Fund Index")
   
   'Creates a Dictionary
   With CreateObject("scripting.dictionary")
      'looks end range in DATA tab
      For Each Cl In DATA.Range("A2", DATA.Range("A" & Rows.Count).End(xlUp))
        'scrolls through columns in DATA tab ascending order
         'Concatenates the 2 cell values with a | in between & adds the resultant value to the dictionary (if it doesn't already exist) with nothing in the item
         .Item(Cl.Offset(, 4).Value & "|" & Cl.Value) = Empty
      Next Cl
      'looks end range in Client Index  tab
      For Each Cl In CIndex.Range("A2", CIndex.Range("A" & Rows.Count).End(xlUp))
         'Concatenates the 2 cell values with a | in between & puts it in the Vu variable
         Vu = Cl.Value & "|" & Cl.Offset(, 1).Value
         ' if the Vu value alreadu exists in the dictionary it adds an array in the item containing the 3 cell values you want returned
         If .Exists(Vu) Then .Item(Vu) = Array(Cl.Offset(, 2).Value, Cl.Offset(, 3).Value, Cl.Offset(, 4).Value)
      Next Cl
      'Pasts all the matching data in Upload tab
      'Loops through the Dictionary Items
      For Each Itm In .Items
         ' checks if the Item has an array
         If IsArray(Itm) Then
            i = i + 1
            'Pastes the values into the sheet
            Sheets("Upload").Range("C" & i).Offset(1, 0).Resize(, 3).Value = Itm
         End If
      Next Itm
   End With

For more info on Dictionaries have a look here https://excelmacromastery.com/vba-dictionary/
The | is simply a separator between the concatenated values. Without it if you had values like
123 456
12 3456
These would both be concatenated to
123456
and would therefore be treated as the same, when they should be treated as different.

HTH