Match Partially Strings Between Cells & Ranges

E

excelakos

Board Regular
Joined
Jan 22, 2014
Messages
85
Hey everybody!! My apologies about this query. Its something i could not even find a proper title. So i hope my description is on the point. Use of screenshots is also needed, but lets start with tables and xl2bb

Betakos Profit Analyzer.xlsb
ABCDEFGHIJKLMNOPQRSTU
1
2PickCodePickLeague1EntosXeKTos21.%X.%2.%Odds ClassIndexPickPickCodeFromTillStakes TargetProfit Target
311ΑΓΓΠ4,15111/1/201531/12/209952
422ΑΓΓΠ0,15X2321/1/201531/12/2099
53X
61212
7131XLeague1EntosXeKTos21.%X.%2.%Odds Class
832X2ΑΓΓΠ4,15A3,5B1,80,150,250,6ΑΓΓΠ 2,37
9
10
11
Portfolio
Cell Formulas
RangeFormula
Q3:Q4Q3=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")


Table 11 is where i input my selection criteria from cell e3 till cell n.

then in table 12 i have data in respectively cells
Now i need a way to search and match if the data existing in table 12 also exist in table 11

I need to match like the image i attach

The example is this:
Does data in E8 exists in any cell of column "League" in table 11? Then
Does data in F8 exists in any cell of column "1" in table 11? Then
Does data in G8 exists in any cell of column "Entos" in table 11? And so on
As you can notice i have used an example of table 12 where same data exist in both rows of table 11


Betakos Profit Analyzer.xlsb
DEFGHIJKLMNOPQRS
1
2League1EntosXeKTos21.%X.%2.%Odds ClassIndexPickPickCodeFromTill
3ΑΓΓΠ4,15111/1/201531/12/2099
4ΑΓΓΠ0,15X2321/1/201531/12/2099
5
6
7League1EntosXeKTos21.%X.%2.%Odds ClassCountIf
8ΑΓΓΠ4,15A3,5B1,80,150,250,6ΑΓΓΠ 2,372
9
10
Portfolio
Cell Formulas
RangeFormula
Q3:Q4Q3=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")


so i need a kind of count if in cell O8 to inform that there are 2 matches of data inputs in table 11 for the row of table 12
and in this example this finding is controversial because
If i had only the first row of table 11 i need to get Pick 1 as a result
If i had only the 2nd row of table 11 i need to get Pick X2 as a result
Now i need the count if to inform me that there multiple matches and so I need to sort it manually

The logic would also be like this: Each row in table 11 could become a kind of unique id. In example "ΑΓΓΠ&4,15" could be the id for row 1 and then "ΑΓΓΠ&0,15" the id for row 2.
In table 12 we always have full data entry, so this is a wider id than table's 11.
I need to answer this: Is there any id of table 11 matching any string part of tables 12 ids?
I tried to go with this E3&F3&....but i face many issues.
One of them is this

Betakos Profit Analyzer.xlsb
DEFGHIJKLMNOPQRS
1
2League1EntosXeKTos21.%X.%2.%Odds ClassIndexPickPickCodeFromTill
3ΑΓΓΠ4,15ΑΓΓΠ4,15111/1/201531/12/2099
4ΑΓΓΠ4,15ΑΓΓΠ4,15X2321/1/201531/12/2099
5
Portfolio
Cell Formulas
RangeFormula
O3O3=[@League]&[@1]
O4O4=[@League]&[@[1.%]]
Q3:Q4Q3=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")


For both rows in table 11 i would get ΑΓΓΠ4,15 but this is not the same, because first 4,15 is for label "1" and the other 4,15 is for label "1.%"

Thank you all just for reading this...
 

Attachments

  • Mrexcel1.png
    Mrexcel1.png
    63.3 KB · Views: 4

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Put separators in like =[@League]&"|"&[@1]&"|"&[@Entos]&"|"&[@2]&"|"&[@1.%]&"|"&[@X.%]&"|"&[@2.%]&"|"&[@Odds Class]
 
Upvote 0
Oh God help me!! It has to be the most complex task ever came across. Ok Lets use your idea about the "|" separators. I will just focus on what i need the final "outcome" to be.

Betakos Profit Analyzer.xlsb
DEFGHIJKLMNOPQRSTUV
1
2League1HomeXAway21.%X.%2.%Odds ClassIndexPickPickCodeFromTillStakes TargetProfit Target
3EPL4,15|EPL|4,15|||||||||111/1/201531/3/202152
40,15|||||||0,15||||X31/1/201531/12/2099
5EPL3,502,37|EPL|||3,5||||||2,37|Over2,5261/1/201531/12/2099
6
7League1HomeXAway21.%X.%2.%Odds ClassIndexDatePick
8EPL4,15Team H3,5Team A1,80,150,250,62,37|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|28/2/20211&X&Over2,5
9
Portfolio
Cell Formulas
RangeFormula
O8,O3:O5O3="|"&[@League]&"|"&[@1]&"|"&[@Home]&"|"&[@X]&"|"&[@Away]&"|"&[@2]&"|"&[@[1.%]]&"|"&[@[X.%]]&"|"&[@[2.%]]&"|"&[@[Odds Class]]&"|"
Q3:Q5Q3=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")



