Iterative Macro (Maybe)

I'd like to sample or filter off the first 91 rows then do a transformation, but I need to do this process multiple times. I was thinking to have an Iterative Macro update a field so that the Sample tool will filter off 91*Iteration and then I would take the bottom 91 which would be the newest data. I've tried multiple ways, but I cannot get the macro to work correctly.

IA40L.yxzp

test.xlsx

Workflow

Macros

Accepted answers

Pilsner

Yes, this is the workflow:

Batch 91 at a time.yxzp

All comments

Pilsner

I think you want to use a batch macro, I have connected your macro to this workflow which should allow the data to be processed in batches, I hope this helps:

Screenshot 2023-07-24 163527.png

mquast

@Pilsner Can you attach the workflow to the forms so I can see the changes

Pilsner

Yes, this is the workflow:

Batch 91 at a time.yxzp

mquast

@Pilsner the Macro seems to be working, but it is losing the format. When I filter to only run Group 1 seven times it works, but when it runs all 7 groups is complies wrong. I've updated the workflow to have the correct order being inputted and I've included the final output in the excel file.

test.xlsx

Batch 91 records at a time.yxmd

binu_acs

@mquast what is the logic to calculate the output? I can see 7 groups (each group contains 91 records). in the output file, why are you taking only 39 columns after transposing and cross-tabing the data?

mquast

@binu_acs the other 61 columns of data are calculated using a different table then joined. The process is the exact same for the first 39 and the last 61. In theory I would like the whole calculation to be in one macro, but I was going to figure out the first half then the 2nd half would be nearly the same.