Transpose in Excel by Rules

Help
, ,
1

Hi,

I have an excel table as a column and I want to write as a row.

I can transpose Excel thanks to “Data Manipulation” package. So, my question is not about only transpose process :slight_smile:

I want to transpose for each group. For example, in first time A1:A5, in second time A7:A11 etc.

There is a rule as A(first_rownumber+6):A(second_rownumber+6).

My Excel is:

resim

I want:

resim

Thank you!

2

Hey @Atakan_Kiziltan

I saw your message and thank you. :slight_smile:

So… for this, you can create a rule to read the three sets separately onto a datatable inside a loop. Next, you can pass that datatable into the transpose activity and get the transposed data as the output.

Once done, you can do a append range activity to append the data to a excel so that you will see the output as needed…

The main thing is, just try introducing a loop to capture each set separately using your rule. And the next part is easy :slight_smile:

3

@Atakan_Kiziltan
As an variation to @Lahiru.Fernando with the same Idea of segementation

Assumption:

  • each segement has a length of 5 values and is complete

otherwise we have to handle additional

First step preperations
Prepare the dataTable for the transposed data:
grafik

Second step: bring all values into a list and filter out empty values:

Assign Activity:
dtData.AsEnumerable.Select(function ( r ) r(0).toString).Where(Function (s) Not String.IsNullOrWhiteSpace(s)).toList

Third Step: Transpose Values:
Assign Activity:
(From segment In Enumerable.Range(0,(Values.Count \ 5))
Select d=Values.Skip(segment*5).Take(5).ToArray
Select dtTransposed.Rows.Add(d)).CopyToDataTable

And assign it to dtTransposed that was prepared from above

dtTransposed can later be used for a write range to Excel

PFA Demo XAML here:
Atakan Kızıltan.xaml (9.8 KB)