0

In one column there are mixed sections, which are to be matched with data from other sheet. But as the sections format is different in entire column, not able to match

I have 2 sheets in which i have

Sheet 1 :

Column A         
AB-C,DE   

Sheet 2 :

Column A    
ABCDE   

I have to match this data in both the sheets

  • 1
    Can you add some more info or more data for us to better understand what you are trying to achieve ? – pjmg Apr 22 at 12:11
  • docs.google.com/spreadsheets/d/… I want to match data from sheet 1 and sheet 2 – vaibhav jeevan Apr 22 at 12:18
  • Hey can you provide some example in your sheet ? what do you want to match, data separated by commas ? full cell ? etc.. – pjmg Apr 23 at 8:45
  • Full cell! Update sheet with example – vaibhav jeevan Apr 24 at 9:56
  • 1
    Your data are very hard to work with ... can you at least try to clean the file ? and add more than 1 example : It appears that you have some entries with several matches.. – pjmg Apr 24 at 12:16
0

So my solution isn't perfect du to my limited knowledge in regular expression combined to the messy data.

First, extract the codes from Sheet1!A:A, replacing non digits with an emoji.

REGEXREPLACE(A2,"[^\d*]","🕳️")

Then split each code to create an array

split(REGEXREPLACE(A2,"[^\d*]","🕳️"),"🕳️",true,true)

Now we need to create a regex when we'll try to matches theses codes with Sheet2

"^"&textjoin("|-",true,split(REGEXREPLACE(A2,"[^\d*]","🕳️"),"🕳️",true,true))&"-"

Now use this regex with regexmatch to find correct matches. This formula returns the row if true.

=ArayFormula(if(REGEXMATCH(Sheet2!A3:A,"^"&textjoin("|-",true,split(REGEXREPLACE(A3,"[^\d*]","🕳️"),"🕳️",true,true))&"-"),row(Sheet2!A3:A),))

Finally =textjoin to display results in one cell :

=TEXTJOIN("|",true,
   ArrayFormula(
      if(
            REGEXMATCH(Sheet2!A2:A,"^"&textjoin("|-",true,split(REGEXREPLACE(A2,"[^\d*]","🕳️"),"🕳️",true,true))&"-")
           ,row(Sheet2!A2:A),)
   )
)

Illustration :

enter image description here

| improve this answer | |
  • @vaibhav jeevan dont forget to accept the answer thanks. – pjmg May 3 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.