Excel repeat each element in a row x times in individual rows

[salomon]

I consider as a simple MWE an Excel table that contains 7 (columns) and 3 rows. I try to repeat each element x times, so that the last column in this row runs to the last column in the entire table. The number of repetitions in each row is therefore determined by the last column in that row.

For example, in row 1 the last element is in column 3, so each element must be repeated 5 times (7 columns in total - 3 last position + 1).
In row 2 the last element is in column 5, so each element must be repeated 3 times (7 columns in total - 5 last position + 1).

I have a table with many columns and rows, but the general structure remains the same, where the first element has 3 values and 2 values are added at each row, with the individual values being different in all cells.

Any thoughts on how to achieve this in an automated way?

My initial spreadsheet looks like this:

	1​	2​	3​	4​	5​	6​	7​
1​	0.233​	0.777​	0.853​
2​	0.814​	0.947​	0.387​	0.119​	0.691​
3​	0.574​	0.231​	0.050​	0.453​	0.952​	0.786​	0.856​

The result should look like this:

	1​	2​	3​	4​	5​	6​	7​
1​	0.233​	0.233​	0.233​	0.233​	0.233​
1​		0.777​	0.777​	0.777​	0.777​	0.777​
1​			0.853​	0.853​	0.853​	0.853​	0.853​
2​	0.814​	0.814​	0.814​
2​		0.947​	0.947​	0.947​
2​			0.387​	0.387​	0.387​
2​				0.119​	0.119​	0.119​
2​					0.691​	0.691​	0.691​
3​	0.574​	0.231​	0.050​	0.453​	0.952​	0.786​	0.856​

 

[Eric W]

Welcome to the forum!

There might be a way to do this with formulas, but it seems more suited to a macro. Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Sub RedoTable()
Dim OutData(), MyData As Variant, MyIDs As Variant
Dim i As Long, j As Long, ctr As Long, k As Long, k2 As Long, r As Long

    MyIDs = Range("A2:A4").Value
    MyData = Range("B2:H4").Value
    ctr = 0
    For i = 1 To UBound(MyData)
        For j = 7 To 1 Step -1
            If MyData(i, j) <> "" Then
                ctr = ctr + 7 - j + 1
                Exit For
            End If
        Next j
    Next i
    ReDim OutData(1 To ctr, 0 To 7)
    
    r = 1
    For i = 1 To UBound(MyData)
        For j = 7 To 1 Step -1
            If MyData(i, j) <> "" Then
                If j = 7 Then
                    OutData(r, 0) = MyIDs(i, 1)
                    For k = 1 To 7
                        OutData(r, k) = MyData(i, k)
                    Next k
                    r = r + 1
                    Exit For
                End If
                For k = 1 To j
                    OutData(r, 0) = MyIDs(i, 1)
                    For k2 = 1 To 7 - j + 1
                        OutData(r, k + k2 - 1) = MyData(i, k)
                    Next k2
                    r = r + 1
                Next k
                Exit For
            End If
        Next j
    Next i
    
    Range("K2").Resize(ctr, 8) = OutData
    
End Sub

Change the ranges in red to match your sheet. Press Alt-Q to close the editor. In Excel, press Alt-F8 to get the macro selector. Select RedoTable and click Run. This is how it worked for me:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		1	2	3	4	5	6	7
2	1	0.233	0.777	0.853							1	0.233	0.233	0.233	0.233	0.233
3	2	0.814	0.947	0.387	0.119	0.691					1		0.777	0.777	0.777	0.777	0.777
4	3	0.574	0.231	0.050	0.453	0.952	0.786	0.856			1			0.853	0.853	0.853	0.853	0.853
5											2	0.814	0.814	0.814
6											2		0.947	0.947	0.947
7											2			0.387	0.387	0.387
8											2				0.119	0.119	0.119
9											2					0.691	0.691	0.691
10											3	0.574	0.231	0.050	0.453	0.952	0.786	0.856
Sheet9