Using INDEX/COLLECT to return multiple values

JLC

Looking for the proper way to return multiple values using INDEX/COLLECT. I'm assuming JOIN needs to be in there but can't get it to work! Both without and with JOIN, the formula still produces the first value only. This formula matches a project number from one sheet to another, and is supposed to return ALL of the resources assigned to that project (displayed in the source sheet as one resource per row, with all rows having the project number field filled out).

Here's what I'm trying to work with:

=JOIN((INDEX(COLLECT({source sheet resource name}, {source sheet project number}, [Project number]@row), 1)), ", ")

Note that this is in a contact column and ideally I'd like the returned values to be contacts, not flat text...but thinking that may not be possible. Grateful for any input!

Genevieve P.

Hi @Jaye Tatone

The INDEX function is what's causing your output to bring only one value back. You can actually just jump right into a JOIN(COLLECT without INDEX at all. Try this:

=JOIN(COLLECT({source sheet resource name}, {source sheet project number}, [Project number]@row), ", ")

You are correct that currently this type of formula wouldn't be able to populate contacts; the JOIN(COLLECT will bring through the values as text. Keep in mind that if the same resource is assigned to the same project in two different rows you'd have their name brought through twice in this cell.

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Jaye Tatone

The INDEX function is what's causing your output to bring only one value back. You can actually just jump right into a JOIN(COLLECT without INDEX at all. Try this:

=JOIN(COLLECT({source sheet resource name}, {source sheet project number}, [Project number]@row), ", ")

You are correct that currently this type of formula wouldn't be able to populate contacts; the JOIN(COLLECT will bring through the values as text. Keep in mind that if the same resource is assigned to the same project in two different rows you'd have their name brought through twice in this cell.

Let me know if this works for you!

Cheers,

Genevieve

Tracey Katz

@Genevieve P Is there a way to use JOIN-COLLECT to populate contacts? I am trying to do something similar but need to pull in contacts and not text. Or is there another function/function combo I can use to accomplish this?

Genevieve P.

Hi @Tracey Katz

Would you be able to explain your process a little more, perhaps with screen captures of both sheets (but block out any sensitive data)?

There isn't a way to bring in multiple, separate contacts and join them into one Contact Column cell through a formula, but if you already have multiple contacts in one cell, there may be ways you can copy that full cell over to another sheet, or search within that cell.

mtk5200

Hi @Genevieve P.

Is it possible to use =Join(Collect to bring back multiple values but instead of a comma delimited list, have the values populate in the same column on separate rows? I've using join/collect in conjunction with a 'Text to Columns' option like excel and it doesn't appear to be an option.

I'm trying to pull into a sheet, from a separate sheet, all of the project names that are associated with one person's name. The catch is, multiple people can be assigned to one project so I can't use a report for this.

Thanks for your help,

Morgan

Genevieve P.

Hi @mtk5200

The JOIN(COLLECT can only join data into one cell versus breaking out the data down rows.

However there may be a way we can do this. Do you know how many possible project names there could be? For example, up to 10?

We could use an INDEX(COLLECT formula to bring across the first match, then in the second row change the formula to look for the second match, and so on.

For example:

=INDEX(COLLECT({Project Names}, {People Assigned}, HAS(@cell, Person@row)), 1)

The 1 at the end tells the formula to bring back the first row.

Then in your second row for that user, update it to:

=INDEX(COLLECT({Project Names}, {People Assigned}, HAS(@cell, Person@row)), 2)

If you know the max number of Projects that could be assigned to someone, you could set up your sheet ahead of time and have the numbers in the cell. Then you can reference the cell instead of writing 1 or 2. Here's an example of what I mean:

Cheers,

Genevieve

Marcin

Hi @Genevieve P.

I'm using your last example which works fine in the give sheet. Trying to modify that to reference another sheet but getting unparseable

Surely I'm wrongly inserting reference to another sheet. Any hint what's wrong with references here?

=INDEX(COLLECT({Integrated Launch Plan Range 1}[Task Name]:[Task Name], {Integrated Launch Plan Range 1}[Priority Focus Flag]:[Priority Focus Flag], HAS({Integrated Launch Plan Range 1}@cell, "Local Priority")), 3)

Best

Marcin

Genevieve P.

Hi @Marcin

There are a couple of syntax errors here.

The first thing you list in the COLLECT function is the column you want to bring data back from. Then you list each Column and Criteria after it.

So in your case, Range 1 should be the Column to bring back data.

=INDEX(COLLECT({Integrated Launch Plan Range 1}

Then if you're looking for a specific Task Name, Range 2 would be the Task Name column in the other sheet:

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2}

And if you're searching for the Task Name in the cell in this same row where you're writing the formula, the reference is [Task Name]@row

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2}, [Task Name]@row

Next we list the multi-select column, which it sounds like is called "Priority Focus Flag" in the other sheet. This would be range 3:

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2}, [Task Name]@row, {Integrated Launch Plan Range 3}

And then what you're looking for in that multi-select would be "Local Priority", you can use HAS(@cell to search for it in the previously stated range:

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2}, [Task Name]@row, {Integrated Launch Plan Range 3}, HAS(@cell, "Local Priority")), 1)

