How i can repeat data vertically according to some numbers?

estatistics · May 9, 2025, 9:30am

in b1:d5, i have these data:
width,height
3,4
4,3
3,4
5,5

width represents the width of data (eg. 3 means 3 numbers) horizontally.
Height represents how many these data will be repeated vertically.

in b9:e19 i would like to produce these data.
as you can see, horizontally numbers are incremental until the given width.
How i can produce these data from width and height in librecalc?
chatgpt failed to give me a working solution

1,2,3
1,2,3
1,2,3
1,2,3
1,2,3,4
1,2,3,4
1,2,3,4
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5 1,2,3,4,5

opengpt suggested this formula. but it does not stop to produce more than i want
=IF(COLUMN()-COLUMN($B$9)+1 <= INDEX($B$2:$B$5; MATCH(ROW()-ROW($B$9)+1; $D$2:$D$5; 1)); COLUMN()-COLUMN($B$9)+1; "") it is based on cumulative height in “d column”

Lupp · May 9, 2025, 9:47am

Don’t tell me about chatgpt. It may waste energy.
Attach an example file where you show by significant examples

what you have
what you want to get
what you already tried.

estatistics · May 9, 2025, 9:54am

lets height 2,3,3,2 vertically in a1.
dt_example.ods (14.2 KB)

	based on height i want
	1
first height 1-2	2
	1
	2
2nd height 1-3	3
	1
	2
3rd height 1-3	3
	1
4rd height 1-2	2

Lupp · May 9, 2025, 10:07am

What I asked for:

I would asume you didn’t read that term the first time in your life.

estatistics · May 9, 2025, 10:09am

attached the file you required

JohnSUN · May 9, 2025, 10:25am

Yes, the file exists, but it does not match the description from the first message, from the text of the question.

Function repeat_data_vertically(aWidth As Variant, aHeight As Variant) As Variant 
Dim aResult As Variant 
Dim i As Long, j As Long, m As Long, maxWidth As Long, iHeight As Long 
	For i = LBound(aWidth) To UBound(aWidth)
		If maxWidth < aWidth(i,1) Then maxWidth = aWidth(i,1)
		iHeight = iHeight + aHeight(i,1)
	Next i
	ReDim aResult(0 To iHeight, 0 To maxWidth)
	For i = 0 To maxWidth
		For j = 0 To iHeight
			aResult(j, i) = ""
		Next j
	Next i
	iHeight = -1
	For i = LBound(aWidth) To UBound(aWidth)
		For m = 1 To aHeight(i, 1)
			iHeight = iHeight + 1
			For j = 0 To aWidth(i, 1) - 1 
				aResult(iHeight, j) = j +1
			Next j
		Next m
	Next i
	repeat_data_vertically = aResult
End Function

Repeat data vertically according to some numbers.ods (11.9 KB)

estatistics · May 9, 2025, 10:30am

i simplified my example in comments. you can use the simplified version.

I want a formula solution

Lupp · May 9, 2025, 1:05pm

Unfortunately I’m not friend with a chatbot whom I could ask how to ask chatbots to get reasonable answers.
Fortunately I’m living in München (Munich) where we have the
Max-Planck-Institut für Biologische Intelligenz.
I was reminded by them that humans should be intelligent themselves to some degree.
I tried and my first result was that a sheet desingned the way decribed by the OQ (OriginalQuestioner) is a bad idea.
The next hunch was that helper cells (in columns mostly) are advantageous if you want to get understandable maintainable, and scalable solutions.

Some additional considerations lead me to a real example which I can present as an .ods file:
disask121871StrangeSequencesWithRepetitions.ods (19.0 KB)
Ask your chatbots if they understnd it.

fpy · May 9, 2025, 10:51am

https://help.libreoffice.org/latest/en-US/text/scalc/01/func_sequence.html

estatistics · May 9, 2025, 10:52am

finally, opengpt produced a working solution! if a1:a4 are the heights and in b1:b4 the cumulative heights, and formula entered in c1, then =IF(ROW(C1) > $B$4, "", ROW(C1) - IFERROR(INDEX($B$1:$B$4, MATCH(ROW(C1)-1, $B$1:$B$4, 1)), 0)) if the numbers are in different row eg. 36th, then =IF(ROW(c36) > $b$39 + 35; ""; ROW(c36) - 35 - IFERROR(INDEX($b$36:$b$39; MATCH(ROW(c36)-36; $b$36:$b$39; 1)); 0))