Join-Distinct-Collect Formula Returning #INVALID DATA TYPE

Jacob Minnich

I'm using a Join Distinct Collect formula to return a list of product numbers from a detailed schedule (formula below). I use this and other formulas to populate a weekly summary sheet.

=JOIN(DISTINCT(COLLECT({Schedule Item}, {Schedule Make Date}, =Date@row, {Schedule Room Name}, =RmName@row)), ", ")

Whenever the schedule has a cleaning placeholder on it, the formula returns #INVALID DATA TYPE. Otherwise it returns a list of the product numbers. It omits blank rows in the schedule that are visual separators. Works perfectly except for when it encounters rows that include cleaning. Screenshots of the summary sheet and the main detailed schedule below. There's a date column to the left on the summary sheet that is omitted from the screenshot.

Anyone have any ideas?

Genevieve P.

Hi @Jacob Minnich

The DISTINCT function can only determine unique values if all of the data types are the same (either all numerical or all text). You're receiving this error because for Rm3, there is both Text and Numerical values to review, which it is unable to do. See the note at the start of the Function description, here:

https://help.smartsheet.com/function/distinct

If you're OK withe excluding any of the "WET CLEAN" cells, you can add in another criteria that filters out those values within the COLLECT function, like so:

=JOIN(DISTINCT(COLLECT({Schedule Item}, {Schedule Make Date}, =Date@row, {Schedule Room Name}, =RmName@row, {Schedule Item}, ISNUMBER(@cell))), ", ")

Cheers,

Genevieve