Sort Excel By Latest Date

The first one gives me this error:
image

The second one gives me this error:
image

The third one have no error but the result is not correct as you can see from the year:
image

@jenkim Check with this query :

DT.AsEnumerable.GroupBy(Function(x) x(“ID”).ToString).Select(Function(n)n.OrderBy(Function(x)DateTime.ParseExact(x(“Date”).ToString,“MM/dd/yyyy HH:mm:ss”,Nothing))(0)).CopyToDataTable

Make sure the Format of the Date is same.

To check the format of the date value, Simply use a Message Box with this value:
DT(0)(“yourDateColumn”).ToString

Based on the Format of the Date, modify the format in the Query

I have change the format accordingly but it returns error:

Format
image

Query:
DT.AsEnumerable.GroupBy(Function(x) x(“ID”).ToString).Select(Function(n)n.OrderBy(Function(x)DateTime.ParseExact(x(“Date”).ToString,“MM/dd/yyyy HH:mm”,Nothing))(0)).CopyToDataTable

Error:
image

You can find sample data here:
Book2.xlsx (15.8 KB)

1 Like

@jenkim Can you just use “M/dd/yyyy HH:mm” and check

Still return the same error as above after change the format to M/dd/yyyy HH:mm

@jenkim Are there any empty values present in the date Column ?

Yes some of the rows contain empty value.

@jenkim What do you want to do in that case ?

I plan to just sort the date to latest date and expect the empty rows will go to the bottom of the list.

@jenkim But you wanted to remove Duplicates with oldest date right and keep the Latest Date :sweat_smile:

Hi @jenkim,

Here is the video explanation that how to sorting the excel based one column (s)

Thank you
Balamurugan.s

Yeah I wanted to remove duplicate IDs.

For example:

Original format
image

After sorted manually in excel:
image

Then I will use LINQ to remove the duplicates and it returns value as below:
image

Removes duplicate part I have found the solution, now I’m only stuck with sorted by latest date part.

@jenkim Check this Query :

DT.AsEnumerable.OrderByDescending(Function(x)if(String.IsNullOrEmpty(x(“Date”).ToString),Cdate(“01/01/0001”),DateTime.ParseExact(x(“Date”).ToString,“MM/dd/yyyy HH:mm:ss”,Nothing))).CopyToDataTable

I have used a very Old date for the rows which have Empty Date. I think you might understand why I did that :sweat_smile:

However the Output I got I think is what you needed. Please check and revert back.

Hi, I tried using this activity but in order to create table I need the range which in my case it varies from time to time.

As per discuss in others post to:

Hi,

I tried as your query and it prompt an error:

image

@jenkim Are you using the same file as what you had provided me ?

Also what is the date format you have used ?

Yes I’m using the same file I provided previously.

I tried both format and both prompt the same error.

  1. “MM/dd/yyyy HH:mm:ss”
  2. “MM/dd/yyyy HH:mm”

@jenkim Can you Check and Execute this workflow :
GroupByAndSum.zip (9.8 KB)

I have done the same thing, but it doesn’t give an error. Maybe if this workflow works. Can you compare and check the difference

It works as expected. The only difference is I put ‘Preserve Format’ for my workbook and that’s the reason of the error.

image

Anyway thank you so much for your help. :smiley:

1 Like

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