Copy data from first sheet to 2nd sheet so as to consolidate the year month to a single column -date

[LearnerAnu]

Hi,
Please help me to consolidate data from 1st sheet to 2nd sheet such that the first sheet has year, month and days as column and rows and i want to copy this data to 2nd sheet as a single column date.
I have several sheets like this so i want a vba code for the same. I am not able to consolidate the data to one column.
Sample data format:

           1st sheet
            Year     Month       1969          1970              1971           .... so on to 2015
            Day     January 
              1                      data1        data101       data201 ....
              2                      data2        data102       data202.....
            so on to 31 January....

              1      February    data1        data101       data201 ....
              2                       data2        data102       data202.....
          so on to 28 or 29 February...
              1      March        data1         data101       data201 ....
              2                       data2         data102       data202.....
          so on to 31March...
          Till December...
   
          2nd sheet
            Date                   Data
            1-jan-1969          Data1
            2-jan-1969          Data2
            so on till
            1-jan-2015          Data100001

I will also attach the file for better view. Kindly help me device a code fr the same as i am unable to do it. Thanks in advance.

 

[LearnerAnu]

I have attached the file in the reply as the upload was failing during the post...Please have a look at the file.

 

[Nebu]

Hi:

Please find the attached . I had to use a macro to get this done.

Thanks

 

[Hui]

Using Formulas
in the RequiredData worksheet

A3: =A2+1
Copy down to Row 16694
I would copy Column A and paste it as Values overitself

then in
B2: =INDEX(Given!$A$1:$AV$446,1+MATCH(TEXT(A2,"MMMM"),Given!$B$3:$B$446,0)+MATCH(DAY(A2),OFFSET(Given!$A$3,MATCH(TEXT(A2,"MMMM"),Given!$B$3:$B$446,0)-1,,31,1),0),MATCH(YEAR(A2),Given!$A$3:$AV$3,0))

Copy down to Row 16694

see attached file:

 

[LearnerAnu]

Thanks both....
mission accomplished :D

 

[LearnerAnu]

Hi Hui,
I am getting no value for 31st of March,May,July...
I see i get correct values for 31st of January but for other months i get N/A....
i cannot seem to know why this is happening...Any suggestions?

 

[Hui]

Whoops my error
Change the formula in Required Data B2: to
=INDEX(Given!$A$1:$AV$446,1+MATCH(TEXT(A2,"MMMM"),Given!$B$3:$B$446,0)+MATCH(DAY(A2),OFFSET(Given!$A$3,MATCH(TEXT(A2,"MMMM"),Given!$B$3:$B$446,0)-1,,32,1),0),MATCH(YEAR(A2),Given!$A$3:$AV$3,0))

Copy down

 

[LearnerAnu]

Yeah...Thats done...Thanks!