I have this Excel file with sheet "master data" i want to split it to multiple sheet year wise. plz help me out here

Almond_MasterData.xlsx (28.6 KB)

Hi,

Welcome to the forum.

What I would do is:

  1. read the data into a datatable dtMasterData
  2. start a loop with a counter intYear set to 2003 (or whatever is the earliest year in your data)
  3. filter dtMasterData into dtMasterData_fiiltered using Filter Data Table so only the year of the counter intYear is kept
  4. save dtMasterData_filtered into a sheet with a name same as the counter intYear
  5. increase counter intYear by 1, check whether it is now in the future - if not, goto 3.

Does this help?

Cheers
Andrzej

1 Like

What should be the filter data table criteria? As the date column in excel is in “23-Feb-2007” format of type string?

If it’s read as a string and always in the same format (year at the end), you could do something like this:

image

Cheers
Andrzej

1 Like

Thanks for that, but it would be great if you can share a demo workflow ,i am a newbee it will help me a lot.

I must say, you chose a pretty complicated task for a newbie.

Here’s a workflow for you: MasterData.zip (42.7 KB)

Those dates are interpreted as datetime, not a string, so I couldn’t simply use the Filter Data Table, I opted for a select instead:
(From p In dtMasterData.Select() Where Convert.ToString((p.Item(“PriceDate”))).Contains(intYear.ToString) Select p).ToArray.CopyToDataTable

It has to be closed in a try-catch though, as it throws an error if there are no rows for the given year.

Have a look and let me know if it works for you. There’s probably some more basic ways you could achieve the same result, so don’t get discouraged :slight_smile:

Cheers
Andrzej

1 Like

add data in seprate sheet according date.zip (46.2 KB)
your query has resolve kindly mark as solution I appreciate

Regard
Aleem Khan
Happy Automation

1 Like

if its helpful please mark as solution to close this topic

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