How to move lines item for specific period

I have two datasets: the primary dataset, referred to as "Main Data," and another one known as the "template data." According to the template data, I need to move or change the Month column to SEP FY30 from APR FY24 in the Main Data tab.

However, I should only move the lines that are present in the template data. In most cases the line items are single but the challenge arises when, for instance, Order 31249 has identical lines in the Main data tab (I have to move only 1 line that is present in the template data). In this case, I'm required to move only one line to the future period for SEP FY30. Can you suggest a method to create a workflow that meets these requirements?

Example 05.02.xlsx

Help

Accepted answers

Calvin6KV

@Learner09 Please see if this helps. What I have done here is to use Record ID to label eac record and then Unique to temporarily separate the duplicated lines (e.g. 31249 and 31211). Now, I can perform the join between the Main and Template Data without worry that the duplicated lines will be affected. Finally, just union everything back together, sort by the Record ID and we get the final output. However, please do take note this workflow assumes that there are no duplicates in the Template Data tab.

Move Lines for Specific Period v1.yxmd

All comments

binu_acs

@Learner09 what is the logic to move if you have multiple numbers in the template? Also why are you considering the rows with number 31208 one row even though there are 2 entries in the template?

Learner09

@binu_acs it is a process requirement, as per the process we have to move a partial amount to the future period and then it will be added back as we get the approval.

Learner09

@binu_acs could you please help in this as well ?

daryllj

Since you have the main data and template, I think you can use the Join tool here and join by Specific Fields using the "ITEM", "SNO" and "Number". This is just to separate what is in the Main and not in the Main template, then further replace the month with the desired one with a Formula tool?

binu_acs

@Learner09 Can you explain how are you calculating the below output

Learner09

@binu_acs there is no SOP for these calculations, this is fully manual, and that is the only reason I am trying and asking for help to automate this. I updated the month as per the data available in the template. let's take an example 31249 all three lines are identical however, I have to push only the line for SEP30 (that also available in the template). So basic I have to move all the lines that are available in the template, in case of all identical lines I have to pick any one.

Calvin6KV

Move Lines for Specific Period v1.yxmd

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703