Really need help outputting a file for each unique account number into a fixed template

I do not know how this works and could greatly use help. Thank you all.

I attached two photos below. The first is my master data. The master data will always be in one sheet, and all info is here. As you can see, sometimes, there will be 2 or more of the same Acc #s, but they will ALWAYS have same Animal Name and ID num. The states and other columns that I did not list in this sample photo can have diff info those. Look below to see the Whale example. One is State 1 and the other is State 11.

The second photo is my template (a separate excel workbook). What I need it to do is return the account only ONCE and concatenate diff data with a comma. So if there are two different states listed for same account, put them both in cell F8 (from second photo below) separated by comma.

The other piece of this is I need it to copy into this template and output it for EACH account. So here, there would be 10 outputs, and when they output, that template in filled in for the corresponding info.

So, the workflow will have 1 input I suppose??? and then the template only is in output tool.

Master Data Sample.png

Workflow

Output

Macros

Accepted answers

All comments

OTrieger

Hi @slaurel

There are a few ways to concatenate data, for your case the easiest will be to use Summarize Tool.

The next thing that you will need is a batch macro, Animal Name will be the variable for Control.

In the batch macro you will connect the macro input data to a filter tool, and you will connect the control Tool to the filter tool too. In that way the macro will take each time one animal and will write it out separately.

To write the data in to a template you will need to use the Blob tool and then you will get what you want.

slaurel

@OTrieger This was sample data. In the real master data, there could be up to 200 account #s. Using a filter for each one won't be possible.

OTrieger

@slaurel
Exactly that is why the filter is in the Batch Macro. Have you worked with Batch Macros before?

The beauty here that you set it in one way and it will do the same for all the data based on your Control variable, in your case Animal name.

So literally what happens is:
All data goes in to the macro, first animal goes through the filter and then output. Now the second animal and it is continue to do it for each and everyone of the different animals.

slaurel

@OTrieger the action tool only allows for value updates to the file name it appears. Not a specific field. There also is no directory input. The master data is always going to be a singular workbook.

OTrieger

If you would like to be able to step up and create robust solutions you must learn how to use macros, there are several types of macros, one of them is a batch macro. Alteryx has very good training on it, also in the Practical lesson section, there is no way around it, that is something that need to be learnt and master. Bellow there is a snippet that shows you how data goes into a macro. The Directory tool does not need to be in the macro. And how to connect the Control tool without using Action tool.

slaurel

@OTrieger I already know how to use macros - I am very familiar with them. I have only used them when there is a directory tool though (so multiple files and tabs etc). This will always just be one singular sheet and with one singular tab. So I am not understanding why there needs to be a macro or how it can go through that field and do what I need it to do. I need it to summarize and concatenate everything so there's only one row per account. and then I need those fields to populate in my template accordingly. Why can't we use a dynamic output functionality.

OTrieger

From my experience for each thing that you would like to do there are multiple ways how to get that result. I shared with you one way how your can achieve that result, I will leave it to you to decide how to go about it, at the end this is your creation and have the power of choice how to get it done

CoG

One reason to use the batch macro is to handle duplicating the template file via blob tools. If all you needed was 1 row of data for each record exported to a standard xlsx file, then dynamic output would work fine, but to utilize the precise formatting you specified will require extra work (hence the value of the batch macro).

You may also be able to use reporting tools, since your formatting is fairly simple, output with Render tool and Group By [Acc #].

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703