I have been trying to use Split and Filter to display ONLY the duplicate zip codes within a column list of zip codes. Column A shows location name (irrevelant) and column B is a comma-separated list of zips. In column C I want only the zip codes that are a duplicate of the entire column B to be shown.

In this case above, in C2 and in C3 the zip code '37066' would be displayed.

I currently have the following in cell C2 =filter(split(B2,","),arrayformula(countif(split(B2,","),B2)>1))

But this is not working.

CodePudding user response：

SPLIT column B by , and pass the split array to COUNTIF as both range and criterion to get the count of each element in the array. IF count>1, return the array, else return nothing.

Sample:

=ARRAYFORMULA(
  LAMBDA(
    s_arr,
    IF(
      COUNTIF(s_arr,s_arr)>1,
      s_arr,
    )
  )(SPLIT(B2:B6,","))
)

CodePudding user response：

Place this in a cell somewhere which will be your helper cell to have a master list of all zip codes: (b being the column which has all your zip codes)

=textjoin(",",1,B2:B)

Then enter this in the header (usually row 1) of your data: (replace $A$6 with the cell you out the helper cell above and replace $b$2:$b$5 with your zip column)

={"dupe zips";arrayformula(byrow(Split($B$2:$B$5, ",",0,1),lambda(z,textjoin(",",1,iferror(if(find(z,substitute($A$6,z,"",1)),z,"z"),"")))))}