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...
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 think Tom_C1155 request is this:
He has a static table that looks like this:
Every day, he needs to put some staffs under some Activity#, like below:
The issue here is: how to make the above screenshot pretty without manually adding rows below 101, 153, and 135 to accommodate the staff associated with each Activity#.
rachel You may be right, but that's not the way I interpreted Tom_C1155 's description/sample workbook. It seems to me like this "form" is just one of many data entry sheets (not the output sheet). There are data validation lists setup in both the Unit and Employee columns, which indicates data entry (although they are currently broken in the stripped-down file). That plus:
- "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."
- "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... I may have 9 people on one task. all nine would be listed under 1 activity code."
These were the reasons that lead me to my conclusion. I didn't see anything in the workbook, or in the description, to indicate that another table exists which links employees to tasks for a given day. In any event, there's clearly not enough information provided to determine what's expected. I guess we'll just have to wait and see what the next response is. 😉
Your way of thinking is really interesting!!
Also, sometimes my OCD just gets triggered.
I read your formula, seems that you just want to skip blank rows, if so, I think below formula should be enough:
