Excel filtering

[khooshbu.jani]

I have the excel sheet in which i need the output as follow:

If login status is failed and account status is “Locked” then I need to find the each user’s last login date/time

following is the excle file for the reference,

Please help.

Please find excel file for the reference. please refer to sheet “Login Attempts”
Failed_Login_Attempt_Spreadsheet.xlsx (16.0 KB)

[Yoichi]

HI,

Can you try the following sample?

dict = dt.AsEnumerable.GroupBy(Function(r) r("Username").ToString).Where(Function(g) g.Any(Function(r) r("Account Status").ToString="Locked")).ToDictionary(Function(g) g.key,Function(g) g.OrderBy(Function(r) CDate(r("Login Date/Time").ToString.Split({" "c}).First()+" "+r("Time").ToString.Split({" "c}).Last())).Select(Function(r) CDate(r("Login Date/Time").ToString.Split({" "c}).First()+" "+r("Time").ToString.Split({" "c}).Last())).Last())

This returns date time which combined “Login date/time” and “time”

Or if you need only “Login date/time” column, the following will work.

dict = dt.AsEnumerable.GroupBy(Function(r) r("Username").ToString).Where(Function(g) g.Any(Function(r) r("Account Status").ToString="Locked")).ToDictionary(Function(g) g.key,Function(g) g.OrderBy(Function(r) CDate(r("Login Date/Time").ToString)).Select(Function(r) CDate(r("Login Date/Time").ToString)).Last())

Sample20230322-4L.zip (15.7 KB)

Regards,

[khooshbu.jani]

This is just giving time in msg box

[Yoichi]

Hi,

What is your expected result? If you need table in worksheet, we can easily create it from the above as the following. Can you try this sample?

Sample20230322-4Lv2.zip (16.3 KB)

Regards,

[khooshbu.jani]

I could do it.

Thank you.