Using INDEX/COLLECT to return multiple values

RaffyM

Hello @Genevieve P.

In my current work sheet, there is already 1000++ unique part numbers and growing, that's why I was hoping there is a formula to get the top 20 part numbers within my worksheet.

It seems my best option is to create a second sheet to get first the unique part numbers then their ranks.

After sorting Rank column, the numbering in Number Helper column disarrange. This wouldn't affect whenever there is new part number entered in my work sheet, correct?

Also, would you please teach me how to do auto sorting? Can it be done here in my second sheet?

Thank you and Happy Friday!

Genevieve P.

Hey @RaffyM

The number order in your helper column won't matter, as long as you have 1 - 600 (or however many) listed as unique numbers in that column.

If you'd prefer to keep your sheet sorted with 1 - 600 in your Number Helper in order, then what you would do is create a Row Report off of this sheet:

• The Row Report would use this second sheet with the formulas as the Source.
• You would then filter by if the Rank is less than 21.
• Once you have the filtered rows in your Report, you can then sort the Report by the Rank column.

Sheets don't currently auto-sort, but the Report will! Here's a webinar on building a report: SmartStart: Reporting

Happy Friday to you as well 🙂

RaffyM

Thank you again @Genevieve P.

I'll probably just do manual sorting of this new sheet, because I have already secondary sheet where all my formulas for dashboard reporting is in placed. Having fourth sheet for a single project may be too much for me to tract. 😅

Till next time. 😉 Enjoy your weekend!

Shreeg

Hi @Genevieve P.

I have similar issue, i need to find all the training assigned to the resource and it should be in 1 row only. If 1 user have 3 training then it will be in 1 row.

In below screenshot where same name but have 3 different courses, i used VLOOKUP but it gives me 1 name only I need three of them. Thank you for Help

Genevieve P.

Hi @Shreeg

Try using JOIN(COLLECT instead of Vlookup!

Here's the basic structure:

=JOIN(COLLECT({Column to bring back}, {Column with matching value}, [Matching Value]@row), ", ")

See: Gather all matching content into one cell

Cheers,

Genevieve

Shreeg

Thank you @Genevieve P.

But can you please elaborate little bit more, According to my case.

I tried the formula you gave but it throwing invalide error.

Thanks in advance

Genevieve P.

Hi @Shreeg

Can you post the formula you tried? Each of {These} are cross-sheet references that you would need to manually build out and select from the sheet you're referencing.

So if you're looking to bring back the "Form Name" according to the "Legal First Name", then it would be something like this:

=JOIN(COLLECT({Form Name Column Reference}, {First Name Column Reference}, [Legal First Name]@row), ", ")

This would mean your current sheet where the formula is placed would need to have a "Legal First Name" column as well, with everyone's names listed.

If you're looking to get data from within the same sheet, then you would structure the formula differently:

=JOIN(COLLECT([Form Name]:[Form Name], [Legal First Name]:[Legal First Name], [Legal First Name]@row), ", ")

Does that make sense?

Cheers,

Genevieve

Shreeg

Thanks for the Help.

It works :)