Basically, I have a dataset with three columns: Start date, end date, and number of replications. I need to generate one column data from this dataset like following regarding the number of replications for each start and end date:

start date
end date
start date
end date
...

I asked the same question here: How to generate repetitions in excel?. Someone helped me with a very useful function to generate the desired table but my data has been changed and for this new data this function doesn't work properly. (For the question in here How to generate repetitions in excel? my data was different (you can see the original data in the answer by @Scott, later I changed my data and now all the start dates are zero. The proposed function in the answer does not work for that)

  • 1
    The problem seems to be the same as in the linked question, please describe the difference, also please let us know why the previous solution doesn't work. – Máté Juhász Jul 23 '15 at 17:05
up vote 0 down vote accepted

I would use the Power Query Add-In for this. It has powerful commands for manipulating data. There are functions underneath, but you can achieve a lot by just clicking the buttons in the Power Query window.

I had a go at this particular challenge and got it working. You can download and use my demo file from my OneDrive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

It's the file: Power Query demo - generating controlled replications.xlsx

As described on the ReadMe sheet, I only had to write one simple function - the rest was built by clicking around the UI.

  • Thank you. I couldn't find "Power Query demo - generating controlled replications.xlsx". In the link I just saw "Power Query demo - searching for duplicated codes in a list of descriptions.xlsx" – Ferra Xu Jul 24 '15 at 16:31
  • Sorry - I've fixed that link. – Mike Honey Jul 26 '15 at 1:56
  • Is there a legitimate reason for changing all of these OneDrive links to the short form? – random Oct 5 '15 at 3:21
  • I'm curious if anyone is clicking on them or not. There tends to be minimal/no feedback from SU. Full OneDrive links don't provide any stats, but bitly (shortened version) does. – Mike Honey Oct 5 '15 at 3:35
  • Tracking users is not a valid reason to use shortened links – random Oct 5 '15 at 16:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.