Almond_MasterData.xlsx (28.6 KB)
I have this Excel file with sheet "master data" i want to split it to multiple sheet year wise. plz help me out here
Welcome to the forum.
What I would do is:
- read the data into a datatable dtMasterData
- start a loop with a counter intYear set to 2003 (or whatever is the earliest year in your data)
- filter dtMasterData into dtMasterData_fiiltered using Filter Data Table so only the year of the counter intYear is kept
- save dtMasterData_filtered into a sheet with a name same as the counter intYear
- increase counter intYear by 1, check whether it is now in the future - if not, goto 3.
Does this help?
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:
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
add data in seprate sheet according date.zip (46.2 KB)
your query has resolve kindly mark as solution I appreciate
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.