Index Collect that excludes previous values in returning sheet

john.misiewicz@gmail.com

Hello-

I am attempting to create an architecture where employees are auto-populated based on role.

I have a sheet with the entire orgs employee list that contains names, employee numbers, and titles

The working sheet is attempting to pull in all people of each job title. Each row is a job title (same title with multiple people in these roles). I want each row to return an index collect from the collection range that has not already been collected in the previous rows. what sort of criterion script can I write to make this happen or are their work arounds.

Thanks,

John

Lucas Rayala

Hi @john.misiewicz@gmail.com, I think the community is tired -- the questions have piled up! The answer to your question is kind of nuanced and depends on the specifics of your data in both your sheets. Let's say that the source sheet is Sheet A and the sheet your trying to get the formula in is Sheet B. Again, there's a million ways to do this and I'm just providing a single viable solution, which will almost certainly need to be modified.

You need to provide Sheet A with a unique key for each row. Unless that key is somehow contained in your incoming data, lets assume you need an autonumbered column. To get that, create a column called "RowID" and select autonumber from the column type.

Now you have a column that allows you to uniquely identify your generic job titles. But you want to pull them in, one after another. To do that, you'll need another helper column in Sheet A. Call this one "RowOrderByTitle". In this column, add the column formula:

=COUNTIFS([Job Title]:[Job Title], [Job Title]@row, RowID:RowID, <=RowID@row)

Now, if you have 10 people with the job "Sales", they will all be sequentially numbered from 1 to 10.

For this example, I'm going to assume you want to bring all the sales people into Sheet B. In Sheet B, you have to pre-populate some rows. A column formula won't work unless there's something in the row. So, go ahead and populate a column, called RowHelper, with sequential numbers, from 1 to x, with "x" being the largest number of rows you will need on that page.

Create your columns to pull in your Name, Emp ID, Titles, etc.

Start in the "Name" column and add this formula:

=IFERROR(INDEX(COLLECT({name column in sheet a}, {RowOrderByTitle in Sheet A}, RowHelper@row, {title column in sheet a}, "Sales"),1),"")

Do this in every column, replacing the name column with emp ID, title, etc, as needed. This will bring in all the sales people from Sheet A, ordered sequentially.

This may not work for you! If you need to have multiple titles, you'll need to modify the formula in RowOrderByTitle to account for that difference.

There are many ways to make this happen, again, it depends on your circumstance.

Let me know if this helps, and please flag this post if I answered your question! Good luck!

john.misiewicz@gmail.com

Is there a way to get faster resolution to questions?

Lucas Rayala

Hi @john.misiewicz@gmail.com, I think the community is tired -- the questions have piled up! The answer to your question is kind of nuanced and depends on the specifics of your data in both your sheets. Let's say that the source sheet is Sheet A and the sheet your trying to get the formula in is Sheet B. Again, there's a million ways to do this and I'm just providing a single viable solution, which will almost certainly need to be modified.

You need to provide Sheet A with a unique key for each row. Unless that key is somehow contained in your incoming data, lets assume you need an autonumbered column. To get that, create a column called "RowID" and select autonumber from the column type.

Now you have a column that allows you to uniquely identify your generic job titles. But you want to pull them in, one after another. To do that, you'll need another helper column in Sheet A. Call this one "RowOrderByTitle". In this column, add the column formula:

=COUNTIFS([Job Title]:[Job Title], [Job Title]@row, RowID:RowID, <=RowID@row)

Now, if you have 10 people with the job "Sales", they will all be sequentially numbered from 1 to 10.

For this example, I'm going to assume you want to bring all the sales people into Sheet B. In Sheet B, you have to pre-populate some rows. A column formula won't work unless there's something in the row. So, go ahead and populate a column, called RowHelper, with sequential numbers, from 1 to x, with "x" being the largest number of rows you will need on that page.

Create your columns to pull in your Name, Emp ID, Titles, etc.

Start in the "Name" column and add this formula:

=IFERROR(INDEX(COLLECT({name column in sheet a}, {RowOrderByTitle in Sheet A}, RowHelper@row, {title column in sheet a}, "Sales"),1),"")

Do this in every column, replacing the name column with emp ID, title, etc, as needed. This will bring in all the sales people from Sheet A, ordered sequentially.

This may not work for you! If you need to have multiple titles, you'll need to modify the formula in RowOrderByTitle to account for that difference.

There are many ways to make this happen, again, it depends on your circumstance.

Let me know if this helps, and please flag this post if I answered your question! Good luck!

john.misiewicz@gmail.com

@Lucas Rayala

Thanks, the referring sheet has employee numbers. I used your logic of number in the nested index, collect function and got it to work. Thanks!

Lucas Rayala

@john.misiewicz@gmail.com I'm glad you got it to work!