Join/Collect/FIND --> Sorting & Concatenation query

.Leon.

Hi All - firstly thank you for all the prior forum articles around JOIN/Collect and multi-select cells requiring HAS/FIND usage, it gave me a terrific starting point to now ask my question around.

I have a list of employees in a sheet and I manually track competencies that they can perform technical interviews for.

I have information like;

username

tenure (number of days)

interview competency (multi-select column) - Tech1, Tech2, Tech3

calibration status (calibrated / learning)

interview participation rank (RANKEQ formula - the most active interviewer is 1, then goes down in descending order, currently people share the same ranking - I can tweak this if needed so people have unique RANKs)

Using the below formula;

=JOIN(DISTINCT(COLLECT({username}, {IV_Competency}, FIND([Interview competency]@row, @cell) > 0)), ", ")

I am creating 1 cell that has an output like the following of the relevant people that match my criteria;

username1, username2, username3, username4

so it is easy to know who to leverage when we schedule our interviews.

What I would like to achieve is that when I am listing calibrated interviewers;

i) I would like to concatenate their interview participation rank next to their username in the following format; username1 (21), username5 (1), username23 (48)

Is there an easy way I can adjust the existing formula so that the output looks like usernameX (X)?

ii) To make it easy to know who to leverage I would also like to ORDER or SORT the list so that the people that have participated the least in interviewing are written furthest to the left, then the people that have participated most, are furthest to the right. The RANK column currently lists the highest participating interviewers from 1 down, so then it should display something like this;

username1 (99), username5 (55), username23 (33), username188 (12)

Can I create a helper sheet that pre-orders the data in descending/ascending order? So that way when the JOIN/COLLECT pulls from this the data comes in pre-sorted? Or is there a better way to achieve this?

iii) Is there a way to cap the output to maybe 10 results?

Very similar -

What I would like to achieve is that when I am listing learning interviewers;

i) I would like to concatenate their tenure next to their username in the following format; username1 (21), username5 (1), username23 (48)

Is there an easy way I can adjust the existing formula so that the output looks like usernameX (X)?

ii) To make it easy to know who to leverage I would also like to ORDER or SORT the list so that the people that have the highest tenure are written furthest to the left, then the people that have the lowest tenure, are furthest to the right. The tenure column currently lists tenure in days, so then it should display something like this;

username1 (99), username5 (55), username23 (33), username233 (12)

Can I create a helper sheet that pre-orders the data in descending/ascending order? So that way when the JOIN/COLLECT pulls from this the data comes in pre-sorted? Or is there a better way to achieve this?

iii) Is there a way to cap the output to maybe 10 results?

I know for the sorting & capping the number of outputs I can potentially use the LARGE function and preset the order, and just copy the formula as many times as needed to create the desired output, but I was hoping to not have to go that route for now if the lists can be dynamically generated.

Thanks in advance!

Genevieve P.

Hi @.Leon.

It may be easier to help if we could see screen captures of your sheet(s), with sensitive data blocked out or example emails/names in copied sheets, but I'll try to answer each question as you've listed them:

i) I would like to concatenate their interview participation rank next to their username in the following format; username1 (21), username5 (1), username23 (48)

The only way to make sure the correct Participation Rank with each Username is if that data is put together in the source sheet, using a helper column, ex:

=Username@row + " (" + Tenure@row + ")"

Then once you have the data in one cell, showing each Username and Tenure together, you can use this Helper Column in your JOIN COLLECT instead:

=JOIN(DISTINCT(COLLECT({Helper Column with Both}, {IV_Competency}, FIND([Interview competency]@row, @cell) > 0)), ", ")

ii) To make it easy to know who to leverage I would also like to ORDER or SORT the list

The JOIN(COLLECT will bring through the data in the order that it's listed in your source Sheet. This means that yes, if you need the Joined data to appear in order then you could populate a "middle" sheet with the same data, but Sorted in an order that you'd prefer, and then have the JOIN(COLLECT look at that middle sheet instead of the original source.

iii) Is there a way to cap the output to maybe 10 results?

JOIN will JOIN together all the rows that meet your criteria in the COLLECT function. However if you had a helper "middle" sheet as discussed above, what you could do is have another formula that identifies the Row Number in this sheet. Then you can use that as another filter in your COLLECT function, to make sure you only pull the numbers up to 10.

To do this, you'd add a System Auto-Number column to the sheet to generate numbers based on when a row is added. Then you can use the MATCH function to populate the row number from that:

=MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)

Then your JOIN(COLLECT will add another column to look in to and a criteria that it's up to 10:

=JOIN(DISTINCT(COLLECT({Helper Column with Both}, {IV_Competency}, FIND([Interview competency]@row, @cell) > 0, {Row Number Formula Column}, >= 10)), ", ")

I would say that it might be easier to create a Report instead of use formulas. Then your Report could filter based on your criteria and automatically keep the Sort order.

Let me know if any of this helped!

Cheers,

Genevieve