Looking for a way to input groups of data into a batch macro and process each group

[jaiwalke]

Hi everyone,

I'm currently working through a large data set (300k rows) and and I've managed to group unique identifiers towards a record ID for example:

ID	Key values	data 1	data 2
1	abc	4352	Alaska
1	abc	500	Ohio
2	xyz	6453	New Jersey
2	xyz	48489	New Jersey
3	jkl	845	Colorado
3	jkl	2354	California

I'm currently trying to process each of these to a batch macro to output each set of ID which are the same as separate records for example:

ID	Key values	data 1	data 2
1	abc	4352	Alaska
1	abc	500	Ohio

My current theoretical solution would be to iterate through each record, but I'm not sure where I'd begin. Any help would be appreciated, thank you!

[SPetrie]

Hello! Can you elaborate a bit more on what you mean by separate records? Are you trying to get all ID 1s into their own file or is something else going on?

What is your goal here?  A batch macro may be a bit much depending on where you are taking this.

[jaiwalke]

Hi SPetrie, I'm looking to get all record 1s to one file, record 2s to another, etc. 

The current goal is to break up a large input file into smaller files to use downstream. For example, record 1 would have 100 entries with the ID 1. These files will then be used for a separate work flow after this data split occurs. 

[SPetrie · Accepted solution]

Thanks for the clarification!

You can use a batch macro to do this, but I think it may be a bit overkill.

Since you already have an ID that indicates which items are to be split up into different files, you can use that field to generate a name column and have an output tool do the splitting for you.

A formula such as ".\MyFile-"+ToString([ID])+".xlsx|||sheet1" for example, would create an excel file called MyFile-## for each ID it found and would only have those ID items in them.

I attached an example workflow showing this method.

[jaiwalke]

Thanks for the solution, I didn't even considering directly outputting the data. I'll give it a go and reply if I get any other issues!