CODEWITHSUNDEEP
google-sheets
Max
Max

Reputation: 940

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

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.

Upvotes: 0

Views: 579

Answers (2)

Harun24hr
Harun24hr

Reputation: 36880

You can use below formula-

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

enter image description here

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15328

Try

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

enter image description here

Upvotes: 1

Related Questions