Need Help with JOIN COLLECT: Need Help by Tomorrow!

Chris Shifflett

How can I collect data from each column and row?

Below you will see each column with an associates name at the top. On the side of the grid you will see the list of dates. I need to pull that data to another grid by needing to know on each date what someone worked.

Example on 11/02: I need to know how many hours Angela Worked.

I'm trying to use a Join/Collect option to pull all that data into a cell based on name and date, but not having any luck.

=JOIN(COLLECT({Downtime Daily all Associates Range 7}, {Downtime Daily all Associates Range 4}, FIND(Staff@row, @cell) > 0, {Downtime Daily all Associates Range 3}, @cell = Parent@row))

Chris Shifflett

@Genevieve P - Hello, I appreciate your response. However I did figure out a better solution to find data from across all rows and columns from multiple grids.

=IFERROR(IF(ISBLANK(Staff@row), "", INDEX({All Downtime for all Coders Range 1}, [Downtime Day]3, MATCH(Staff@row, {All Downtime for all Coders Range 2}, 0))), 0)

The solution provides indexing which will put into account all rows (in the example above) then MATCH the staff member from the first row (as shown above). I then created two columns on my other grid (as shown below) that balances the Downtime Day with the row number (example is 10/26/2020 is row 1, 10/27/2020 is row 2, etc. I can now move associates from any grid and it will logically assign without human interaction.

Downtime 1 and Downtime 2 are pulling from two grids that are similar to the example I gave you, because I have a staffing model that consists of 260 associates. Indexing will not allow formula stacking of greater than 100,000 referenced cells. I used the Downtime column to add the value pending on which grid the associate is stored =IF(ISBLANK([Downtime 1]@row), "", VALUE([Downtime 1]@row + [Downtime 2]@row))

So whomever is in the "Staff" column, it will index and match based on 2 grids that contain 250 associates. Importantly is if that Staff MEMBER moves to another team we add them onto the Supervisor or Managers grid and the data will follow them. I NO longer need to do a 1:1 index/match solution.

Hope this makes sense!

Genevieve P.

Hi @Chris Shifflett

Since you have individual columns per-person, you'll need to create a different formula per-person as well. Your initial range in a JOIN(COLLECT will be the column with the value you want returned.

So, for Angela, this range will need to be Column 2. However, for Angie, this column is Column 3.

Instead of a JOIN(COLLECT which brings together multiple cells into one cell, what about using an INDEX(MATCH?

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}))

So for Angela:

=INDEX({Columnn 2}, MATCH([Date Column]@row, {Date Column in source sheet}))

Let me know if this makes sense, or if you need any more help!

Cheers,

Genevieve

Chris Shifflett

@Genevieve P - Hello, I appreciate your response. However I did figure out a better solution to find data from across all rows and columns from multiple grids.

=IFERROR(IF(ISBLANK(Staff@row), "", INDEX({All Downtime for all Coders Range 1}, [Downtime Day]3, MATCH(Staff@row, {All Downtime for all Coders Range 2}, 0))), 0)

The solution provides indexing which will put into account all rows (in the example above) then MATCH the staff member from the first row (as shown above). I then created two columns on my other grid (as shown below) that balances the Downtime Day with the row number (example is 10/26/2020 is row 1, 10/27/2020 is row 2, etc. I can now move associates from any grid and it will logically assign without human interaction.

Downtime 1 and Downtime 2 are pulling from two grids that are similar to the example I gave you, because I have a staffing model that consists of 260 associates. Indexing will not allow formula stacking of greater than 100,000 referenced cells. I used the Downtime column to add the value pending on which grid the associate is stored =IF(ISBLANK([Downtime 1]@row), "", VALUE([Downtime 1]@row + [Downtime 2]@row))

So whomever is in the "Staff" column, it will index and match based on 2 grids that contain 250 associates. Importantly is if that Staff MEMBER moves to another team we add them onto the Supervisor or Managers grid and the data will follow them. I NO longer need to do a 1:1 index/match solution.

Hope this makes sense!

Genevieve P.

Hi @Chris Shifflett

Wow, what a great solution, thank you so much for sharing! I'm going to mark your response as the "Accepted Answer" in case other Community members are looking for something similar.

Thanks again!

Genevieve