Comparing two comma separated lists to get matching values in google sheets-CodePudding

I'd like to compare two comma-separated lists, and get the matching values in google sheets. All values are positive numbers and include single and double digit numbers only. E.g,

Data         Result
1,2,3          2,3
2,3,10

I do this in excel with a macro code that I have, now I'd like to do the same in google sheets. Can i get some help to do this, please.

CodePudding user response：

You can use below formula-

=TEXTJOIN(",",TRUE,QUERY(FLATTEN(SPLIT(A2,",")),"select Col1 where Col1 matches '" & SUBSTITUTE(A3,",","|") & "'"))

CodePudding user response：

Try

=TEXTJOIN(",",,query(arrayformula({unique(flatten(split(A2:A,","))),countif(flatten(split(A2:A,",")),unique(flatten(split(A2:A,","))))}),"select Col1 where Col2>1"))