You always need a 1 at the end because that tells the INDEX function what row to bring back. Does that make sense?

If this formula doesn't work from you, please post screen captures of your current source sheet and the sheet where you're writing the formula, identifying what columns you want to Match and what you want to Bring Back, but please delete out sensitive data.

Cheers!

Genevieve

SaMMY123

Hi @Genevieve P.

How do I bring the Asset numbers associated with one GPS number together? I tried executing the formula as per your previous comment, but it's not working. Please assist me here.

Genevieve P.

Hi @SaMMY123

If you're looking to bring all related content into one cell, you'll want to use JOIN(COLLECT instead, to join those values into one cell.

Try this:

=JOIN(COLLECT({Assets and Artifacts Column}, {GPS Column Reference}, [GPS#]@row), ", ")

See:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations?#toc-gather-all-matching-content-into-one-cell

Cheers,

Genevieve

SaMMY123

Thank you for responding,@Genevieve P. I'm looking to bring all the Asset names from the master sheet(Sheet-1) to the template sheet(Sheet-2), one below the other. So 1 GPS number in the 1st column and n Asset numbers in the 2nd column. I tried using INDEX and COLLECT function together and its just not working.

Genevieve P.

Hi @SaMMY123

Have you tried my previous suggestion / comment? This requires another column with numbers running down it:

https://community.smartsheet.com/discussion/comment/313320#Comment_313320

RaffyM

Hello @Genevieve P.

If you may, I need your assistance as well. I have worksheet with thousands of rows and it grows daily as my colleague continuously entering data into the worksheet. In my worksheet, there is Part Number columns where a part number is entered once or multiple times (which is fine because our goal is to determine which part number is problematic). Screenshot of my worksheet is below. Part number 86085576 is entered 4 times in this example so this is top 1, 86069906 is #2. Because there are thousands of part numbers that are entered, I'd like to get the top 10 part numbers with most entered.

Thank you in advance

Genevieve P.

Hey @RaffyM

I assume you're trying to keep columns/formulas out of this source sheet if it's getting quite large, is that correct?

If so, do you have a second sheet that lists all the unique Part Numbers down one column? What I would do is in this second, reference sheet, set up a COUNTIF formula to count how many times that part appears in your intake sheet:

=COUNTIF({Part Number Column}, @cell = [Part Number]@row)

Then once you have a count for each of the parts, you could use a RANKEQ function to find the rank (or top number of parts). For example:

=RANKEQ(Count@row, Count:Count, 0)

Cheers,

Genevieve

RaffyM

Hello @Genevieve P.

Thank you for your time checking my concern.

First, I don't have a second sheet. I only created a second sheet (test sheet) so I could practice around some formulas hoping to get the correct one.

Second, in my original work sheet, I already added helper columns for Count and Ranking. Both formulas I have for these two columns work perfectly fine. Part Number column has all the part numbers that have been entered, could only be once or multiple times. That's why in the Ranking columns in counts all rows of the same part number, before assigning the next rank. Example 86044697 is Rank 183 and since this number was entered 31 times, the next rank will be 214th, but it should be 184th. Hopefully I explained it clearly.

Anyway, under the Top 20 column, I'd like to have the part number listed based on its count or ranking. If this can't be done in the same work sheet, it's fine. I can create second sheet just get those top 20 part numbers.

Thanks

Genevieve P.

Hi @RaffyM

Thanks for sharing more of your sheet!

Since you're creating a COUNT down every single row, including duplicate rows, then your RANK formula will be including every row in its formula as well. I would actually suggest a second sheet - this way you can have each unique Part Number in a row, ensuring there are no duplicates.

If you don't know all your part numbers, you can get this as well by using an INDEX formula to find the DISTINCT value and return that based on the row number.

This would require you to add a column with number 1 - XXX (how many you estimate you have plus extra to allow for growth). Then you can reference that column in your formula:

=INDEX(DISTINCT({Part Number Sheet 1}), [Number Helper]@row)

Keep in mind the number at the end of the INDEX tells the Index to bring back the "first match" or "second match" so it's dependent on your source sheet order. This means your numbers may rearrange as new rows come in (if they're coming in from the top-down).

Then that way your RANK will be unique to just that one value, instead of every row. You could Sort the sheet then by the Rank so your 1 - 20 are at the top. This order will change as time goes on, so if needed, you could build a Report that filters by the top 20 and auto-sorts it every time.

Cheers,

Genevieve