Forum Discussion
creating data sets from a dynamic range
Tom_C1155 It's very difficult to decipher exactly what you want to achieve, or where the results should be output to. From what I understand, it seems like you want to input a single activity number for each task. When multiple employees are assigned to one task, only the first row will contain the activity number, description, location/notes and unit. If this is correct, I'm guessing you want to summarize or group the data by "Task ID" somewhere else in the workbook. As your sample workbook has been stripped down considerably, and I'm not exactly sure where/what you want to output, consider the following example as a nudge in the right direction (hopefully):
=LET( empNames, C16:C36, actNums, D16:D36, tskId, SCAN(0, actNums, LAMBDA(a,v, a+(v<>""))), FILL, LAMBDA(range, SCAN("", range, LAMBDA(a,v, IF(v="", a, v)))), arr, HSTACK(tskId, FILL(B16:B36), empNames, FILL(actNums), FILL(E16:E36), FILL(F16:F36), G16:G36), FILTER(arr, (empNames<>"")*IF(N13="", SEQUENCE(ROWS(actNums)), tskId=N13), "none") )Sample Results
The SCAN function is first used to assign Task ID numbers to the dataset, incrementing by 1 whenever a new activity number is input. The SCAN function is then used to define a custom LAMBDA function "FILL", which is used to fill the task details down for each employee assigned to each task.
To filter the results by Task ID, input the desired ID number in cell N13. This was done as an example only. You could also add filter controls for other columns, such as unit or activity number.
Please see the attached workbook...
Hi,
Will something like the attached work?
I configure No.Rows for each Activity like below, (in a new tab I added, called "No. Rows").
e.g. Activity 101 needs 3 slots, Activity 153 needs 4 slots, Activity 135 needs 2 slots, and Activity 125 needs 125 slots.
Then in columnD I added a formula =TOCOL(IFS(NOT(ISBLANK(A2:H5)),A2:A5),2,FALSE) base on this configuration:
- hi rachel,
thank you for your effort, unfortunately it will not work in this circumstance. the spreadsheet I provided is an example of the formatting used on the page. I had to strip my version down a lot to be able to post it.
This workbook has a page like this for every day of the month. The activity # column uses a dropdown to draw from a table. there are about 200 activity # and column E populates with a Vlookup to match the activity number to the proper description.
Every column D entry signifies a new timesheet/task for my staff. Day 1, I may have 9 people on one task. all nine would be listed under 1 activity code. Day 2 I might have the same 9 people each assigned to their own task. this would be 9 activity codes. Day 3, i may have 2 or 3 crews staffed with 1 to 4 individuals per activity. again 1 activity code per crew. using the uniqueness of an activity number itself as a means to division doesnt work either. On the days when I have 9 staff on their own task, most would be on the same activity code.
This is a tough one...Tom_C1155 It's very difficult to decipher exactly what you want to achieve, or where the results should be output to. From what I understand, it seems like you want to input a single activity number for each task. When multiple employees are assigned to one task, only the first row will contain the activity number, description, location/notes and unit. If this is correct, I'm guessing you want to summarize or group the data by "Task ID" somewhere else in the workbook. As your sample workbook has been stripped down considerably, and I'm not exactly sure where/what you want to output, consider the following example as a nudge in the right direction (hopefully):
=LET( empNames, C16:C36, actNums, D16:D36, tskId, SCAN(0, actNums, LAMBDA(a,v, a+(v<>""))), FILL, LAMBDA(range, SCAN("", range, LAMBDA(a,v, IF(v="", a, v)))), arr, HSTACK(tskId, FILL(B16:B36), empNames, FILL(actNums), FILL(E16:E36), FILL(F16:F36), G16:G36), FILTER(arr, (empNames<>"")*IF(N13="", SEQUENCE(ROWS(actNums)), tskId=N13), "none") )Sample Results
The SCAN function is first used to assign Task ID numbers to the dataset, incrementing by 1 whenever a new activity number is input. The SCAN function is then used to define a custom LAMBDA function "FILL", which is used to fill the task details down for each employee assigned to each task.
To filter the results by Task ID, input the desired ID number in cell N13. This was done as an example only. You could also add filter controls for other columns, such as unit or activity number.
Please see the attached workbook...
Just played with the formula you provided and I think you hit the nail on the head. This should achieve exactly what I need it to do.
Thank you so much!
Hi,
I see.
I think you still need to input Staff under a certain Activity# for each day somewhere in your workbook?
Will inputing Activity# and Staffs like below make your life easier?
(I think you can input this table either by hand, or by some vlookup + transpose formula?)
I added formulas in both column c and column D based on this input:
