Search code examples
google-sheetsgoogle-sheets-querygoogle-sheets-formula

Duplicating data in one cell as per repeat counter

I got into a bit of problem. I have data like this

   Date     Repeat

 7-Oct-2018    1
 8-Oct-2018    1
 9-Oct-2018    2
10-Oct-2018    2
11-Oct-2018    3
12-Oct-2018    2
13-Oct-2018    1

Now the result i want is a column where the date is repeated as per the repeat numbers, like this.

    Result

 7-Oct-2018
 8-Oct-2018
 9-Oct-2018
 9-Oct-2018
10-Oct-2018
10-Oct-2018
11-Oct-2018
11-Oct-2018
11-Oct-2018
12-Oct-2018
12-Oct-2018
13-Oct-2018

So how can i get this result. Please do help

Solution

  • I tried to think of it for own study. For example, how about this sample formula? I think that there might be simpler formulas. So please think of this as one of them. When you use this, please put the following formula to a cell.

    =TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(A2:A8&",",B2:B8))),","))

    This formula supposes that the values of Date and Repeat are put in A2:A8 and B2:B8, respectively.

    • Show repeatedly Date using REPT(). At this time, , is used as a delimiter.
    • Join each cell with , using JOIN().
    • Split the cell with , using SPLIT().
    • Transpose the cells using TRANSPOSE().

    I think that you can also use CONCATENATE() and TEXTJOIN() for joining cells.

    References: