Do I need INDEX?

System

This discussion was created from comments split from: Index Collect Formula.

Lauren P

Hello all, I'm not sure if I need an INDEX in my formula. Here is what I'm trying to do in a single cell. I want to list in a single cell the content of TITLE for all the STATUS = Active AND RAIDL_TYPE = Decision, Issue, or Risk. I want to order by Decision, Risk, Issue if possible. I want a carriage return as delimiter.

I'm partly there - this formula does work if I am only looking for "Decision".

=JOIN(COLLECT(Title13:Title50, Status13:Status50, "Active", [RAIDL_Type]13:[RAIDL_Type]50, "Decision"), CHAR(10))

What is throwing me off is the Order By and showing the multiple conditions for Decision, Issues, orRisk. I keep getting errors when I try to add more to this formula. Any suggestions?

bisaacs

Hey @Lauren P,

So I think you can use JOIN/COLLECT like you're doing, you'll just add an OR statement within the criteria portion of your formula:

=JOIN(COLLECT(Title13:Title50, Status13:Status50, "Active", [RAIDL_Type]13:[RAIDL_Type]50, OR(@cell = "Decision", @cell = "Issue", @cell = "Risk)), CHAR(10))

That way it should be looking for an Active Item that either has a Status of Decision, Issue, or Risk (which is I believe what you're trying to do.

Hope this helps!

Paul Newcome

@Lauren P You can use an OR function in the JOIN/COLLECT to include multiple statuses, but the output will not be grouped by status. To group by status, you would need to write out separate JOIN/COLLECT statements for each status and then "add" them together with a line break in the order that you want them in.

=JOIN(COLLECT(first status), CHAR(10)) + CHAR(10) + JOIN(COLLECT(second status), CHAR(10)) + CHAR(10) + JOIN(COLLECT(third status), CHAR(10))