• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for extracting the common value from two data

delvillardennis

delvillardennis

Member
Hello All,


Please help me to have a formula that would extract the common value between two data. For example below, the formula will extract 1-2-3 in B1 taken from two data in A1:A2


# A B

1 1-2-3-4-5-6 1-2-3

2 1-2-3-7-8-9
 
Delvillardennis


I hope you posted this to win a bet as I think I've fried my brain


This assume you have two text strings in Cells A1 and A2 and you want the common values starting from the left side of the text


Code: 
=LEFT(A1,MIN(IF((ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2))))*(MID(A1,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1) <> MID(A2,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1))),(ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2))))*(MID(A1,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1)<> MID(A2,ROW(OFFSET($A$1,,,MIN(LEN(A1), LEN(A2)),1)),1))),FALSE))-2)
Ctrl+Shift+Enter
 
OMG!!!


what an effort Hui!!! you are awesome.


just one improvement: the formula is resulting in one character less than common characters. I think we need to place -1 instead of -2 at the last segment of formula.
 
Hi Hui,


Actually, I have an excel file of the lotto results. I posted the excel question for me to know if my bet matched any of the winning numbers :) So, I made some modification in your formula if the two text strings matched:


={IF(A1=A2,"JACKPOT",LEFT(A1,MIN(IF((ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2))))*(MID(A1,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1)<>MID(A2,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1))),(ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2))))*(MID(A1,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1)<>MID(A2,ROW(OFFSET($A$1,,,MIN(LEN(A1),LEN(A2)),1)),1))),FALSE))-2))}


However, if the two text strings in Cells A1 and A2 are as follows, the formula gives the result 1-2-3 instead of 1-2-3-5-6. Maybe needs further modification?


# A B

1 1-2-3-4-5-6 1-2-3-5-6

2 1-2-3-5-6-7
 
Hi ,


Can you try this ?

[pre]
Code: 
=LEFT(A1,MAX(IF(IFERROR(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))=LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))),0),ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(A2)))))))
[/pre]
entered as an array formula.


This will check the two strings looking for a match from the left of each string ; if the strings match in between , this formula will not find it.


Narayan


P.S. I had typed this in earlier , before I saw your second post. This formula will not resolve your problem.
 
Hi Narayank991,


Thanks for helping me. My goal is to match the two strings including in between. In A1, it contains the lotto results drawn on a certain day and in A2 (or in A3,A4...), my bets.


For instance, see another sample below as result in B2 for the bet in A2:


# A B

1 5-8-10-20-24-55

2 10-15-20-21-24-47 10-20-24
 
Hi Dennis ,


I think your best bet is to do it using VBA ; but I am not clear on what is the exact requirement.


If we take your example , suppose the result for a particular day is :


5-8-10-20-24-55


what does this mean ? Are these 6 numbers , drawn out of the hat , specifically in this order ? Is a sequence of numbers such as 5-8-20-10-24-55 supposed to match the result ?


If not , then what is the extent of matching to be done ? Will a sequence such as 5-15-10-20-24-25 be a match ? Should this return 5-10-20-24 ?


In other words , you are looking to return the largest sub-string which matches a given string , with the words in the same order as in the string being searched. For example , if we take two normal English sentences :


He is an honest man

He is a tall man


you want that the following sub-string should be returned :


He is man


Can you confirm ?


Narayan
 
Hi NarayanK991,


The 6 numbers are in random order taken from the lotto website. What I did is to re-arrange it from lowest to the highest sequence. So, to answer your questions...


"xxxIf not , then what is the extent of matching to be done ? Will a sequence such as 5-15-10-20-24-25 be a match ? Should this return 5-10-20-24 ?.xxx" ans: Yes, please...


"xxxHe is an honest man

He is a tall man


you want that the following sub-string should be returned :


He is man


Can you confirm ?xxx" ans: Yes, I confirm.


Thank you so much for the responses.
 
Hi Dennis ,


If , as you say , the numbers were in random order to start with , then shouldn't the order be irrelevant ?


Suppose the numbers were , to take your example , 5 , 8 , 10 , 20 , 24 , 55 , then the matching should be on the presence / absence of the same numbers , without any regard to the order.


So what we are looking for is :


1. Take any one input string , and using the hyphen character "-" as a delimiter , isolate the individual numbers.


2. Do the same for the second input string.


3. Compare the two sets of individual numbers , and return the string which consists of the maximum number of matches between the two sets of numbers , irrespective of the order in which they occur in the two strings.


Is this OK with you ?


Narayan
 
Hi NarayanK991,


Yes it is OK. There is no preference in the order and matching should be on the presence/absence of the same numbers. I just want to know the numbers from my bets (i.e. sets of 6 numbers) matched to the result of the lotto draw :) Many thanks for the assistance.
 
Why not list the lotto values in separate columns or rows

Then use a simple Match() to check if each value is in the other list?
 
Hi Hui,


I am on that direction. It so happened that when I extracted the results from the lottery website (i.e in text file)and paste it in my worksheet, I thought that there could be a formula of getting the match directly from it without the use of separate columns or rows.
 
You must log in or register to reply here.
Back
Top