Forum Discussion
creating data sets from a dynamic range
Hello,
I have a work schedule that I populate for my staff's daily activities and I have made it able to populate other documentation from this form.
The issue I am having is I have only gotten it to work with a set range (currently 5 rows), which is great if I have 2-5 people on a task. if I have 1 or 8, I either take too much space or don't have enough slots to populate the task.
I have a range B16:F65. I want it to recognize an entry in column D and use that to determine how many rows to draw values from. it would then recognize the 2nd, 3rd, ... column D entries and create data sets that I could then use to populate my other paperwork.
Any help is appreciated.
https://1drv.ms/x/s!AocIVX6UvuXYgs5pgHIgvjMqdF7Bmw
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...
15 Replies
whoa things blew up! ok sorry for the confusion. I'm gonna take a closer look at everyones responses and get back to you all. i appreciate the insights.
Just for clarification this is the only sheet where data entry takes place, i have other tabs that use the information from this sheet to populate other paperwork i hand my staff.
The end result I'm hoping for is to export the data entered on the sheet (probably to another tab) that will list all data in the range for the first task. my initial version looks like this
This was the only way I could get the information divided into ranges to populate the other tabs properly. I have each activity physically divided on the sheet and use static formulas to pull the needed information into the paperwork. This divided crews into groups of 5 for any given task. Its ok but lacks flexibility. I cant put more than 5 to a task or if I have 10 people all on separate tasks it becomes a 3 page printout
If there is a way to recognize the occurrence of an entry in column D and have it figure out how many rows there are to the next occurrence of a column D entry and pull that data group as the first set. then from the second occurrence to the 3rd would be designated as the second set and so on. Each entry in column D would trigger the next group of results. it can get exported to an alternate tab for organization or whatever it takes. The data can then be used to populate the appropriate paperwork. I hope that helps clarify.
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...
