NUMBERS IN COLUMNS TO

%3CLINGO-SUB%20id%3D%22lingo-sub-1660117%22%20slang%3D%22en-US%22%3ENUMBERS%20IN%20COLUMNS%20TO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660117%22%20slang%3D%22en-US%22%3E%3CP%3ENUMBERS%20IN%20COLUMNS%3C%2FP%3E%3CP%3EIn%20one%20sheet%20I%20have%20many%20groups%20(about%20300%20groups)%20of%205%20numbers%20in%20column.%3C%2FP%3E%3CP%3EThese%20numbers%20change%20every%205%20minutes.%3C%2FP%3E%3CP%3EI%20need%20to%20convert%20these%20numbers%20in%20another%20sheet%20in%20many%20rows%20of%205%20numbers%20each%20one.%3C%2FP%3E%3CP%3EExample%20column%3A%3C%2FP%3E%3CP%3E3%3C%2FP%3E%3CP%3E2%3C%2FP%3E%3CP%3E1%3C%2FP%3E%3CP%3E3%3C%2FP%3E%3CP%3E4%3C%2FP%3E%3CP%3E9%3C%2FP%3E%3CP%3E7%3C%2FP%3E%3CP%3E3%3C%2FP%3E%3CP%3E6%3C%2FP%3E%3CP%3E7%3C%2FP%3E%3CP%3EI%20want%20these%20rows%20in%20another%20sheet%3A%3C%2FP%3E%3CP%3E3%202%201%203%204%3C%2FP%3E%3CP%3E9%207%203%206%207%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1660117%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660177%22%20slang%3D%22en-US%22%3ERe%3A%20NUMBERS%20IN%20COLUMNS%20TO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302266%22%20target%3D%22_blank%22%3E%40robgiuss1960%3C%2FA%3E%26nbsp%3BIf%20you%20in%20to%20a%20power%20query%20solution%2C%20have%20a%20look%20at%20the%20attached%20file.%20It%20takes%20your%20list%20of%20numbers%20as%20a%20structured%20Excel%20table%20(%22Table1%22)%20or%20as%20a%20dynamic%20named%20range%20(rngNumbers%22).%20The%20applied%20steps%20are%20basically%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Add%20index%3C%2FP%3E%3CP%3E2.%20Insert%20Modulo%20(5)%3C%2FP%3E%3CP%3E3.%20Pivot%20the%20Modulo%20column%3C%2FP%3E%3CP%3E4.%20Fill%20upp%20the%20columns%201%2C%202%2C%203%20and%204%3C%2FP%3E%3CP%3E5.%20Filter%20out%20%22null%22%20from%20column%200%3C%2FP%3E%3CP%3E6.%20Delete%20the%20index%20column%3C%2FP%3E%3CP%3E7.%20Close%20and%20load%20to%20a%20table%20in%20a%20new%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImport%20your%20new%20numbers%20list%20in%20the%20relevant%20location.%20Select%20Data%20ribbon%2C%20press%20Refresh%20All.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660375%22%20slang%3D%22en-US%22%3ERe%3A%20NUMBERS%20IN%20COLUMNS%20TO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660375%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302266%22%20target%3D%22_blank%22%3E%40robgiuss1960%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20599px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217968iCA3AAAAD94573893%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24B%3A%24B%2C(ROW()-ROW(%24D%247))*5%2BROW(%24B%242)%2BCOLUMN()-COLUMN(%24D%247)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edragged%20within%20target%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@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. 

@robgiuss1960 

As variant that could be

image.png

with formula

=INDEX($B:$B,(ROW()-ROW($D$7))*5+ROW($B$2)+COLUMN()-COLUMN($D$7),1)

dragged within target range.