Index table, match substrings and count how many matches

D

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
591
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
43706T_C6000Cabin Door - Glass - Deeper DoorSMC00377215404
43915T_C6001Cabin Door - BarredSMC00369216127
44140T_C6002Cabin Door - Glass - Deeper DoorSMC00377215404
43675T_C6003Super Cabin Door - New GlassSMC00419215808
42842T_C6004Standard Cabin Door With PerspexNMC01099215329
42086T_C6005
42494T_C6006
43160T_C6007
43898T_C6008Cabin Door - BarredHMC00604215044
43964T_C6009Cabin Door - BarredHMC00604215044
44183T_C6010Cabin Door - BarredHMC00604215044
44405T_C6011Cabin Door - GlassHMC00550214812
43185T_C6012
42786T_C6013
43968T_C6014
42920T_C3000NCabin Door - Glass HPC00050 LatchLMC00404215328
42921T_C3000NCabin Door - Barred HPC00050 LatchLMC00407214644
42957T_C3000NCabin Door - Glass HPC00050 LatchLMC00404215328
43044T_C3000NCabin Door - Glass HPC00050 LatchLMC00404215328
43045T_C3000NCabin Door - Glass HPC00050 LatchLMC00404215328
43046T_C3000NCabin Door - Glass HPC00050 LatchLMC00404215328
43087T_C3000NCabin Door - Glass HPC00050 LatchLMC00404215536
41037T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215404
42762T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42766T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42791T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42792T_C4000XL Cab Door Assem (HPC50) New GlassHMC00953215152
42793T_C4000XL Cab Door Assem (HPC50) New GlassHMC00953215152
42819T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42820T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42904T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42970T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
42984T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
43015T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
43020T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
43600T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
44120T_C4000Cabin Door - Barred - HPC50 Door LatchCMC00994215404
45444T_C4000Cabin Door - Glass - HPC00050 Door LatchCMC00377215606
40942T_C4000LCabin Door - GlassHMC00550214812
45434T_C4000SCabin Door - GlassHMC00550214812
42804T_C4000WSL-Cabin Std Door - Glass WSL 2014HMC00722215536
42805T_C4000WSL-Cabin Std Door - Glass WSL 2014HMC00722215536
42872T_C5000XLCab Deep Barred Door *** for HPC50 LatchHMC00948213840
42959T_C5000XLCab Door *** for HPC50 Handle - BarredHMC00944215404
42961T_C5000XLCab Door *** for HPC50 Handle - BarredHMC00944215404
42969T_C5000XLCab Door *** for HPC50 Handle - BarredHMC00944215404
43022T_C5000XLCab Door Assem (HPC50) New GlassHMC00953215152
43023T_C5000XLCab Door Assem (HPC50) New GlassHMC00953215152
43024T_C5000XLCab Door Assem (HPC50) New GlassHMC00953215152
43025T_C5000XLCab Door Assem (HPC50) New GlassHMC00953215809
44002T_C5000XLCab Door Assem (HPC50) New GlassHMC00953215809
44572T_C5000NXLC5XL Cabin Door - GlassHMC01120215810
SPARE1 Cabin Door - Glass HPC00050 LatchLMC00404216057
SPARE2 Cabin Door - Glass HPC00050 LatchLMC00404216057

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

My 2 columns (D:E)
SMC0037743706 44140
SMC0036943915
SMC0041943675
NMC0109942842
HMC0060443898 43964 44183
HMC0055044405 40942 45434
LMC0040442920 42957 43044 43045 43046 43087 SPARE1 SPARE2
LMC0040742921
CMC0037741037 42762 42766 42791 42819 42820 42904 42970
CMC0037742984 43015 43020 43600 45444
HMC0095342792 42793 43022 43023 43024 43025 44002
CMC0099444120
HMC0072242804 42805
HMC0094842872
HMC0094442959 42961 42969
HMC0112044572
SMC0032943706
SMC0004743915
SMC0005044140
SMC0023043675
NMC0147642842
UMC0002042086 42494 43160
AMC0015043898 43964 44183 44405
VEMC0223 (VEMC0217/222)43185
MMC0185242786
MMC0197843968
LMC0023142920 42921 42957 43044 43045 43046
LMC0027843087
CMC0022041037 42762 42819 42820 42904 42970 42984 43015
CMC0022043020 43600 44120 45444
CMC0022142766 42791
HMC0000042792 42793 42872 42959 42961 42969 43022 43023
HMC0000043024 43025 44002
HMC0068140942
HMC0049045434
HMC0070242804 42805
HMC0111544572

<colgroup><col><col></colgroup><tbody>
</tbody>

In column F wished result
215404 (2)
216127 (1)
215808 (1)
215329 (1)
215044 (3)
214812 (3)
215328(5) 215536(2) 216057(2)
214644(1)
.
.
.

etc
.
.
.
.

215951(1)
214812(1)
215404(2)
215810(1)

<colgroup><col></colgroup><tbody>
</tbody>

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)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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
 
Upvote 0
How to place them 2 For functions together so first one goes next only when second one is done with all its steps?
Code: 
    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
 
Last edited:
Upvote 0
I did it
Code: 
 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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,028
Messages
6,122,749
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top