How to filter out and retain different duplicates in a column in Excel and do certain operations with it

[Neha_Kumari1 (Neha Kumari)]

Hello All,
Hope you are doing good.

I am stuck in a part in excel automation, would really appreciate on any ideas.

I am just concerned about the two columns in the given screenshot, ‘Result’ and the ‘Judge Date’. I need to filter the ‘Judge date’ on basis of each of the different available dates in the column (ignoring the time), and for those each date, I need to check the result, on basis of it, if there is any ‘Fail’, and further counting the ‘Fail’ and note somewhere.
But, since the dates could be dynamic every time, I am confused on capturing it

Thankyou

[ppr (Peter Preuss)]

assign Activity:
LHS: dictReport | Dictionary(of String,Int32)
RHS:

(From d in dtData.AsEnumerable
Let dp = DateTime.ParseExact(d("Judge Date").toString.Trim, "yyyy-MM-dd HH:mm", System.Globalization.CultureInfo.InvariantCulture)
Group d by k=dp.Date into grp=Group
Let fc = grp.Where(Function (x) x("Result").toString.Trim.ToUpper.Equals("FAIL")).Count
Select t = Tuple.Create(k.toString("yyyy-MM-dd"), fc)).ToDictionary(Function (t) t.Item1,Function (t) t.Item2) 

We would assume that the date format after reading in the Excel within the datatable will be different. But You can check during a debugging and adapt as needed

[Neha_Kumari1 (Neha Kumari)]

Hi @ppr ,
Thankyou so much for your response. I got it & it’s working quite fine.

Just few more questions, how could we arrange this output data under two columns in excel, like this

and why we are using Tuple here? Could we also use List in it’s place?

[saurabhB]

Small changes over the solution provided by Peter,

define a datatable “DtResult” using build datatable with columns “Date” and “Fail”
in assign activity , left side will be “DtResult” and right side will have below code

dtData.AsEnumerable
Let dp = DateTime.ParseExact(d("Judge Date").toString.Trim, "yyyy-MM-dd HH:mm", System.Globalization.CultureInfo.InvariantCulture)
Group d by k=dp.Date into grp=Group
Let fc = grp.Where(Function (x) x("Result").toString.Trim.ToUpper.Equals("FAIL")).Count
Let ra = New Object(){k.toString("dd-MMM"), fc} 
Select DtResult.Rows.Add(ra)).CopyToDatatable 

Tuples are easy to hold key value pairs and easy to query like you can just say tuple_var(“2022-04-16”) and this way you can get the number of “Fail” for this day as output

[Neha_Kumari1 (Neha Kumari)]

Hi @saurabhB,
Thanks for acknowledging my question.
I was trying with this, but it’s showing error with end of expression expected, though it seems okay to me. Could you find anything

[ppr (Peter Preuss)]

(From d in 

You need to complete the statement by adding above on the begin

[Neha_Kumari1 (Neha Kumari)]

Got it now…
Thanks @saurabhB &@ppr