I have values {A, B, C, D, E} and I would like to repeat them {n, m, o, p, q} times in another column.
| Values | Count |
|---|---|
| A | 3 |
| B | 6 |
| C | 5 |
| D | 4 |
| E | 2 |
| Desired |
|---|
| A |
| A |
| A |
| B |
| B |
| B |
| B |
| B |
| B |
| C |
| C |
| C |
| C |
| C |
| D |
| D |
| D |
| D |
| E |
| E |
Two Caveats:
There will be a variable number of values, so I need to use something equivalent to ARRAYFORMULA().
The values have many characters (not just a single letter) and the count can be in the thousands. I've tried using:
=ARRAYFORMULA(FLATTEN(TRANSPOSE(SPLIT(REPT(A2:A&"|",D2:D),"|"))))
However, I get an error: "Text result of REPT is longer than the limit of 32000 characters."
I'm thinking a formula in my "Desired" column using ARRAYFORMULA(ROW(C2:C)) to compare with a cumulative count of the values should be able to suggest which value is correct:
| Values | Count | Cumulative |
|---|---|---|
| A | 3 | 3 |
| B | 6 | 9 |
| C | 5 | 14 |
| D | 4 | 18 |
| E | 2 | 20 |