How to sort the Months in Descending order in Excel

Help Studio
, ,
1

Hi,

I have an Excel with Months as columns which is not in proper order.

Excel11
Excel11876×98 2.42 KB

The output of the Excel Should be in the below format.
Excel22
Excel22870×91 2.54 KB

Please provide solution for this

Thanks in advance

2

Hi @vinjam_likitha ,

We can perform this By Reading the Excel and Using DefaultView to Order the Column names.

However, we would require to assume two things.

  1. We already know the A, B, C, D Columns. So we can use it as a Prepared List or an Array.
  2. Next to the A, B, C, D columns will be only the Month Columns.

Considering the two Points we maybe able to use the Solution provided below :

Excel_Sort_ColumnNames.zip (8.6 KB)

  1. First, we read the Excel using Read Range Workbook Activity. We get the Output Datatable, say DT.

  2. Now we create an array of the Column Names we already know that are at the Beginning.

otherColumnNames = {"A","B","C","D"}
  1. Next, we Prepare the Monthly Column Names using the Linq Expression below :
MonthColumnNames = DT.Columns.Cast(Of DataColumn).Where(Function(x)Not(OtherColumnNames.Contains(x.ColumnName.ToString))).OrderByDescending(Function(x)DateTime.ParseExact(x.ColumnName.ToString.Substring(0,3),"MMM",System.Globalization.CultureInfo.InvariantCulture).Month).Select(Function(x)x.columnName.ToString).ToArray

The Assumption over here is that, we may have many formats of Month Column Names, so we generalise it to a Three Letter Month Name format and order the Months and Select the Column Names.

  1. Next, we use the DefaultView.ToTable to order the Columns in the DT 
DT = DT.DefaultView.ToTable(false,OtherColumnNames.Concat(MonthColumnNames).ToArray)

We have the Ordered Columns in Datatable which we can then write it to an Excel.

Let us know after you have checked the workflow and if you are still not able to achieve the Required Output.

3

Hi ,

If the column names are like this 22-Jan,22-Feb in this format instead of Jan ,feb

how to sort them.

Please guide on this @supermanPunch

Thanks for the solution which you provided.

4

@vinjam_likitha ,

If you have all the Column Names in this format, then the Format would be d-MMM.

You could replace “MMM” format to “d-MMM” in th DateTime.ParseExact method of Linq Expression and Check if it works

5

Hi,

Tried using d-MMM but it was showing below error.

Assign: String was not recognized as a valid DateTime

6

@vinjam_likitha , Could you try changing the format to “dd-MMM” and check again. If this gives out error we might need to check the Date format using a Log Message or a Write Line Activity and then conclude the Format.

7

Hi,

The column names which i am getting for months are:
image

First it was showing month/date/year format.

8

@vinjam_likitha , In that case we can Simply use CDate() Method instead of DateTime.ParseExact()

Update the Linq Expression to the Below and let us know if you are still facing issues :

MonthColumnNames = DT.Columns.Cast(Of DataColumn).Where(Function(x)Not(OtherColumnNames.Contains(x.ColumnName.ToString))).OrderByDescending(Function(x)CDate(x.ColumnName.ToString).Month).Select(Function(x)x.columnName.ToString).ToArray