How to set the months given in the table

Help Automation Ops
,
1

If there is start month and end month in the summary then the months should be paste at the below table as mentioned in the summary and the value of that months should be copy paste there how to do that

2

Hi @anjani_priya

Could you share the input and expected output file.

Regards

3

Book 1.xlsx (10.8 KB)
sheet2 is input
sheet3 is output

1 Like
4

Hi,

Can you try the following sample?

image
image899×678 30.9 KB

Sample
Sample20240130-3.zip (17.5 KB)

(Please check result.xlsx)

Regards,

5

output should be only month and year like dec-23 like this but it is like

Capture226
Capture226354×600 3.72 KB

can you make the change

6

Hi,

How about the following?

Sample20240130-3 (2).zip (18.4 KB)

or please set “mmm-yy” as CellFormat in advance.

Regards,

1 Like
7

End month is getting repeated

Capture228
Capture228649×744 37.2 KB

how to solve this

8

Hi,

The above sample assumes input date is 1st day of each month.
Can you try the following sample? it works input is any day.

Sample20240130-3v3.zip (14.6 KB)

Regards,

9

can you explain the logic

10 
dt.AsEnumerable.SelectMany(Function(r) Enumerable.Range(0,1+(CDate(r(1)).Year-CDate(r(0)).Year)*12 +(CDate(r(1)).Month-CDate(r(0)).Month)).Select(Function(i) dtResult.LoadDataRow({CDate(r(0)).AddMonths(i).ToString("MMM-yy"),r(2)},False))).CopyToDataTable

First, we need to calculate number of months between 2 dates. The following expression returns it.

(CDate(r(1)).Year-CDate(r(0)).Year)*12 +(CDate(r(1)).Month-CDate(r(0)).Month)

Then, create list of number which is from 1 to number of months.

Enumerable.Range(0,1+(CDate(r(1)).Year-CDate(r(0)).Year)*12 +(CDate(r(1)).Month-CDate(r(0)).Month))

Next, from the above, create MMM-yy style date using “from date” adding months using the above list and return it as datarow.

Select(Function(i) dtResult.LoadDataRow({CDate(r(0)).AddMonths(i).ToString("MMM-yy"),r(2)},False)))

Repeat number of rows of input datatable and return single sequence of datarow using SelectMany.

dt.AsEnumerable.SelectMany

Finally, convert it to datatable using CopyToDataTable()

Hope this helps you.

Regards,

2 Likes
11

It is not handling null values how to modify it

12

Capture230
getting this error

13

Can you elaborate? Also share input and expected output?
Basically overwrite expected value to the null then run the above logic.

14

same input and output but its getting error
can you please modify that the code should handle the null values as well

15

Hi,

If you read unnecessary rows in the ReadRange, can you try to add the following FilterDataTable activity?

image
image1144×440 37.6 KB

16

Book 1.xlsx (12.8 KB)

sheet1 input
sheet2 output

17

In my environment, it works without error as the following.
Did you set proper address of cell?

image
image842×806 39.7 KB

18

yes I have set the proper address of cell

19

in input b column is empty

20

Capture231

iam not getting jun-23 to feb24
I want headers also