0

I have a spreadsheet with 4 x 1 table. Each cells contains a list of names(separated by comma). Now I would like to extract names that appear in all list and put them all in a new list(also separated by comma) in column F. In the following example 'Michael,John' should appear in F1. Is there any solution?

Example:

         A                          B                         C                       D                         E                            F
Michael,John,Ann      Michael,John,Peter,Patrick      Michael,John,Peter      Michael,John,Alex       Michael,John,Jason,Alex
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Luo Ocean
  • 3
  • 2
  • Welcome to SO. I read the question wrongly since the duplicates focus on unique values instead of values that appear in all cells. So the forwarded duplicates aren't going to answer the question (they may still help). Even so, the question is off-topic. See [ask] a question with a [mcve] and just see [how much effort is required](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) from your end before posting. – JvdV Apr 12 '22 at 06:13

2 Answers2

1

For O365 users on the Insider channel:

=LET(ζ,A1:E1,κ,TEXTSPLIT(TEXTJOIN(",",,ζ),","),IFERROR(TEXTJOIN(",",,UNIQUE(FILTER(κ,BYCOL(κ,LAMBDA(a,SUM(N(κ=a))))=COLUMNS(ζ)),1)),""))

This assumes that a given name will never occur more than once within the same cell.

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
0

I recommend that you copy the paste special as transpose. Then under the Data tool set there is an icon called text to columns. This will separate the data by commas.

Shane S
  • 1,747
  • 14
  • 31