Dataset Building

[knnwndlm]

Hello SME,

In the attached Excel file, I have a populate dataset in columns A to C.  I want to create a new dataset based on the Table in I5:J8.  In other words, for stratum 1, I want to pick up the total amount for the first 13 records, first 10 records in stratum 2 and first 11 in stratum 3.  The results is shown in L5:M8 table.

I can do this the long way, but I would like to find a better approach to do this since this must be dynamic as the Table input could be changed.

Appreciate your help.

Thanks,

kwl

[Clifford_Coon · Accepted solution]

Hi @knnwndlm ,

Here's one way to keep it dynamic.

Create a column with a value of 1, and then a running total (grouped by Stratum)

Join back to the pickup by stratum.

Filter running total <= Pickup #, then summarize.

 WF attached, Happy Solving