Sort Excel By Latest Date

Learning Hub Academy Feedback
, ,
1

Hi,

I have a problem in sorting a date format inside an excel. I have tried using Sort Data Table activity and the result doesn’t work as expected.

I would like to remove rows that contains a duplicate ID with the oldest date. Hence, I tried to sort the date first into a latest date and use remove duplicate rows activity (still not sure if this works).

Original Format as below:
image

Result after use Sort DataTable activity:
image

I have tried using other ways that I found in the forum but it doesn’t work:

  1. DT.AsEnumerable().OrderByDescending(Function(r) CDate(r(“Date”).ToString.Trim)).CopyToDataTable

  2. (From row In DT Order By DateTime.ParseExact(row.Item(“Date”).ToString, “dd-MM-yy”, System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable

  3. (From x In SiebelDT.AsEnumerable() Order By convert.ToString(x(“ID”)),convert.ToString(x(“Date”)) Select x).CopyToDataTable

2

@jenkim Does these queries give you errors ? These queries as it explains by itself Will Perform a Sorting Operation based on the Date value, they do not remove records which are duplicates or only Select some records which are needed.

We would need to perform Additional operation along with it.

Can you provide a Sample Excel File with few records and the output that you Expect for the Sample data ?

1 Like
3

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

4

@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

5

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
6

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

7

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

8

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

9

Yes some of the rows contain empty value.

10

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

11

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

12

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

13

Hi @jenkim,

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

Thank you
Balamurugan.s

14

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.

15

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

16

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:

17

Hi,

I tried as your query and it prompt an error:

image

18

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

Also what is the date format you have used ?

19

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

image
image245×515 7.64 KB

I tried both format and both prompt the same error.

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

@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

1 Like