JOIN(COLLECT formula

System

This discussion was created from comments split from: Using INDEX/COLLECT to return multiple values.

Oemer Faruk Aslantas

Hi community,

I have a similiar question:

So, currently i am using a formula:

=SUM(COLLECT([additional cost]:[additional cost], [Destination]:[Destination], [Destination]@row, [Request entered by]:[Request entered by], [Request entered by]@row, [Created (date)]:[Created (date)], <([Created (date)]@row + 1), [Created (date)]:[Created (date)], >([Created (date)]@row - 8)))

This sums the total cost of same Requester within 7 days. Is there a way to add another column to get the material no. of all collected requests? like:

= JOIN(COLLECT([material no]:[material no], [Destination]:[Destination], [Destination]@row, [Request entered by]:[Request entered by], [Request entered by]@row, [Created (date)]:[Created (date)], <([Created (date)]@row + 1), [Created (date)]:[Created (date)], >([Created (date)]@row - 8))))

ideally seperated by a comma or space. I haven't found a way to solve this yet.

Thank you in advance!

Paul Newcome

Try this...

=JOIN(COLLECT([additional cost]:[additional cost], [Destination]:[Destination], [Destination]@row, [Request entered by]:[Request entered by], [Request entered by]@row, [Created (date)]:[Created (date)], <([Created (date)]@row + 1), [Created (date)]:[Created (date)], >([Created (date)]@row - 8)), ", ")

Leibel S

@Paul Newcome

I think @Oemer Faruk Aslantas is looking to join and collect on the material not the cost.

See below formula for that:

= JOIN(COLLECT([material no]:[material no], [Destination]:[Destination], [Destination]@row, [Request entered by]:[Request entered by], [Request entered by]@row, [Created (date)]:[Created (date)], <([Created (date)]@row + 1), [Created (date)]:[Created (date)], >([Created (date)]@row - 8)), ", ")

Paul Newcome

@Leibel S You are right. I meant to update that before posting. Good catch. Thank you.

Oemer Faruk Aslantas

Thank you all! Meanwhile I have found a workaround but the way you describe it @Paul Newcome is awesome and saves me 2 columns :D

Paul Newcome

@Oemer Faruk Aslantas What was the solution you ended up implementing?

Oemer Faruk Aslantas

I added a new column:

= "|" + [material no]@row + "|"

--> then collected that column.

Result was like: | material no 1 || material no 2 || material no 3 |