Search code examples
google-sheetsgoogle-sheets-formula

Display only duplicates from a 2D array


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.

Location Name Zip Code List [Output]Col C Col D Col E
Home 1 37075,37066,37072 37075 37066 37072
Home 2 37066,37072 37066 37072
Home 3 37072,37112,37089 37072
Home 4 37075,37067 37075

In this case above, Column C, D, E are the expected output.

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

But this is not working.


Solution

  • 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,","))
    )