Column Transpose with Condition on its Cells' Values

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.

1 Like

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.

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

1 Like

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

Hi @hsendel

Hope you are doing good

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

Cheers

1 Like

Thanks @Palaniyappan for Asking, hope same for you , long time ago we didn’t hear from you :slight_smile:
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 :

image

Thanks in Advance

@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:
grafik

retrieving the max length for the longest group:
grafik

Preparing the result datatable columnstructure:
grafik

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

Finaly we use following LINQ for populating the target DataTable:
grafik

  • 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:

3 Likes

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

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

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

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

Studio 2020.10.7

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 :+1:

1 Like

Should be available

1 Like

@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?

yes we can modify

1 Like

Great !!!

Lets assume first col (index 0) holds the grouping info, second col holds the values
give a try on

(From s In dtData.AsEnumerable.Select(Function (x) x(0).toString).Distinct()
Where Not (isNothing(s) OrElse String.IsNullOrEmpty(s))
Let grp = dtData.AsEnumerable.Where(Function (f) f(1).toString.StartsWith(s))
Let grv = grp.Select(Function (v) v(1)).toList
Select Tuple.Create(s, grv)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)

feel free to replace the col index with the col name

1 Like

No Comment !!! :+1:

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