Trying to match 2 arrays using VBA please help

[TeckTeck]

Hi anyone, I would like to match this 2 table as shown:

Using the reference list, match the master list and return Column D to Column G. However the placement of the return has to be of the same position as the master list.

Condition 1: If the code number of the reference list (Column D) matches the code number of the master list (Column A) then paste Column D in the same placement as Column A in a new column (Column G)

Condition 2: If the Name of the reference list (Column E) matches partially or completely to the name of the master list (Column B) then paste Column D in the same placement as Column A in a new column (Column G)

I am aware and tried VLOOKUP or Using wildcards ("*") but could not do it. I am hoping someone has done it before and can share with me the VBA codes for this type of situation. Thank you.

 

[jolivanes]

I would have a macro that will make all the Names the same.
It would mean making a onetime list for comparison.
That list could be in a hidden sheet if you want to.
I can see your Material Balance being called "Mat Bal", "Material Bal", "Mat Balance" and maybe another several possibilities let alone Capitals and Small letters like you have in the last entry in Column B.

 

[TeckTeck]

Hi and how can i do that as i am kind of new to VBA

 

[jolivanes]

Which Column would be the "Master" Column?
Do you want the data in Column E to be changed to the text in Column A or the other way around?

 

[Akuini]

Is it possible for the text in column E to partially and completely match different cell text in column B? like this:

TeckTeck 1.xlsm
	A	B	C	D	E	F	G
2	Code Number	Name		Code Number	Name		Code Number
3	6705	Instrumentation Pressure Relief		603	Pressure Relief
4	1750	Pressure Relief
Sheet2

 

[TeckTeck]

Which Column would be the "Master" Column?
Do you want the data in Column E to be changed to the text in Column A or the other way around?

Hi after comparing the reference list to the master list (for both text and/or code number)

i would need the reference list code number (column D) to be pasted onto a new column, however, the pasted code number should be on the same row as the master list that was matched.

 

[TeckTeck]

Is it possible for the text in column E to partially and completely match different cell text in column B? like this:

TeckTeck 1.xlsm
	A	B	C	D	E	F	G
2	Code Number	Name		Code Number	Name		Code Number
3	6705	Instrumentation Pressure Relief		603	Pressure Relief
4	1750	Pressure Relief
Sheet2

Hi, yes however is it possible to paste D3 into G4 as it must match the row of column A/B.

So G3 would appear blank and G4 would appear as "603"

 

[Akuini]

Sorry, I misunderstood your data. After reading your example again, I don't think the partial match will work. Excel functions can't recognize 'Mat Bal' as a partial match for 'Material Balance'. So, I can't help you. I hope what @jolivanes suggested in post #2 would work.

 

[Georgiboy]

This method has it's flaws as many will, i have used this method before to match data, it may not work for you but will share anyway. This method required VBA as well as Formulae:

Book1
	A	B	C	D	E	F	G
1	Master List			Reference List			New Code from Column E with Reference to Master List S/N
2	Code Number	Name		Code Number	Name		Code Number
3	6705	Material Balance		1	Piping Instrument Diagram		3
4	1750	Plot Plan		2	Instrumenation Pressure
5	135	Statistic with Anova		3	Mat Bal
6	2000	Heat and Mass		4	Engineering Control Design
7	7884	Piping Diagram		5	Pressure Relief		1
8	3270	Process Flow
9	8783	Basis of Design
10	1007	Pressure Relief					5
11	3040	Instrumentation Pressure Safety					2
12	8137	Engineering and Control					4
Sheet1

Cell Formulas
Range	Formula
G3:G12	G3	=LET( lv,--BYROW($E$3:$E$7,LAMBDA(x,IF(ISNUMBER(SEARCH(LEFT(x,3),B3)),LevenshteinDistance(B3,x),100))), f,FILTER($D$3:$D$7,lv=MIN(lv)), IF(COUNTA(f)=1,f,"") )

Code Used:

Function LevenshteinDistance(s1 As String, s2 As String) As Integer
    Dim len1 As Integer
    Dim len2 As Integer
    Dim d() As Integer
    Dim i As Integer
    Dim j As Integer
    
    len1 = Len(s1)
    len2 = Len(s2)
    
    ReDim d(0 To len1, 0 To len2)
    
    For i = 0 To len1
        d(i, 0) = i
    Next i
    
    For j = 0 To len2
        d(0, j) = j
    Next j
    
    For i = 1 To len1
        For j = 1 To len2
            If Mid(s1, i, 1) = Mid(s2, j, 1) Then
                d(i, j) = d(i - 1, j - 1)
            Else
                d(i, j) = Application.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + 1)
            End If
        Next j
    Next i

    LevenshteinDistance = d(len1, len2)
End Function