How to sort Columns data based on write range condition

I have one age column where I need to sort that based on one condition for example age is 35 then the bot will create one sheet 31-40 and write age range of 31-41 on that sheet then bot again read next row of age if age is 87 it will create one sheet 81-90 and write a range of 81-90 inside that sheet
please help me community.

1 Like

Hi

Hope the below steps would help you resolve this

  1. Say you are getting the input from user age through input dialog box activity or through a process variable or argument
    Named str_age

  2. At the same time we have a datatable named dt from which we need to filter the datatable for the give age

  3. Now use a assign activity like this

int_toprange = (Convert.ToInt32(str_age.ToString)/10)*10

and again another assign

int_toprange = int_toprange + 1

Where int_toprange is a variable of type int32

  1. And another assign to get the lower range

int_lowrange = int_toprange + 9

  1. Finally to get the filtered datatable use this expression in a assign activity

dt = dt.AsEnumerable().Where(Function(a) Convert.ToInt32(a.Field(of String)(“your columnname”).ToString)>= int_toprange AND Convert.ToInt32(a.Field(of String)(“your columnname”).ToString)<=int_lowrange).Copytodatatable()

  1. Now this dt can be written anywhere in a excel with nee sheet we want with WRTIE RANGE activity

Cheers @sandip_shahane

Read the excel in Datatable, use for each data row loop.

Use Build Datatable, to create the table for Age group. Say, two tables, one for 31-40 and another 81-90

Read the column for Age in a variable,convert it in integer using cint(variable)and then add if condition for the range you want. Like if intAge > 30 AND intAge < 41, in then block you can add data row in the datatable you build for 31-40 using Add Data Row, add {Age} , add the age variable you reading from datatable in that, same for other ranges you need. May be you can have nested if too.

Then once all the rows are processed.

Simple paste the values using Write Range.
Datatable for 31-40 range in the same file but in a sheet named as 31-40. and so on

The write range will create the sheet of not already there and in this way you’ll have sheets of those range that are present in original excel data.

Hope this helps!

I tried this one but not find an answer my question is this “Divide these records as per Age Group, 0-10, 11-20, 21-30, 31-40, 41-50 and so on… and store in to separate sheet in an excel for each age group.”

Please give me some hint

Hey @sandip_shahane

did you try the above mentioned 2nd solution?

That is working.

Yes I was try that also inside that solution condition is done but write range is not working, it was add just one row data in each sheet , when I used message box to display age in inside that condition it goes right but write range not working

It worked for me, hope this helps…

You can use switch in case you have more ranges to catch. Logic should be clear.

ToCheck.zip (9 KB)

Appreciate you tried :+1:

Thank-you @rahulsharma

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.