Column Transpose with Condition on its Cells' Values

[hsendel]

Dears

How To Read one Column and Paste some of its Cells in Row that meet some Conditions?

Thanks in Advance

[Krishna_547 (MudhuKrishna)]

Hi,

Follow the below steps
-Built a data table with the required column name

• Use Read Range to read the column
• Use For Each Row activity to loop through each row of the column,
  _Use IF activity with conditions
  _Use add data row to add those satisfied rows to that created data table in the first step.

HAPPY AUTOMATION !!!

[hsendel]

Thanks a lot @Krishna_547 , I’ve followed your steps and created corresponding workflow but still getting an error : For Each Row: Collection was modified enumeration operation might not execute. ( Workflow Attached
ColumToRowWithCondition.zip (41.6 KB)
)

[Krishna_547 (MudhuKrishna)]

ColumToRowWithCondition (2).zip (11.1 KB)

[hsendel]

@hsendel

For Each Row: Collection was modified enumeration operation might not execute

Thanks @Krishna_547 , Possible to write the output in row instead of column?

[ermanoj3101 (MANOJ)]

Hi @hsendel ,

You can also try below approach-

Step1 - Read column from Read column activity.
Step 2 - Iterate each column value one by one in for each activity.
Step 3: Write your if condition in body of loop and start update your value in excel from write cell activity.

[hsendel]

Thanks @ermanoj3101 , It’s OK for Step 1 & 2 , but for step 3 nothing is showing that the Write Cell Activity will write horizontally ( row) and not Vertically ( Column) . Please explain more for this step.

[ermanoj3101 (MANOJ)]

Yes it will update vertically if Column is fixed.

But as you have mentioned, you have some if condition through which you have to decide on which column that value should update.
So you can write those value accordingly by giving address as “A1” or B1 or C1 in those if blocks for horizontally.

For vertically it should be mentioned as A+icounter

[hsendel]

The Column is Fixed, but the Rows are Changing ( You can refer to Excel Sheet Attached in my above Example). Thx

[Palaniyappan (Forum Leader)]

Hi @hsendel

Hope you are doing good

Pls find the attached xaml
ColumToRowWithCondition (2).zip (10.5 KB)

Cheers

[hsendel]

Thanks @Palaniyappan for Asking, hope same for you , long time ago we didn’t hear from you
Regarding your answer, I’m not sure if you understand my exact query, I want to have at the Output Data written in row instead of Column for each array as follows :

Thanks in Advance

[ppr (Peter)]

@hsendel
we can do it dynamicly with the help of groupBy, dictionary and LINQ

Variables:

Grouping the data by the the first two letters into a dictionary:

retrieving the max length for the longest group:

Preparing the result datatable columnstructure:

Build datatable with first column (ColName: Key), then we do add the other columns:

Finaly we use following LINQ for populating the target DataTable:

• LINQ iterates over the GroupDictionary
• Fills up missing columns by empty values
• Concats exisitng values with filled up values (pra)
• Adds the key info to the rowArray (ra)
• uses the rowarray to add the datarow to dtResult

Input (Similar to your Excel), GroupDictionary and dtResult:

Find starter help here:
GroupBy_1Col_TransposeMembers.xaml (11.8 KB)

Find some further help also here:

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

[hsendel]

Excellent Explanation @ppr … Well Done , Please tell me How you’re able to print the following :

[ppr (Peter)]

set a breakpoint or debug stepwise, the use watch or immediate panel

There is a course on debuging in the UiPath Academy which is highly recommendable for this as well

[hsendel]

Seems you’re using Pro Edition…I’m using Studio Only

[ppr (Peter)]

should also be available on Studio without Pro, which version are you using?

[hsendel]

Studio 2020.10.7

[hsendel]

Anyway @ppr , I will check this Topic on Academy as you said … at least my issue is fixed and I will be able to continue my project. Thanks again for you and all the Team that collaborate on that @Krishna_547 @ermanoj3101 and @Palaniyappan … I’m really appreciate about the JOB you’re doing in UiPath Community … Keep Doing

[ppr (Peter)]

Should be available

The Immediate Panel

[hsendel]

@ppr

Grouping the data by the the first to letters into a dictionary:

@ppr, Last Query before closing this Topic: I see that for grouping the item you’re using 2 Strings :
(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Substring(0,2) Into grp=Group
Let gl = grp.Select(Function (x) x(0)).toList
Select Tuple.Create(k,gl)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)

Can you replace this by what is declared whatever it’s length ( Whatever written in Column A in Sheet2 in My Example?