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...
Yes, it is completely unnecessary.
If it is the filtering you want to do, I would just do this:
=LET(res, LET(data,A16:I72,raw_activities, D16:D72, activities, SCAN(TAKE(raw_activities,1,1),raw_activities,LAMBDA(a,v, IF(v="", a, v))), FILTER(data, activities = V14, "")), IF(res="", "", res))
rachel LMAO, this is all very amusing to me, considering the lack of clarity provided in the OP. My answer was never intended to be a "solution", but rather a demonstration of what's possible, based on my interpretation of the inquiry (which was stated in my original response).
The Task ID method is the easiest way to "create data sets [for each task] that I could then use to populate my other paperwork", which I deemed to be the primary outcome sought by the OP. The fill down / filter method you just shared, based on the Activity # column only, doesn't account for scenarios where the same Activity # is used on multiple Units in one day (each one would be its own task). Sure, you could fill down both the Unit column and the Activity # column to create a combined ID that would uniquely identify each task, but that would also require filtering with AND criteria using both columns. The Task ID method achieves this with one column.
Plus, the fill down method that I demonstrated (outputting the filled data across all columns), provides more flexibility... the data could be linked to a pivot table, for example, or be summarized elsewhere using SUMIFS.
Since this is currently all hypothetical, I think it would be best to wait for additional clarification by Tom_C1155
Kind regards.
- I am not criticising you. Sorry you feel that way. I just want to get a clear idea why you think your formula provides more flexibility.
- I think we're done here. If you're not getting it, then I think you should just focus on your own response, rather than attacking/criticizing someone else's.
- FILL down method provides more flexibility for what? as long as there is data in tabular format, you could use a pivot table on the data.