Index table, match substrings and count how many matches

[dado6612]

Hello all
I have in 2 columns a couple of strings in a cell
Is it possible to index a master table and count how many times do they match?

ex.
Master table
[TABLE="width: 710"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]43706[/TD]
[TD]T_C6000[/TD]
[TD]Cabin Door - Glass - Deeper Door[/TD]
[TD]SMC00377[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]43915[/TD]
[TD]T_C6001[/TD]
[TD]Cabin Door - Barred[/TD]
[TD]SMC00369[/TD]
[TD]216127[/TD]
[/TR]
[TR]
[TD]44140[/TD]
[TD]T_C6002[/TD]
[TD]Cabin Door - Glass - Deeper Door[/TD]
[TD]SMC00377[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]43675[/TD]
[TD]T_C6003[/TD]
[TD]Super Cabin Door - New Glass[/TD]
[TD]SMC00419[/TD]
[TD]215808[/TD]
[/TR]
[TR]
[TD]42842[/TD]
[TD]T_C6004[/TD]
[TD]Standard Cabin Door With Perspex[/TD]
[TD]NMC01099[/TD]
[TD]215329[/TD]
[/TR]
[TR]
[TD]42086[/TD]
[TD]T_C6005[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]42494[/TD]
[TD]T_C6006[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]43160[/TD]
[TD]T_C6007[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]43898[/TD]
[TD]T_C6008[/TD]
[TD]Cabin Door - Barred[/TD]
[TD]HMC00604[/TD]
[TD]215044[/TD]
[/TR]
[TR]
[TD]43964[/TD]
[TD]T_C6009[/TD]
[TD]Cabin Door - Barred[/TD]
[TD]HMC00604[/TD]
[TD]215044[/TD]
[/TR]
[TR]
[TD]44183[/TD]
[TD]T_C6010[/TD]
[TD]Cabin Door - Barred[/TD]
[TD]HMC00604[/TD]
[TD]215044[/TD]
[/TR]
[TR]
[TD]44405[/TD]
[TD]T_C6011[/TD]
[TD]Cabin Door - Glass[/TD]
[TD]HMC00550[/TD]
[TD]214812[/TD]
[/TR]
[TR]
[TD]43185[/TD]
[TD]T_C6012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]42786[/TD]
[TD]T_C6013[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]43968[/TD]
[TD]T_C6014[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]42920[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]215328[/TD]
[/TR]
[TR]
[TD]42921[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Barred HPC00050 Latch[/TD]
[TD]LMC00407[/TD]
[TD]214644[/TD]
[/TR]
[TR]
[TD]42957[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]215328[/TD]
[/TR]
[TR]
[TD]43044[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]215328[/TD]
[/TR]
[TR]
[TD]43045[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]215328[/TD]
[/TR]
[TR]
[TD]43046[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]215328[/TD]
[/TR]
[TR]
[TD]43087[/TD]
[TD]T_C3000N[/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]215536[/TD]
[/TR]
[TR]
[TD]41037[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]42762[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42766[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42791[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42792[/TD]
[TD]T_C4000[/TD]
[TD]XL Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215152[/TD]
[/TR]
[TR]
[TD]42793[/TD]
[TD]T_C4000[/TD]
[TD]XL Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215152[/TD]
[/TR]
[TR]
[TD]42819[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42820[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42904[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42970[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]42984[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]43015[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]43020[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]43600[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]44120[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Barred - HPC50 Door Latch[/TD]
[TD]CMC00994[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]45444[/TD]
[TD]T_C4000[/TD]
[TD]Cabin Door - Glass - HPC00050 Door Latch[/TD]
[TD]CMC00377[/TD]
[TD]215606[/TD]
[/TR]
[TR]
[TD]40942[/TD]
[TD]T_C4000L[/TD]
[TD]Cabin Door - Glass[/TD]
[TD]HMC00550[/TD]
[TD]214812[/TD]
[/TR]
[TR]
[TD]45434[/TD]
[TD]T_C4000S[/TD]
[TD]Cabin Door - Glass[/TD]
[TD]HMC00550[/TD]
[TD]214812[/TD]
[/TR]
[TR]
[TD]42804[/TD]
[TD]T_C4000WSL-[/TD]
[TD]Cabin Std Door - Glass WSL 2014[/TD]
[TD]HMC00722[/TD]
[TD]215536[/TD]
[/TR]
[TR]
[TD]42805[/TD]
[TD]T_C4000WSL-[/TD]
[TD]Cabin Std Door - Glass WSL 2014[/TD]
[TD]HMC00722[/TD]
[TD]215536[/TD]
[/TR]
[TR]
[TD]42872[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Deep Barred Door *** for HPC50 Latch[/TD]
[TD]HMC00948[/TD]
[TD]213840[/TD]
[/TR]
[TR]
[TD]42959[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door *** for HPC50 Handle - Barred[/TD]
[TD]HMC00944[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]42961[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door *** for HPC50 Handle - Barred[/TD]
[TD]HMC00944[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]42969[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door *** for HPC50 Handle - Barred[/TD]
[TD]HMC00944[/TD]
[TD]215404[/TD]
[/TR]
[TR]
[TD]43022[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215152[/TD]
[/TR]
[TR]
[TD]43023[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215152[/TD]
[/TR]
[TR]
[TD]43024[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215152[/TD]
[/TR]
[TR]
[TD]43025[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215809[/TD]
[/TR]
[TR]
[TD]44002[/TD]
[TD]T_C5000XL[/TD]
[TD]Cab Door Assem (HPC50) New Glass[/TD]
[TD]HMC00953[/TD]
[TD]215809[/TD]
[/TR]
[TR]
[TD]44572[/TD]
[TD]T_C5000NXL[/TD]
[TD]C5XL Cabin Door - Glass[/TD]
[TD]HMC01120[/TD]
[TD]215810[/TD]
[/TR]
[TR]
[TD]SPARE1[/TD]
[TD] [/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]216057[/TD]
[/TR]
[TR]
[TD]SPARE2[/TD]
[TD] [/TD]
[TD]Cabin Door - Glass HPC00050 Latch[/TD]
[TD]LMC00404[/TD]
[TD]216057[/TD]
[/TR]
</tbody>[/TABLE]

My 2 columns (D:E)
[TABLE="width: 524"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SMC00377[/TD]
[TD]43706 44140[/TD]
[/TR]
[TR]
[TD]SMC00369[/TD]
[TD]43915[/TD]
[/TR]
[TR]
[TD]SMC00419[/TD]
[TD]43675[/TD]
[/TR]
[TR]
[TD]NMC01099[/TD]
[TD]42842[/TD]
[/TR]
[TR]
[TD]HMC00604[/TD]
[TD]43898 43964 44183[/TD]
[/TR]
[TR]
[TD]HMC00550[/TD]
[TD]44405 40942 45434[/TD]
[/TR]
[TR]
[TD]LMC00404[/TD]
[TD]42920 42957 43044 43045 43046 43087 SPARE1 SPARE2[/TD]
[/TR]
[TR]
[TD]LMC00407[/TD]
[TD]42921[/TD]
[/TR]
[TR]
[TD]CMC00377[/TD]
[TD]41037 42762 42766 42791 42819 42820 42904 42970[/TD]
[/TR]
[TR]
[TD]CMC00377[/TD]
[TD]42984 43015 43020 43600 45444[/TD]
[/TR]
[TR]
[TD]HMC00953[/TD]
[TD]42792 42793 43022 43023 43024 43025 44002[/TD]
[/TR]
[TR]
[TD]CMC00994[/TD]
[TD]44120[/TD]
[/TR]
[TR]
[TD]HMC00722[/TD]
[TD]42804 42805[/TD]
[/TR]
[TR]
[TD]HMC00948[/TD]
[TD]42872[/TD]
[/TR]
[TR]
[TD]HMC00944[/TD]
[TD]42959 42961 42969[/TD]
[/TR]
[TR]
[TD]HMC01120[/TD]
[TD]44572[/TD]
[/TR]
[TR]
[TD]SMC00329[/TD]
[TD]43706[/TD]
[/TR]
[TR]
[TD]SMC00047[/TD]
[TD]43915[/TD]
[/TR]
[TR]
[TD]SMC00050[/TD]
[TD]44140[/TD]
[/TR]
[TR]
[TD]SMC00230[/TD]
[TD]43675[/TD]
[/TR]
[TR]
[TD]NMC01476[/TD]
[TD]42842[/TD]
[/TR]
[TR]
[TD]UMC00020[/TD]
[TD]42086 42494 43160[/TD]
[/TR]
[TR]
[TD]AMC00150[/TD]
[TD]43898 43964 44183 44405[/TD]
[/TR]
[TR]
[TD]VEMC0223 (VEMC0217/222)[/TD]
[TD]43185[/TD]
[/TR]
[TR]
[TD]MMC01852[/TD]
[TD]42786[/TD]
[/TR]
[TR]
[TD]MMC01978[/TD]
[TD]43968[/TD]
[/TR]
[TR]
[TD]LMC00231[/TD]
[TD]42920 42921 42957 43044 43045 43046[/TD]
[/TR]
[TR]
[TD]LMC00278[/TD]
[TD]43087[/TD]
[/TR]
[TR]
[TD]CMC00220[/TD]
[TD]41037 42762 42819 42820 42904 42970 42984 43015[/TD]
[/TR]
[TR]
[TD]CMC00220[/TD]
[TD]43020 43600 44120 45444[/TD]
[/TR]
[TR]
[TD]CMC00221[/TD]
[TD]42766 42791[/TD]
[/TR]
[TR]
[TD]HMC00000[/TD]
[TD]42792 42793 42872 42959 42961 42969 43022 43023[/TD]
[/TR]
[TR]
[TD]HMC00000[/TD]
[TD]43024 43025 44002[/TD]
[/TR]
[TR]
[TD]HMC00681[/TD]
[TD]40942[/TD]
[/TR]
[TR]
[TD]HMC00490[/TD]
[TD]45434[/TD]
[/TR]
[TR]
[TD]HMC00702[/TD]
[TD]42804 42805[/TD]
[/TR]
[TR]
[TD]HMC01115[/TD]
[TD]44572[/TD]
[/TR]
</tbody>[/TABLE]

In column F wished result
[TABLE="width: 194"]
<colgroup><col></colgroup><tbody>[TR]
[TD]215404 (2)[/TD]
[/TR]
[TR]
[TD]216127 (1)[/TD]
[/TR]
[TR]
[TD]215808 (1)[/TD]
[/TR]
[TR]
[TD]215329 (1)[/TD]
[/TR]
[TR]
[TD]215044 (3)[/TD]
[/TR]
[TR]
[TD]214812 (3)[/TD]
[/TR]
[TR]
[TD]215328(5) 215536(2) 216057(2)[/TD]
[/TR]
[TR]
[TD]214644(1)
.
.
.

[/TD]
[/TR]
[TR]
[TD]etc
.
.
.
.

[/TD]
[/TR]
[TR]
[TD]215951(1)[/TD]
[/TR]
[TR]
[TD]214812(1)[/TD]
[/TR]
[TR]
[TD]215404(2)[/TD]
[/TR]
[TR]
[TD]215810(1)[/TD]
[/TR]
</tbody>[/TABLE]

If needed for easier match, I have helper column (I) which looks the same as wished result but without counts and brackets.

Hope it's clear and easy to do. Thanks
(sorry if them tables ended up badly, i can put a dropbox for a file if needed)

 

[dado6612]

Will try to make this more clear and easier, maybe this way can work
I have a column E and F as presented above with series of srrings in cells (sometimes there are more than 1 in each column). I have a master table where those strings are matched by rows, in columns "B&F", "B&H"I need a cose for column F to be matched and then counted how many times string matches with others in master table(check column F wished result).
There is space in column "I" for dumping as code works

 

[dado6612]

How to place them 2 For functions together so first one goes next only when second one is done with all its steps?

    For d = amp To lrq        
        Range("f" & b).Select
        Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
        Range("e" & b).Select
        Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 2), Array(2, 1)), TrailingMinusNumbers:=True
    For p = emp To colq
        Range("j1:q1").Select
        Selection.Copy
        If Range("j2").Offset(0, o) <> "" Then
        Range("j3").Offset(0, o).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        Range("j11").Formula = "=IFERROR(IF(J$2=INDEX(Sheet1!$F:$F,MATCH(Sheet2!J3,Sheet1!$F:$F,0),0),1,0),IF(J$2=INDEX(Sheet1!$I:$I,MATCH(Sheet2!J3,Sheet1!$B:$B,0),0),1,0))"
        Range("j11:j18").FillDown
        Range("j11", Range("j18").Offset(0, o)).FillRight
        Range("j19").Formula = "=J2&""(""&SUM(J11:J18)&"")"""
        Range("j11", Range("j19").Offset(0, o)).FillRight
        o = o + 1
        End If
   Next
    Range("f" & b).Formula = "=concat(J19:Q19,"" "")"
    Range("F" & b).Copy
    Range("f" & b).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("j1:k20").ClearContents
    b = b + 1
    Next

Also I noticed every time I run this portion of code it asks me 30+ times if I want to replace contents of cells, can that be avoided? Or am I doing something wrong? Thanks

 

[dado6612]

I did it

 amp = 2    b = amp
For d = amp To lrq
    Range("E" & b).Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(0, 1), Array(5, 1), Array(11, 1), Array(17, 1), Array(23, 1), _
        Array(29, 1), Array(35, 1), Array(41, 1)), TrailingMinusNumbers:=True
    Range("F" & b).Select
    Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(0, 1), Array(5, 1), Array(11, 1), Array(17, 1), Array(23, 1), _
        Array(29, 1), Array(35, 1), Array(41, 1)), TrailingMinusNumbers:=True
    Range("J1:Q1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J3:Q3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        Application.CutCopyMode = False
        Range("j11").Formula = "=IFERROR(IF(J$2=INDEX(Sheet1!$F:$F,MATCH(Sheet2!J3,Sheet1!$b:$b,0),0),1,IF(J$2=INDEX(Sheet1!$I:$I,MATCH(Sheet2!J3,Sheet1!$B:$B,0),0),1,0)),"""")"
        Range("j11:j18").FillDown
        Range("j19").Formula = "=if(SUM(J11:J18)=0,"""",if(j2<>"""",J2&""(""&SUM(J11:J18)&"")"",""""))"
        Range("j11:q19").FillRight
    Range("f" & b).Formula = "=concat(J19:Q19,"" "")"
    Range("F" & b).Copy
    Range("f" & b).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("j1:q20").ClearContents
    b = b + 1
Next