1

I have the following data in Excel:

start date  end date    number of repetition
 0             515            423
 0             484            982
 0             456           5,012
 0             425           1,063
 0             395           2,148

I need to generate for example 423 repetitions of corresponding start date and end date in a column like this:

0
515
0
515
...

And then the rest of the repetitions start after that in the same column..

7
  • I think you will have to use vbscript. Have you tried anything already?
    – Prasanna
    Jul 10, 2015 at 19:00
  • Here is the single item version of this. You may be able to modify it to repeat two items. If not, you could get it in two steps, repeating each label the number of times and then using a second formula to alternate the rows and go down a column: stackoverflow.com/questions/30805665/…
    – Byron Wall
    Jul 10, 2015 at 19:34
  • @ByronWall This is exactly what I'm looking for, but I don't know how to edit it to repeat two items...
    – Ferra Xu
    Jul 10, 2015 at 20:12
  • I don't either and I wrote the first one! Seriously, you might want to consider VBA or a multi step process. That other answer could be modified to work here, but this is much more easily handled with VBA.
    – Byron Wall
    Jul 10, 2015 at 20:13
  • @ByronWall Scott helped me to change your function for my problem but my data is changed it doesn't work for this data! He doesn't answer me anymore, can you please take a look at it to see whether you can help me or not? Thanks
    – Ferra Xu
    Jul 22, 2015 at 17:16

2 Answers 2

3

I was able to adapt Byron Wall’s answer to How to create a dynamic table in Excel? (on Stack Overflow) to alternate between the two columns:

  • E2=A2
  • E3=B2
  • E4=IF(
        INDEX(C$2:C$99, MATCH(E2,A$2:A$99), 1) > COUNTIF(E$2:E2, E2),
        E2,
        INDEX(A$2:A$99, MATCH(E2,A$2:A$99)+1, 1))
  • E5=IF(
        INDEX(C$2:$C99, MATCH(E2,A$2:A$99), 1) > COUNTIF(E$2:E2, E2),
        E3,
        INDEX(B$2:B$99, MATCH(E2,A$2:A$99)+1, 1))

Then select E4 and E5 together and drag down.  Of course you should replace 99 with the last row number of your source data (or something higher).

For testing/demonstration purposes, I

  • changed the repetition counts to something manageable, so I wouldn’t have to drag down to Row 828 (2×413+2) just to see 32 (A3) for the first time,
  • changed the end dates B3:B6, so it would be obvious that B3, B4, B5, and B6 were being displayed opposite A3, A4, A5, and A6 (and not B2 over and over again),
  • for display purposes only, split Column E into two pieces, so the image could be 20 rows high rather than 38.

     

This allows the date ranges to overlap, as above,

   or like this:                  or to be non-overlapping, like this:

start date  end date                                      start date  end date
     0         15                                              1         10
    10         25                                             11         20
    20         35                                             21         30
    30         45                                             31         40
    40         55                                             41         50

But it does not allow A values to have occurred on previous rows as B values

    like this:                        or like this:

start date  end date                                      start date  end date
     1          8                                              1         15
     8         15                                              8         22
    15         22                                             15         29
    22         29                                             22         36
    29         36                                             29         43

(i.e., the ranges must not be contiguous).  If you need to handle data like that, change E4 and E5 to:

  • E4=IF(
        INDEX(C$2:C$99, MATCH(E2,A$2:A$99), 1) >
                  SUMPRODUCT(--(E$2:E2 = E2), --(MOD(ROW(E$2:E2),2)=0)),
        E2,
        INDEX(A$2:A$99, MATCH(E2,A$2:A$99)+1, 1))
  • E5=IF(
        INDEX(C$2:C$99, MATCH(E2,A$2:A$99), 1) >
                  SUMPRODUCT(--(E$2:E2 = E2), --(MOD(ROW(E$2:E2),2)=0)),
        E3,
        INDEX(B$2:B$99, MATCH(E2,A$2:A$99)+1, 1))

I have formatted the start dates to be blue and the end dates to be yellow, for clarity:

 

2
  • Thank you for your answer. My data has been changed and all the start points are 0 now. On this new data it doesn't work...Please see the question for the new data..Thanks
    – Ferra Xu
    Jul 20, 2015 at 23:18
  • Nice adaptation of the original formula. Always crazy how far these things can be extended if you have the patience.
    – Byron Wall
    Jul 22, 2015 at 22:23
1

This works, but you have to manually adjust for each line. You can autofill this formula down however many times you need, it will start returning blank when it's done enough repetitions. To do the first row (1,516) you use:

=IF(ROW()<=C$2*2+1,IF(ISEVEN(ROW()),A$2,B$2),"")

Adjusting for the next row looks like:

=IF(ROW()<=C$3*2+1,IF(ISEVEN(ROW()),A$3,B$3),"")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .