# JOIN(COLLECT formula

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

• Options

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.

• ✭✭✭✭✭✭
Options

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)), ", ")

• ✭✭✭✭✭✭
Options

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)), ", ")

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options