Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

**obot64** · 01-07-2014, 07:53 AM

Row to Matrix Conversion??? How To Turn 1 Row of Data into 16 columns of data:

I have a several set of data that are usually about 400 or 500 items long all in 1 row. I have been taking this data and separating it into columns by hand. Needless this to say this takes up a lot of time.
After searching online I originally though the TRANSPOSE function could be used, but I could not find a way to have it carry the data to a new line after transposing the first 16 characters.

I found a bit of VBA Code the other day that Takes one row of data and separates it into 3 columns of data. Unfortunately I need to separate my 1 row of data into 16 columns. How can I do this quickly?

Data example: Row of 35 items to table/Matrix. How would I Take a row of 35 items and separate it in to a table/Matrix that looks like this:

01 02 03 04 05 06
07 08 09 10 11 12
13 14 15 16 17 18
19 20 21 22 23 24
25 26 27 28 29 30
31 32 34 35

Source Data would look like this

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

**ragulduy** · 01-07-2014, 08:04 AM

If your source data was in column A then you could use a formula like this:
=INDEX($A$1:$A$35,(ROW(A1)-1)*6+COLUMN(A1))
in the top left cell of the matrix and then copy down/across to populate the other cells.

Where the 6 is the number of columns in your matrix/table.

**obot64** · 01-07-2014, 09:56 AM

Thanks for the help Yudlugar

Question for If row A had 500 values and i wanted spread them into 16 columns what would I change in the formula?

My guess is

=INDEX($A$1:$A$500,(ROW(A1)-1)*16+COLUMN(A1))

**ragulduy** · 01-07-2014, 09:57 AM

Yes I think that would work, why don't you try it?

**obot64** · 01-07-2014, 10:04 AM

yes it works! can you help me understand this formula a bit more so i can create my own in the future.

($A$1:$A$500,(ROW(A1)-1)*16+COLUMN(A1))

($A$1:$A$500,
do we have to define a range for Row a?

Could we use A:A to sort everything from a into the 16 columns?

**ragulduy** · 01-07-2014, 10:09 AM

The INDEX(a,n) returns the nth term of the array or range a.

So we set our range (a) to $A$1:$A$500 then we pick out the nth term.

So for the top left cell we have:
(ROW(A1)-1)*16+COLUMN(A1)

Row(A1) return the row number of A1 and COLUMN(A1) returns the column number of A1, so we have:
(1-1)*16+1 = 1, so we return the 1st cell in the Range a1:a500, which is a1.

when we drag one cell to the left we get
(ROW(A1)-1)*16+COLUMN(B1)
so we now have the column number of B1, which is 2:
(1-1)*16+2 = 2, so we return the 1st cell in a1:a500, which is a2

when we then move down a cell we have;
(ROW(B1)-1)*16+COLUMN(B1)
which gives
(2-1)*16+2 = 18, so we get the 18th cell, A18

and so on.

**obot64** · 01-07-2014, 10:15 AM

I understand how the formula is working now, thank you for the explanation.

**newdoverman** · 01-07-2014, 11:32 AM

Something that I just recently tried and found useful in this situation was to change the COLUMN(A1) to COLUMNS($A$1:A1). This allowed the following IF statement to be added so that when creating the matrix, an "over-run" in the selection of columns wouldn't result in a matrix that wasn't wanted.

Original formula by yudlugar for 16 columns:

Formula:

Please Login or Register  to view this content.

Formula limited to 16 columns by minor modification:

Formula:

Please Login or Register  to view this content.

The system blocked my attempt to give yudlugar a rep point so, kudos to you for your fine formula.

**obot64** · 01-07-2014, 01:58 PM

nice touch newdoverman, constraining it to 16 columns is a great feature!

Is there a way I can save this formula in excel so i do not have to coppy/paste it every time?

**newdoverman** · 01-07-2014, 02:55 PM

Why not make a workbook of useful Excel stuff that you can reference when you want?

Open a new workbook, then copy the formula then paste into Excel. It would help to have a small sample data to demonstrate what the formula is doing. Give the workbook a name and refer to it as required.

**ragulduy** · 01-08-2014, 05:18 AM

@Newdoverman,

I think that:
=IF(COLUMNS($A$1:A1)>16,"",INDEX($A$1:$A$35,(ROW(A1)-1)*16+COLUMNS($A$1:A1)))
is the same as:
=IF(COLUMN(A1)>16,"",INDEX($A$1:$A$35,(ROW(A1)-1)*16+COLUMN(A1)))

although I have seen people recommend that columns($A$1:A1) be used as it is more robust when inserting new columns.

With regards to saving it, you could build a UDF in vba and put it in your personal.xls workbook.

**newdoverman** · 01-08-2014, 09:51 AM

@yudlugar

I have heard the same thing about the COLUMNS($A$1:A1) being more robust but I haven't heard an explanation as to why. Seeing that the sources of the information are far better than I am at Excel, I took their word for it.

I have never tried making a UDF and have no experience with them. I must make time to see what they are all about and how to make use of them.

Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

LinkBack

Thread Tools

Rate This Thread

Display

Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Re: Row to Matrix Conversions? How To Turn 1 Row of Data into 16 columns of data:

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Creating a Matrix with Multiple Columns of Data

Convert Hourly Tidal Data Matrix to Column Data

Excel 2007 : Creating Macro's to Create Columns and turn change data structure

Matrix Build from 3 Columns of Data

[SOLVED] Turn vertical data to horizontal data automaticaly

Bookmarks

Bookmarks

Posting Permissions