1

The idea here is to create a backup table to enable a faster filling of repeated information in another worksheet.

Dataset:

Exam    Parameter   Step        System  Samples
b-HCG      OD       Calibration   1        5
TSH        OD       Calibration   2        3

where Col1 = Exam, Col2 = Parameter, Col3 = Step, Col4 = System

enter image description here

So I've been trying to repeat each line x times. X is defined by the nº of samples in each analysis and I would like to return the repeated set in a merged table. For example, all info in row 1 repeated 5 times, row 2 repeated 3 times and so on.

To make a dynamic formula, I tried the following:

=TRANSPOSE(SPLIT(REPT(B3&"|";F3);"|"))
=TRANSPOSE(SPLIT(REPT(C3&"|";F3);"|"))
=TRANSPOSE(SPLIT(REPT(D3&"|";F3);"|"))
=TRANSPOSE(SPLIT(REPT(E3&"|";F3);"|"))

enter image description here

By this I can get the repeated set but just for the first row. If I try to autofill the remaining rows with the above formula, the original formula is overwritten, and I get the repeated data set for the 2nd row instead.

Can I solve this with native formula only or is this only manageable by JavaScript?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Lucca
  • 13
  • 3
  • Rationale: The strategy mentioned [here](https://stackoverflow.com/a/70161140) is similar to the strategy in [this answer](https://stackoverflow.com/a/73880367). There is another answer(mine) providing a different strategy altogether. Furthermore, newer formulas are preferred than older formulas. It is better if this question is closed as duplicate rather than the newer one. – TheMaster Sep 30 '22 at 09:38

1 Answers1

0

try:

=INDEX(SUBSTITUTE(SPLIT(FLATTEN(SPLIT(QUERY(REPT(FLATTEN(
 QUERY(TRANSPOSE(SUBSTITUTE({"♦"&A2:A, B2:D}, " ", "♠")),,
 9^9)), E2:E),,9^9), "♦")), " "), "♠", " "))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Amazing, thanks for your help! I am curious about the code, what is the role of the symbols (♠, ♦) and the 9^9 in the formula? – Lucca Nov 30 '21 at 11:29
  • @Lucca ♦♠ are just separators for SPLIT to cut string exactly where we need. you can change it for whatever you like ♀♫♪♥♣☼►♂▬... 9^9 is a really big number also can be changed anything you wish 45665121, 9999999, 1541515445365.... it doesnt matter, it just needs tto be bigger than sum of all rows in the sheet – player0 Nov 30 '21 at 14:09