Assistance Needed: JOIN and COLLECT Formula Returning Blank Values or Duplicates

System

This discussion was created from comments split from: INDEX MATCH on multiple value cell.

Kelly Moore

Hey @MazU

The DISTINCT function will eliminate your duplicates

=JOIN(DISTINCT(COLLECT({Building Database Range 3}, {Building Database Range 4}, CONTAINS(@cell, [Site Name]@row))), ",")

Kelly

MazU

@Paul Newcome Hope you're well Paul

I tried using your formula but the values is blank

i tried it with contains but keep getting dupes (see second screenshot)

=JOIN(COLLECT({Building Database Range 3}, {Building Database Range 4}, CONTAINS(@cell, [Site Name]@row)), ",")

please can you assist me

Kelly Moore

Hey @MazU

The DISTINCT function will eliminate your duplicates

=JOIN(DISTINCT(COLLECT({Building Database Range 3}, {Building Database Range 4}, CONTAINS(@cell, [Site Name]@row))), ",")

Kelly

MazU

@Kelly Moore Thank you very much! Not sure why i didn't think of the Distinct function but it seems to be working like a charm.