Read and process first row from each three files then move to second row

I am doing reconciliation from three inputs, every row is having data of unique RootID, I want to read first row from each file, process it (transform and join data), output the result in a new sheet named after that row's RootID, then move to second row and do the same.

I am unable to implement this in macro. I tried with batch macro but it is duplicating, giving one-to-many records (attached my current test output). Can anyone please help.

FileB_v2.xlsx

FileA_v2.xlsx

FileC_v2.xlsx

Desired output.xlsx

test output.xlsx

Macros

Batch Macro

Iterative Macro

Accepted answers

DataNath

@hf14 how does this look? In terms of doing this efficiently I think you were on the right lines with a batch macro. Happy to revisit if this doesn't get you any closer to your desired output but seems to work for the example provided:

RootId feeds the control parameter:

Outputs look like so:

Output based on RootId.yxzp

DataNath

No problem @hf14 - happy to help!

Ah I see, I didn't realise the field was a string and so the filters are set up to filter numeric data types i.e. they don't use quotes. When you come to use this, you'll just need to change the 3 filters so that they contain quotes like so (single or double, doesn't matter):

In your Action tool, you'll need to ensure that you're still only replacing the bit inside the quotes and so you definitely need to tick Replace a specific string and only keep the bit that's inside the quotes in the setup workflow, like so:

Have amended the macro, though I did add 3 extra selects to force RootId to be a string for the setup - you can just right click > delete and connect around these as your incoming data will already be a string. Let me know if you run into anymore issues of course!

Output based on RootId.yxzp

All comments

DataNath

RootId feeds the control parameter:

Outputs look like so:

Output based on RootId.yxzp

hf14

Hi @DataNath

That's exactly what I wanted, thanks so much!

But the only problem is some of my RootIDs are mix of numbers and alphabets, and then filter tool is giving error 'Invalid type in operator =='.

In my original dataset this field has V_string datatype. when I change to Int then its reading but escaping the alphanumeric RootIDs.

DataNath

No problem @hf14 - happy to help!

Output based on RootId.yxzp

hf14

@DataNathThanks for the solution! I have another query though

My original dataset has 80 fields, I have to use Join records by Fields.

My file changes sometimes and gives multiple records for a single rootID (attached FileB-v3). I have used loop on OrderID since this is unique here, inside RootID loop. But I'm getting the duplicate record displayed. (attached Output based on RootID). Can this be handled?

Also I want a different output for these OrderIDs. So i'm running the loop on those three RootIDs. The topmost Summarize is giving result as below, but I want is to be displayed adjacent to each other. Like in the attached desired output.

FileB_v3.xlsx

Desired output2.xlsx

hf14

Hi @DataNath

In continuation of this workflow, I want the output in a fix template(attached Test template). The values coming should be filled as per the template wherever that field occurs, for each rootID (attached desired template output)

Thanks in advance!

Desired template output.xlsx

Test template.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703