Indexing and matching based on complex criteria

[MurdochQuill]

Hi.

I have a difficult one for you all.

I'm trying to translate some data to different columns in VBA based on some complex criteria. Not sure on the formatting to search this stuff so hopefully someone can help !

What I would like to do is feed in raw data ID's under Name/Desc/Type in F column. I would like to then match them to the A, B and C columns, then I would like to copy/paste the G column "Value" into the corresponding D "Value" column where there is a match.

Here is an example sheet:

Book1
	A	B	C	D	E	F	G
1	Name	Description	TYPE	VALUE:		Name/DESC/TYPE	Value:
2		AA	G3			YTR5-G4	12
3	DD	CC	G3			WER3-G3	12
4	G564	CC	G3			JHG6-G6	33
5		BB	G3			JHG6-G5	19
6	G544	CC	G3			I78-G6	45
7	G55	CC	G3			I78-G3	324
8	G55	CC	G6			HFH4-G5	560
9	I78	CC	G3			G564-G3	43
10	I78	CC	G6			G55-G6	45
11	JHG6	CC	G5			G55-G3	345
12	JHG6	CC	G6			G544-G3	55
13	HFH4	CC	G5			FGH4-G3	2
14	456D	CC	G5			DD-G3	36
15	456S	CC	G3			BB-G3	56
16	WER3	CC	G3			AA-G3	68
17	FGH4	CC	G3			456S-G3	3
18	YTR5	CC	G4			456D-G5	4
Sheet1

So here are a bunch of rules I need to work out when copying over:

• Description AA will always ignore any input name, but as long as we match the description that's ok.
• Description BB same as above.
• Description CC will always need to have name matched on column F before the "-XX" suffix.
• Type will always be a suffix in the form of "-XX".
• Both name and suffix need to match in order to send from column G to column D.

Here is what it should look like when complete:

Book1
	A	B	C	D	E	F	G
1	Name	Description	TYPE	VALUE:		Name/DESC/TYPE	Value:
2		AA	G3	68		YTR5-G4	12
3	DD	CC	G3	36		WER3-G3	12
4	G564	CC	G3	43		JHG6-G6	33
5		BB	G3	56		JHG6-G5	19
6	G544	CC	G3	55		I78-G6	45
7	G55	CC	G3	345		I78-G3	324
8	G55	CC	G6	45		HFH4-G5	560
9	I78	CC	G3	324		G564-G3	43
10	I78	CC	G6	45		G55-G6	45
11	JHG6	CC	G5	19		G55-G3	345
12	JHG6	CC	G6	33		G544-G3	55
13	HFH4	CC	G5	560		FGH4-G3	2
14	456D	CC	G5	4		DD-G3	36
15	456S	CC	G3	3		BB-G3	56
16	WER3	CC	G3	12		AA-G3	68
17	FGH4	CC	G3	2		456S-G3	3
18	YTR5	CC	G4	12		456D-G5	4
Sheet1

 

[Fluff]

Any particular reason you need a macro, as this can easily be one with a formula.

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Name	Description	TYPE	VALUE:		Name/DESC/TYPE	Value:
2		AA	G3	68		YTR5-G4	12
3	DD	CC	G3	36		WER3-G3	12
4	G564	CC	G3	43		JHG6-G6	33
5		BB	G3	56		JHG6-G5	19
6	G544	CC	G3	55		I78-G6	45
7	G55	CC	G3	345		I78-G3	324
8	G55	CC	G6	45		HFH4-G5	560
9	I78	CC	G3	324		G564-G3	43
10	I78	CC	G6	45		G55-G6	45
11	JHG6	CC	G5	19		G55-G3	345
12	JHG6	CC	G6	33		G544-G3	55
13	HFH4	CC	G5	560		FGH4-G3	2
14	456D	CC	G5	4		DD-G3	36
15	456S	CC	G3	3		BB-G3	56
16	WER3	CC	G3	12		AA-G3	68
17	FGH4	CC	G3	2		456S-G3	3
18	YTR5	CC	G4	12		456D-G5	4
19
Main

Cell Formulas
Range	Formula
D2:D18	D2	=XLOOKUP(IF(OR(B2={"AA","BB"}),B2,A2)&"-"&C2,$F$2:$F$18,$G$2:$G$18,"",0)

 

[MurdochQuill]

