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.
SPLIT
column B by ,
and pass the s
plit arr
ay 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.
=ARRAYFORMULA(
LAMBDA(
s_arr,
IF(
COUNTIF(s_arr,s_arr)>1,
s_arr,
)
)(SPLIT(B2:B6,","))
)