If one ID is assigned two different values then keep only one of them and assign another to other ID where both the ID's will be under same Name


I have an excel sheet like shown below image

Description of excel

Here , The data is like a “Name” has a number of “ID” assigned where “Work” has a value the “Status” is “Occupied” and if “Work” is blank then “Status” is “Available”.

“Duplicates” contain Count of ID’s occuring how many times
“NameCount” Contains count of ID’s that a name has

Problem Statement The output file is like image

In the output file : if a name has 2 id’s where one id contain more than one row and if another id is available(contains no value of “Work”) then assign one of the duplicate value of work to another id which is available .

The main aim is to remove the duplicate ID’s and assign the value to ID which has same name but status is available. If no available then skip. The shifting of rows can only happen within a name.

Can anybody please help me how to solve this problem ?

Have a look on this demo XAML:
Sonia215_differentIDsFlow.xaml (15.1 KB)

For production level I suggest to enhanced it with some additional checks (e.g. 0 row return results handling etc.) Also I do feel that some exeption scenarios are to expect e.g. more duplicate groups as available free reows etc.)

Kindly note: 8 g 34, vs. 9 g 23 (implemented). XAML has implemented to select the 2nd row from the duplicate group and so it comes to this different output

1 Like

Thanks @ppr the solution is working and if any exception occurs in my original data i will get back to you.
Thanks again …

@ppr In this workflow what is the meaning of “res.ElementAt(1)” in Assign actvity.

@ppr because it is showing me the error “Assign: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index”

Res.elementat(1) is fetching the Second element from a collection. For any reasons this Second element doesnt exists.therefore the issue.

I would suggest to Analyse the Data to find Out what is Happening and causing this. Then save IT Up with some Checks. This a typical Case for what i named “bringing IT Up to production Level”

Thanks @ppr Second element means in a row the element of second column ?

Insider the Statement returning to res is this Access to the Second element. IT depends on this Not on row Indexes Fron datatable

Thanks @ppr i think the issue is that in some cases elementAt(1) is not present and in some cases it is present. What should i use to avoid that problem ?
Please help.

Best would be to reformulate requirements Akin with Sample Data covering all different scenarios. With this the Implementation can be enhanced. Can you Share it? Thanks

Find updated XAML incorporating reformulated requirements here:
Sonia215_differentIDsFlow_V2.xaml (18.8 KB)

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.