Sorting tables based on cell colors

Help
1

Hi,

Im trying to sort a column in excel, my column has three different types- High, med and low, so i want High to come first, followed by medium and low, im trying to use sort data table activity but it gives the option of sorting descending or ascending only.
How do i arrange on bases of values like high, medium and low

2

Hi @shreyaank

I’ll suggest you to take Invoke Method activity and use SetOrdinal method and give index accordingly,

I’m attaching my workflow here for your better understanding.
Zip : ArrangeColumns.zip (17.7 KB)

sh1 Result—> sh2
Run this xaml and you’ll see the expected sorting of columns in sheet2,
I hope this will help you.

:slight_smile:

1 Like
3

Hi Samir, I need to sort a single column, which has many cells of High, medium and low -rowise.
Its not mulitple columns like the way you have mentioned on top

4

can you provide sample file

5

image

I need to sort out the rows which is colored, in the order of High, medium and Low. And simultaneously, all the other columns and rows also should get aligned.

6

In this condition you can use filte datatable and filter that datatable in 3 different case high, medium and low,
and using Append Range you can append data like 1st high then Mediun and then Low, @shreyaank

1 Like
7

can you share a sample XAML? @samir

8

@shreyaank Check this is updated,

ArrangeColumns.zip (18.4 KB)

1 Like
9

HI, have a look.
Apologies for clumsy code. 5July.zip (20.5 KB)

It maintain your cell color also.
If you dont want cell color then only keep select statement assigned activity and append range activity.
But it work and give your required output and suggestion or different way to achieve this will be appreciable

10

Hi Samir,

I tried your solution, i have attached the output excel, im not able to segregate the column properly, and when the data gets appended - dt2 and dt3, empty cells are popping up. Please have a lookformat.xlsx (13.5 KB)

1 Like
11

Hi @Tushar,

I tried your solution, i have attached the output excel, im not able to segregate the column properly, and when the data gets appended - dt2 and dt3, empty cells are popping up. Please have a lookformat.xlsx (13.5 KB)
[/quote]

1 Like
12

I will check again with test data.
Is it possible you to provide input file so that i directly check with it

13

hI Tushar,
this is my input IP.xlsx (13.0 KB) file

1 Like
14

5July - 1.1v.zip (32.0 KB)
Have a look into this. Also it remove unwanted empty column.
o/p is in sheet3. Delete all item in sheet3 and check.

It take little time to delete empty column but i am not able to find any fastest way to do that

15

Hi Tushar,

I figured out a way to delete the unwanted columns by passing the correct range for the datatable. However, when Im trying to append the datatable for High,Med and Low, there are empty rows in between, Please see the below screenshot. The excel table breaks in between and again starts appending from row 56,57 … image