Forum Discussion
NUMBERS IN COLUMNS TO
NUMBERS IN COLUMNS
In one sheet I have many groups (about 300 groups) of 5 numbers in column.
These numbers change every 5 minutes.
I need to convert these numbers in another sheet in many rows of 5 numbers each one.
Example column:
3
2
1
3
4
9
7
3
6
7
I want these rows in another sheet:
3 2 1 3 4
9 7 3 6 7
2 Replies
As variant that could be
with formula
=INDEX($B:$B,(ROW()-ROW($D$7))*5+ROW($B$2)+COLUMN()-COLUMN($D$7),1)dragged within target range.
robgiuss1960 If you in to a power query solution, have a look at the attached file. It takes your list of numbers as a structured Excel table ("Table1") or as a dynamic named range (rngNumbers"). The applied steps are basically the same.
1. Add index
2. Insert Modulo (5)
3. Pivot the Modulo column
4. Fill upp the columns 1, 2, 3 and 4
5. Filter out "null" from column 0
6. Delete the index column
7. Close and load to a table in a new sheet.
Import your new numbers list in the relevant location. Select Data ribbon, press Refresh All.