In row 8 "table 12" we have the game we need to get Pick Indicators from "table 11" above.
So the separators create 10 data boxes for each row in table 11==> |EPL| is data box No1 and so on....

Now if the data in each row in table 11 are exactly match with the respectively ones in rows in table 12 then i need a multiple INDEX functions to get me the picks as shown in cell Q8 in table 12
Now in my example, each row in table 11 has an absolute match of data in table's 12 row
This means first row of table 11 has 2 filled data boxes==> |EPL|&|4,15|. These 2 data boxes exist too in table 12 row, in the exact positions 1 & 2
2nd row of table 11 has 1 filled data box ==> |0,15| which is the 7th position. In table 12 position 7 also has |0,15|==> Again exact match
3rd row of table 11 has 3 filled data boxes ==> |EPL|&|3,5|&|2,37| in positions 1, 4, 10. In table 12 the exact same positions has the exact same data.

So each of the 3 rows in table 11 has an exact match in table's 12 row of data. So all 3 Picks are valid and need to be placed in cell Q8.
*Important note. In table 11, columns R & S are date restrictions that must apply according to the match date (cell P8 in table 12)

Example of non exact match.

Betakos Profit Analyzer.xlsb
DEFGHIJKLMNOPQRSTUV
1
2League1HomeXAway21.%X.%2.%Odds ClassIndexPickPickCodeFromTillStakes TargetProfit Target
3EPL4,15|EPL|4,15|||||||||111/1/201531/3/202152
40,15|||||||0,15||||X31/1/201531/12/2099
5EPL3,502,002,37|EPL|||3,5||2||||2,37|Over2,5261/1/201531/12/2099
6
7League1HomeXAway21.%X.%2.%Odds ClassIndexDatePick
8EPL4,15Team H3,5Team A1,80,150,250,62,37|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|28/2/20211&X&Over2,5
9
10
Portfolio
Cell Formulas
RangeFormula
O8,O3:O5O3="|"&[@League]&"|"&[@1]&"|"&[@Home]&"|"&[@X]&"|"&[@Away]&"|"&[@2]&"|"&[@[1.%]]&"|"&[@[X.%]]&"|"&[@[2.%]]&"|"&[@[Odds Class]]&"|"
Q3:Q5Q3=IFERROR(INDEX(t_pickconvertor,MATCH([@Pick],t_pickconvertor[Pick],0),MATCH(t_pickconvertor[[#Headers],[PickCode]],t_pickconvertor[#Headers],0)),"")


In above example 3rd row of table 11, is not exact match for the row in table 12 cause data box in position 6 (labeled "2"), has no exact match in row of table 12. So in this case Pick Over2.5 must not show up in cell Q8




GlennUK said:
Put separators in like =[@League]&"|"&[@1]&"|"&[@Entos]&"|"&[@2]&"|"&[@1.%]&"|"&[@X.%]&"|"&[@2.%]&"|"&[@Odds Class]
Click to expand...
 
Upvote 0
Your requirement is difficult to understand ... you seem to be wanting to do a match per field/content from one table to the other, but all in one formula, which I'm not sure is possible. What version of Excel are you trying to do this in?
 
Upvote 0
GlennUK said:
Your requirement is difficult to understand ... you seem to be wanting to do a match per field/content from one table to the other, but all in one formula, which I'm not sure is possible. What version of Excel are you trying to do this in?
Click to expand...
2013 version
Ok lets talk about it like this. Can Excel spot exact text strings of a larger txt into smaller ones?
|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37| is a full index

|EPL|4,15||||||||| is a partial index.

Can Excel tell us that "|EPL|4,15|||||||||" is an exact substring of "|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|" ?
And the reason "|EPL|4,15|||||||||" is an exact partial string of "|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|"

is because EPL and 4,15 are in the exact same spots in our example spots 1&2

In example string |EPL|||4,15||||||| IS NOT an exact substring of "|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|"
because |4,15| is in spot 2 in the full index , but in spot 4 in the substring.


|1|2|3|4|5|6|7|8|9|10| is the structure of the strings
 
Upvote 0
Look at this:

Book3 (version 1).xlsb
ABCD
1|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|EPL|4,15|||||||||EPL|4,15|*|*|*|*|*|*|*|*TRUE
2|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|EPL||4,15|||||||||EPL|*|4,15|*|*|*|*|*|*|*|*FALSE
Sheet8
Cell Formulas
RangeFormula
C1:C2C1=SUBSTITUTE(SUBSTITUTE(B1,"||","|*|"),"||","|*|")&"*"
D1:D2D1=NOT(ISERROR(SEARCH(C1,A1)))
 
Upvote 0
Dear
GlennUK said:
Look at this:

Book3 (version 1).xlsb
ABCD
1|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|EPL|4,15|||||||||EPL|4,15|*|*|*|*|*|*|*|*TRUE
2|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|EPL||4,15|||||||||EPL|*|4,15|*|*|*|*|*|*|*|*FALSE
Sheet8
Cell Formulas
RangeFormula
C1:C2C1=SUBSTITUTE(SUBSTITUTE(B1,"||","|*|"),"||","|*|")&"*"
D1:D2D1=NOT(ISERROR(SEARCH(C1,A1)))
Click to expand...
Glenn that was a good starting point! But there is still some work to be done (if possible).
We have 2 tables as below

Book1.xlsb
ABCDEFGHIJKLMNOPQRS
1
2LeagueHomeOddsHomeTeamDrawOddsAwayTeamAwayOddsHome%Draw%Away%ClassIdSUBSTTFCellsPickMatchCellPicks
312,003,04,005,06,007,008,009,0010,00|1|2|3|4|5|6|7|8|9|10||1|2|3|4|5|6|7|8|9|10|FALSE|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|$Q$31&Over2,5
4EPL4,15|EPL|4,15||||||||||EPL|4,15|*|*|*|*|*|*|*|*|TRUE$Q$31|EPL|4,15|Team H|3,5|Team A|1,5|0,15|0,25|0,6|2,3|$Q$4 
5EPL1,80|EPL|||||1,8||||||EPL|*|*|*|*|1,8|*|*|*|*|TRUE$Q$3Over2,5|EPL|4,8|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|$Q$5 
Sample
Cell Formulas
RangeFormula
K3:K5K3="|"&[League]&"|"&[HomeOdds]&"|"&[HomeTeam]&"|"&[DrawOdds]&"|"&[AwayTeam]&"|"&[AwayOdds]&"|"&[Home%]&"|"&[Draw%]&"|"&[Away%]&"|"&[Class]&"|"
L3:L5L3=SUBSTITUTE(SUBSTITUTE([@Id],"||","|*|"),"||","|*|")
M3:M5M3=NOT(ISERROR(SEARCH([@SUBST],schedule[Match])))
R3:R5R3=CELL("address",[@Match])
S3:S5S3=MultipleLookupNoRept([@Cell],picksid[[Cells]:[Pick]],2)


Now lest start with scenario 1 which is the simplest, in which a "match" from table "schedule" has multiple matchups in table "picksid"
In the above example first match meets the criteria in both id2 & id3. Now if beside the TRUE, FALSE we get in column "M", we could get the cell address of the matching, as i have mark it manually in the example, i have found the solution to get both picks for the match in cell S3 with the help of the below code


VBA Code: 
Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & "&"
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function


So this is the example where games match multiple pickids=> 1 game matches 2 ids in the above example.

Now the second scenario is the tougher one. It is the scenario where a pickid matches multiple games.

This is a table example of what i would wish to get as a final outcome. Note that the results are hand written (yellow font) for explaining purposes.

Book1.xlsb
ABCDEFGHIJKLMNOPQRS
1
2LeagueHomeOddsHomeTeamDrawOddsAwayTeamAwayOddsHome%Draw%Away%ClassIdSUBSTTFCellsPickMatchCellPicks
312,003,04,005,06,007,008,009,0010,00|1|2|3|4|5|6|7|8|9|10||1|2|3|4|5|6|7|8|9|10|FALSE|EPL|4,15|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|$Q$31&Over2,5
4EPL4,15|EPL|4,15||||||||||EPL|4,15|*|*|*|*|*|*|*|*|TRUE$Q$3,$Q$41|EPL|4,15|Team H|3,5|Team A|1,5|0,15|0,25|0,6|2,3|$Q$41
5EPL1,80|EPL|||||1,8||||||EPL|*|*|*|*|1,8|*|*|*|*|TRUE$Q$3,$Q$5Over2,5|EPL|4,8|Team H|3,5|Team A|1,8|0,15|0,25|0,6|2,37|$Q$5Over2,5
Sample (2)
Cell Formulas
RangeFormula
K3:K5K3="|"&[League]&"|"&[HomeOdds]&"|"&[HomeTeam]&"|"&[DrawOdds]&"|"&[AwayTeam]&"|"&[AwayOdds]&"|"&[Home%]&"|"&[Draw%]&"|"&[Away%]&"|"&[Class]&"|"
L3:L5L3=SUBSTITUTE(SUBSTITUTE([@Id],"||","|*|"),"||","|*|")
M3:M5M3=NOT(ISERROR(SEARCH([@SUBST],schedule8[Match])))
R3:R5R3=CELL("address",[@Match])


Any work arounds for this?

Thanks to anyone in advance
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
110
FormulasOnly
F
S
  • Solved
Convert to dynamic array
Replies
7
Views
136
sharshra
S
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
111
Nicha
N
S
  • Solved
SEARCH problem?
Replies
7
Views
254
sharshra
S
S
  • Solved
Problem with XLOOKUP on array
Replies
3
Views
184
Fluff
Fluff

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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