Forum Discussion
Conversion of data into single column
Okay. Let define named range for the values as
where first row is the row with years and couple of empty rows at the end as the gap.
Transferred data will put here
In BF3 is the formula to calculate the date starting from the first one in our range
=DATE(INDEX(rngValues,1,1),1,ROW()-ROW($BF$2))into BG3 we pick-up the value from the range
=IFNA(INDEX(rngValues,$BF3-DATE(YEAR($BF3),1,1)+2,MATCH(YEAR($BF3),INDEX(rngValues,1,0),0)),"out of range")Let stay on BF3 and in reference box will enter
Entire this range will be selected. Now press Ctrl+D, formulas from BF3:BG3 will be expanded till end of this range.
Right now for leap years with have zero for 31 Dec. I didn't add any values, add them yourself where necessary.
Please check in second sheet of the attached file.
Thanks for your reply. Yes, please add one more value so that leap year may also be added.
Kind regards,
S
Okay. Let define named range for the values as
where first row is the row with years and couple of empty rows at the end as the gap.
Transferred data will put here
In BF3 is the formula to calculate the date starting from the first one in our range
=DATE(INDEX(rngValues,1,1),1,ROW()-ROW($BF$2))into BG3 we pick-up the value from the range
=IFNA(INDEX(rngValues,$BF3-DATE(YEAR($BF3),1,1)+2,MATCH(YEAR($BF3),INDEX(rngValues,1,0),0)),"out of range")Let stay on BF3 and in reference box will enter
Entire this range will be selected. Now press Ctrl+D, formulas from BF3:BG3 will be expanded till end of this range.
Right now for leap years with have zero for 31 Dec. I didn't add any values, add them yourself where necessary.
Please check in second sheet of the attached file.
- Thanks a lot SergeiBaklan. It worked and solved my problem.
saleemch , you are welcome, glad to help