Iterative macro?

[lliberm01]

I am looking to perform a set of processes, then go back to initial data and perform that set of processes again for another input.  I am assuming an iterative macro is needed.  See the attached data set in excel with an example of what I am trying to do (scaled down).

My current process is to input a macro, formula tool the abs value difference between my input number i am iterating through (1 to 10) from the "number field".  I then sort this from smallest to largest and take the top 8 records with a sample tool.  I then use summarize to average those values which shows the avg "Value", the "Number", and the "iteration number".

My output is currently getting to the right answer for the first input but doesnt show any more data in the macro run.  When i bring into the larger data set (main file), the macro output is multiple rows but a different amount than the macro showed and is only averaging when the input matches the number but not averaging the closest N numbers like it does in the macro itself.

I am also attaching the actual macro which uses different fields/amounts compared to my excel example file in case that is easier.

[Yoshiro_Fujimori]

@lliberm01 

If you do not stick to macro, there is also a way to do without it.

You can multiply the rows for each iteration value (1..10) and narrow them down to 8 records for each iteration value and get the average.

Workflow

Output Data

Iteration	Avg_value
1	77.125
2	77.125
3	70.125
4	66.125
5	45.5
6	45.5
7	38.125
8	39
9	43.625
10	31.25

I hope this helps.

[KGT · Accepted solution]

As above, this may not require a macro. It is not uncommon to go down the route of an iterative macro and then realise it's not needed. I think of this in the example of an amortisation table. You could use an iterative macro to build out the table (and the table is easy to conceptualise), but you could use a formula to get a result at any point rather than needing the table.

In your iterative macro, there are a couple of issues.

1. The data going through the iteration output (your "Loop") has to be the same schema as the iteration input.
2. You are filtering based on a single iteration number and so will only get a result out of the "Exit" when iteration 224 runs.
3. If the filter is not configurable from outside the iterative macro, then there is no need to have it inside the iterative macro, it could filter the data coming in.
4. I'm assuming the duplicated data on different rounds per ID is just through creating dummy data...
5. Your Input template data is totally different to the test data that you uploaded...

So, is this what you want?

For each of x numbers, you would like to find the average values of the closest 8 QB's in the draft to number x?

If that's it, then most definitely go with the solution by @Yoshiro_Fujimori !!

If you do want to continue down the path of the iterative macro, then you need to break the process down to something that is repeatable.

It looks like you actually want to do this on the entire dataset each iteration, in which case your iteration output will be straight after the iteration input. 

Take a look at the attached for an iterative macro solution, but if you are looking for the quick solution and it's only a small amount of data (<5k or so), then the macro is not needed.

[Yoshiro_Fujimori]

Hi @KGT ,

Thank you for your comment.

For me, the decision criteria to go with or without macro is not clear.

I prefer to go with standard workflow as it tends to be simpler and easier to debug.

There surely are cases that iterative macro fits better,

but when I deal with large tables (such as Advent of Code problems),

an iterative macro sometimes performs slower than a standard workflow.

I guess it happens when the table passed to the next iteration is too large.

So far I do not have a way to tell which way is better before I go trial and error.

I wonder if anyone have any tips for this decision making...

[lliberm01]

@Yoshiro_Fujimori @KGT 

Thanks.  I kept the macro and fixed the looping error so it output all the iterations.  I then updated further for my purposes and it is working now.

I wasn't able to download the non macro example as its scanning for viruses indefinitely but if that is an option I will look into it based on the tools to see if i can recreate that as I didn't realize you could loop without a macro.  Thanks again!