Any particular reason you need a macro, as this can easily be one with a formula.

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Name	Description	TYPE	VALUE:		Name/DESC/TYPE	Value:
2		AA	G3	68		YTR5-G4	12
3	DD	CC	G3	36		WER3-G3	12
4	G564	CC	G3	43		JHG6-G6	33
5		BB	G3	56		JHG6-G5	19
6	G544	CC	G3	55		I78-G6	45
7	G55	CC	G3	345		I78-G3	324
8	G55	CC	G6	45		HFH4-G5	560
9	I78	CC	G3	324		G564-G3	43
10	I78	CC	G6	45		G55-G6	45
11	JHG6	CC	G5	19		G55-G3	345
12	JHG6	CC	G6	33		G544-G3	55
13	HFH4	CC	G5	560		FGH4-G3	2
14	456D	CC	G5	4		DD-G3	36
15	456S	CC	G3	3		BB-G3	56
16	WER3	CC	G3	12		AA-G3	68
17	FGH4	CC	G3	2		456S-G3	3
18	YTR5	CC	G4	12		456D-G5	4
19
Main

Cell Formulas
Range	Formula
D2:D18	D2	=XLOOKUP(IF(OR(B2={"AA","BB"}),B2,A2)&"-"&C2,$F$2:$F$18,$G$2:$G$18,"",0)

Thank you for your help Fluff. Yeah the current solution is done with cell formulas, but I would like to use as little in cell formulae as possible. That being said, I'm also interested in how to approach these problems in VBA.

(Worst case scenario I dump a formula in, then convert to values).

 

[Fluff]

Ok, how about

Sub MurdochQuill()
   Dim Cl As Range
   Dim Txt As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
         Select Case Cl.Offset(, -2).Value
            Case "AA", "BB"
               Txt = Cl.Offset(, -2).Value & "-" & Cl.Offset(, -1).Value
            Case Else
               Txt = Cl.Offset(, -3).Value & "-" & Cl.Offset(, -1).Value
         End Select
         If .Exists(Txt) Then Cl.Value = .Item(Txt)
      Next Cl
   End With
End Sub

 

[MurdochQuill]

Ok, how about

Sub MurdochQuill()
   Dim Cl As Range
   Dim Txt As String
 
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
         Select Case Cl.Offset(, -2).Value
            Case "AA", "BB"
               Txt = Cl.Offset(, -2).Value & "-" & Cl.Offset(, -1).Value
            Case Else
               Txt = Cl.Offset(, -3).Value & "-" & Cl.Offset(, -1).Value
         End Select
         If .Exists(Txt) Then Cl.Value = .Item(Txt)
      Next Cl
   End With
End Sub

This is excellent!
Say I wanted to grab this data from elsewhere, F & G columns from Sheet 2 to put into column D on a Destination sheet, I could:
Set SrcSheet = ActiveWorkbook.Sheets("Sheet2")
Set DstSheet = ActiveWorkbook.Sheets("Sheet1")

I see that you created an object using the F column, I'm not sure how to direct this to the destination sheet without breaking your code.

 

[Fluff]

You can do that like

Sub MurdochQuill()
   Dim Cl As Range
   Dim Txt As String
   Dim wsSrc As Worksheet, wsDest As Worksheet
   Set wsSrc = Sheets("Sheet1")
   Set wsDest = Sheets("Sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In wsSrc.Range("F2", wsSrc.Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In wsDest.Range("D2:D" & wsDest.Range("A" & Rows.Count).End(xlUp).Row)
         Select Case Cl.Offset(, -2).Value
            Case "AA", "BB"
               Txt = Cl.Offset(, -2).Value & "-" & Cl.Offset(, -1).Value
            Case Else
               Txt = Cl.Offset(, -3).Value & "-" & Cl.Offset(, -1).Value
         End Select
         If .Exists(Txt) Then Cl.Value = .Item(Txt)
      Next Cl
   End With
End Sub

 

[MurdochQuill]

You can do that like

Sub MurdochQuill()
   Dim Cl As Range
   Dim Txt As String
   Dim wsSrc As Worksheet, wsDest As Worksheet
   Set wsSrc = Sheets("Sheet1")
   Set wsDest = Sheets("Sheet2")
 
   With CreateObject("scripting.dictionary")
      For Each Cl In wsSrc.Range("F2", wsSrc.Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In wsDest.Range("D2:D" & wsDest.Range("A" & Rows.Count).End(xlUp).Row)
         Select Case Cl.Offset(, -2).Value
            Case "AA", "BB"
               Txt = Cl.Offset(, -2).Value & "-" & Cl.Offset(, -1).Value
            Case Else
               Txt = Cl.Offset(, -3).Value & "-" & Cl.Offset(, -1).Value
         End Select
         If .Exists(Txt) Then Cl.Value = .Item(Txt)
      Next Cl
   End With
End Sub

You have been incredibly helpful Fluff. A true gentleman and scholar :D

I like the idea of using column offsets to work around the problem.

 

[Fluff]

You're welcome & thanks for the feedback.