Hi !

I have data that is organized like this

Grid Date
1 11/10/12
1 11/10/12
1 15/10/12
1 1/1/13
1 5/1/13
1 10/1/13
2 4/12/12
2 5/12/12
2 7/12/12
2 10/4/13
3 1/1/12
3 2/1/12
3 3/1/12
3 4/1/12
4 10/6/11
4 11/6/11
4 15/6/11
4 17/6/11
5 19/6/11
5 20/5/11
5 22/5/11

So what I'd like to know is how many days each grid has. For eg:grid 1 has been sampled form 11/10/12 to 15/10/12 in Oct, that's 5 days and similarly in January 13 it has 5 days. grid 2 worked in dec12 for 4 days from 4th to 7th.

So I would like my output to look like this

grid Jan11 Feb11 MAr11 ....Oct12...dec12
1 5 0 0 5 0
2 0 0 0 0 4

Please help !
Thanks in advance!

Hi Nitya,

Am Mon, 10 Aug 2015 06:15:46 -0700 (PDT) schrieb Nitya Satheesh:

Grid Date
1 11/10/12
1 11/10/12
1 15/10/12
1 1/1/13
1 5/1/13
1 10/1/13
2 4/12/12
2 5/12/12
2 7/12/12
2 10/4/13
3 1/1/12
3 2/1/12
3 3/1/12
3 4/1/12
4 10/6/11
4 11/6/11
4 15/6/11
4 17/6/11
5 19/6/11
5 20/5/11
5 22/5/11

grid Jan11 Feb11 MAr11 ....Oct12...dec12
1 5 0 0 5 0
2 0 0 0 0 4

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Grid"
There is a suggestion with formula and one with Pivot.
I would prefer Pivot.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Monday, August 10, 2015 at 10:39:01 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 06:15:46 -0700 (PDT) schrieb Nitya Satheesh:

Grid Date
1 11/10/12
1 11/10/12
1 15/10/12
1 1/1/13
1 5/1/13
1 10/1/13
2 4/12/12
2 5/12/12
2 7/12/12
2 10/4/13
3 1/1/12
3 2/1/12
3 3/1/12
3 4/1/12
4 10/6/11
4 11/6/11
4 15/6/11
4 17/6/11
5 19/6/11
5 20/5/11
5 22/5/11

grid Jan11 Feb11 MAr11 ....Oct12...dec12
1 5 0 0 5 0
2 0 0 0 0 4

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Grid"
There is a suggestion with formula and one with Pivot.
I would prefer Pivot.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi !
Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

Can this be done?
Please help.
Thanks,
Nitya

Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Tuesday, August 11, 2015 at 11:34:03 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi,
Thanks a lot Claus! This works perfectly !
I'm so sorry, but if you don't mind , could you please explain the formula,I didn't quite get it.

Hi Nitya,

Am Tue, 11 Aug 2015 01:37:17 -0700 (PDT) schrieb Nitya Satheesh:

On Tuesday, August 11, 2015 at 11:34:03 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I'm so sorry, but if you don't mind , could you please explain the formula,I didn't quite get it.

In the E1:Z1 I have dates with always the first of month.In E2:E6 there
a the unique values of the grid.
In F2 I check if there is a Max if the grid in column A is the grid of
E2 and the month and the year in column B is month and year of F1.
If so I substract the Max with this conditions and the Min with this
conditions and add 1 for the start day because the difference is one day
less than the count of days.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

i am agree for a job