Macro for filter values and save it as different results

[Manojkumar]

Hi,

I have the following data

A	B
1	1
2	2
3	3
4	1
5	2
6	3
7	4

I want to write a macro which takes the value from B and filter A, then multiply them and store it in subsequent columns.

The output I want is

a	multiplier
1	1	1	2	3	4
2	2	2	4	6	8
3	3	3	6	9	12
4	4	4	8	12	16
5	5	5	10	15	20
6	6	6	12	18	24
7	7	7	14	21	28

 when I tried in batch macro using control parameter , update value as shown below I get the following result.

a	b
1	1	1
2	2	4
3	3	9
4	4	16
5	5	25
6	6	36
7	7	49

Can some one please help me on what is wrong with the flow.

[StephenR]

Looks like you want an iterative macro. I have a couple of questions: In the output example it looks like Column A was just added to the result each time. In the batch macro example it looks A and B are multiplied, but the pattern on B is not the same as the top input data, is what you want Column A multiplied by Column B? Finally, how many times do you want this multiplication to happen?

[Manojkumar]

Hi Sruhi,

Thanks for replying. Yes, I want the column A to be multiplied with column B values.

About how many times, its the number of values in column B. Lets say, first take column B value 1, multiply all the values of A with 1.

Then take the second value of column B which is  2, multiply all the values of A with 2. Like this do for all values in column B.

Hope I have clarified. kindly let me know if you need more info.

[AdamR_AYX · Accepted solution]

Does the attached do what you are looking for?

[StephenR]

If you are filtering then multiplying isn't that the same as just saying Col A*Col B? I feel like I'm still missing something.

[Manojkumar]

Hi AdamR,

Thanks for the workflow. It worked like a charm.

Just an added request, is there a way to save each batch run as different sheets in same Excel file.

Thanks in advance

[AdamR_AYX · Accepted solution]

You certainly can. See the attached. You might want to clean up what fields go into the excel file, but it should give you the idea.

[Manojkumar]

Hi Adam,

Thanks for the workflow, When I tried with my workflow, I am getting the following error.

Error: Churn trend macro (15): Record #2: Tool #27: Target Excel file may be corrupt! Cannot find required file, or element within file: xl/workbook.xml

I have attached the photos of macro controls, I have tried and searched for solutions, I couldn't find any. Even if I copy the macro tool from yours to mine,i am getting the same error. It works as long as it is in your workflow. Kindly help me understand what I am doing wrong,