Word Find/Map between Several Columns

Question

I have the below data in excel [which contains hundreds of rows]:

I am trying to find/map the words in the column "Form Word" (columnA) against the column "Form Word Orig." (columnC) and retrieve the root word which matches between "Root Results" (columnB) and "Root Results - Multiple Options" (columnD).

Note: The solution needs to find the matching ROOT from the several options (columnD) which are grouped by the Form word (columnC)

The solution would generate results something like this following:

If you could help provide a formula OR Visual Basic based solution I would be grateful.

Thank you in advance.

[This question](https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another), plus an IFERROR if there is no match. — BigBen, Jul 24 '20 at 16:42
Tried using vlookup and tried playing around with Conditional Formatting. No luck. — Ghulam, Jul 24 '20 at 16:46
What rule to be applied to obtain what you name "root"? Why the third row keeping values 'Word1 - aaa', being identic with the first row, does not return 'AAA', too? Should it be returned only once? — FaneDuru, Jul 24 '20 at 16:58
In order to exist a root match, is it necessary that value in B:B to be equal with low case value in D:D? Or it only happened that and a match can also be considered between two identic strings (both of them UCase)? — FaneDuru, Jul 24 '20 at 17:09
I have used uppercase and lowercase letters just for illustration purposes. I was trying to highlight that the match needs to happen between certain specific words (roots) which are referenced using columnA and columnC as a type of index. I hop that is clear. — Ghulam, Jul 24 '20 at 17:17

score 0 · Accepted Answer · answered Jul 24 '20 at 17:16

Try the next code, please:

Sub testFind_Mapp_Col()
  Dim sh As Worksheet, arr1 As Variant, arr2 As Variant, i As Long, j As Long
  
  Set sh = ActiveSheet 'use here your sheet
  arr1 = sh.Range("A2:B" & sh.Range("A" & Rows.count).End(xlUp).Row).Value
  arr2 = sh.Range("C2:E" & sh.Range("C" & Rows.count).End(xlUp).Row).Value
  
  For i = 1 To UBound(arr1)
    For j = 1 To UBound(arr2)
        If arr1(i, 1) = arr2(j, 1) Then
            If UCase(arr2(j, 2)) = Ucase(arr1(i, 2)) Then arr2(j, 3) = arr1(i, 2): Exit For
        End If
    Next j
  Next i
  sh.Range("C2").Resize(UBound(arr2), UBound(arr2, 2)).Value = arr2
End Sub

Your solution is was perfect. Thank you! – Ghulam Jul 24 '20 at 17:57 — Ghulam, Jul 24 '20 at 17:57

Word Find/Map between Several Columns

1 Answers1