How to merge previous row as per

Help Studio
, ,
1

hi ,

I have an excel that has table format check excel

what i want to do is for previous months i want to club all records that ar there
into month wise instead of date but for current month as records date wise
Book1.xlsx (8.8 KB)

2

Hi,

How about the following?

image
image698×498 19.7 KB 

dt = dt.AsEnumerable.Where(Function(r) not System.Text.RegularExpressions.Regex.IsMatch(r("date").ToString.ToLower,"jan")).GroupBy(Function(r) System.Text.RegularExpressions.Regex.Match(r("date").ToString.ToLower,"[a-z]+").Value).Select(Function(g) dt.Clone.LoadDataRow({g.Key+".xlsx",g.Sum(Function(r) CInt(r("records").ToString))},False)).Concat(dt.AsEnumerable.Where(Function(r) System.Text.RegularExpressions.Regex.IsMatch(r("date").ToString.ToLower,"jan"))).CopyToDataTable

Sample20230104-5L.zip (8.3 KB)

Regards,

3

@Yoichi its working but how to get in first letter in upper case like sept.xlsx as Sept.xlsx
also Jan will not be fixed need to change as per current month

4

Hi @manoj_verma1 ,

What about this workflow

Input
image

Output
image

Workflow

image
image1096×497 25.3 KB

Code

(From item In dt.AsEnumerable
Group item By col1 = item("date").ToString.Substring(3).Substring(0,1).ToUpper+item("date").ToString.Substring(4).ToLower Into Group 
Select dt.Clone.Rows.Add(col1,Group.sum(Function(x) CInt(x("records").ToString)))).CopyToDataTable

Thanks,

5

Hi,

All right. Can you try the following expression?

dt.AsEnumerable.Where(Function(r) not System.Text.RegularExpressions.Regex.IsMatch(r("date").ToString.ToLower,Now.ToString("MMM").ToLower)).GroupBy(Function(r) System.Text.RegularExpressions.Regex.Match(r("date").ToString.ToLower,"[a-z]+").Value).Select(Function(g) dt.Clone.LoadDataRow({System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(g.Key)+".xlsx",g.Sum(Function(r) CInt(r("records").ToString))},False)).Concat(dt.AsEnumerable.Where(Function(r) System.Text.RegularExpressions.Regex.IsMatch(r("date").ToString.ToLower,Now.ToString("MMM").ToLower))).CopyToDataTable

Main.xaml (6.5 KB)

Regards,