JOIN/COLLECT To compile Data across columns

Jimmie Fulkerson

I have created a form to use as a replenishment order for consumable materials for technicians. Each item has been assigned it's own column as a single select drop down with varying quantities. The intention is to have the form completed and set with an automation that alerts a group email when a row is added to the sheet. I have seen multiple suggestions but have not been successful in getting this to work. The suggestions I see are compiling rows. The range needs to be from "[Column 1]@row:[Column 72]@row". I need to show the column heard "Part Number", along with the cell value "quantity" in an easy to read notification. Without using formulas the automation sends the entire sheet. The goal would be for the automation to only send the cells that are <> 0. This would shorten the list and greatly reduce misorders. Does anyone have a suggestion for a formula? Would "JOIN(COLLECT" or "COLLECT(IF" be a solution to compile the data into one column and assign that column to the automation? See screenshots below.

"Column 51" is being used as the compile column housing the formula. All columns are not shown, but this gives the idea.

Automation email example. The one time I got a formula to take it repeated the "is not blank" for every value that = 0.

Genevieve P.

Hi @Jimmie Fulkerson

You could use a JOIN(COLLECT formula to only Join together cells that either aren't 0 (while also still including any cells that have text), like so:

=JOIN(COLLECT(Range to Bring Back, Range to Evaluate, OR(@cell <> 0, ISTEXT(@cell))), ", ")

In your case, the Range to Bring Back and the one to Evaluate are the same, so try:

=JOIN(COLLECT([Column 1]@row:[Column 72]@row, [Column 1]@row:[Column 72]@row, OR(@cell <> 0, ISTEXT(@cell))), CHAR(10))

The CHAR(10) at the end creates a line break in your cell so they appear as a list. You could also use a comma, like I have in the first example.

However this formula can only bring together the details in the cell content from those columns, it won't include the Column Name if that's what you meant by the Part Number?

Cheers,

Genevieve

Genevieve P.

Hi @Jimmie Fulkerson

You could use a JOIN(COLLECT formula to only Join together cells that either aren't 0 (while also still including any cells that have text), like so:

=JOIN(COLLECT(Range to Bring Back, Range to Evaluate, OR(@cell <> 0, ISTEXT(@cell))), ", ")

In your case, the Range to Bring Back and the one to Evaluate are the same, so try:

=JOIN(COLLECT([Column 1]@row:[Column 72]@row, [Column 1]@row:[Column 72]@row, OR(@cell <> 0, ISTEXT(@cell))), CHAR(10))

The CHAR(10) at the end creates a line break in your cell so they appear as a list. You could also use a comma, like I have in the first example.

However this formula can only bring together the details in the cell content from those columns, it won't include the Column Name if that's what you meant by the Part Number?

Cheers,

Genevieve

Jimmie Fulkerson

Genevieve,

Thank you for the reply. I tried the above formula's and ran into two different errors. The first one I got was "Incorrect Argument" Below is what I tried. Location is Column 1, 1CKH9 is Column 72 :)

=JOIN(COLLECT([Location]@row:[1CKH9]@row, [Location]@row:[1CKH9]@row),OR(@cell <> 0, ISTEXT(@cell))), ", ")

I tried the other option as well and kept receiving the "Unparseable" error.

=JOIN(COLLECT(Location@row:[1CKH9]@row, Location@row:[1CKH9]@row), OR(@cell <> 0, ISTEXT(@cell))), CHAR(10))

Do you have any recommendations?

Genevieve P.

Hi @Jimmie Fulkerson

It looks like you're closing off the COLLECT function too soon, you have the closing ) after listing two ranges, but before the criteria.

Try:

=JOIN(COLLECT(Location@row:[1CKH9]@row, Location@row:[1CKH9]@row, OR(@cell <> 0, ISTEXT(@cell))), CHAR(10))

Jimmie Fulkerson

Of course....Thank you so much. That worked perfectly.

Genevieve P.

No problem! Glad I could help 🙂