Manipulating a matrix of data
 

I am trying to change data from:

A 1 2 3
B 1 2
C 1 2 3 4

(presume that is 3 rows and 5 columns)

to

A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3
C 4

Can anyone help with formula, or hints on how to get there?

Manipulating a matrix of data
 

This puts the transformed data in the 12th column (column L) and doesn't
supplant the existing data. Assumed therefore is that the range of numbers in
any row does not extend that far. Change to suit if they do. The code can of
course be changed to put the transformed data in a different worksheet etc.
Also assumed is that the data starts in cell A1 and ranges down an indefinite
number of rows. Change the cell reference to suit.

After generating the new data, you can check it for accuracy and paste it
over the existing data. The code also handles the situation where there are
no numbers following the letter. It will only return the letter in the
transformed data. If this can never happen then it could be simplified a
little.

Sub TransformData()
Dim r As Range
Dim i As Long, ii As Long
Dim x As Long, xx As Long

Set r = Range(Range("A1"), Range("A1").End(xlDown))
For i = 1 To r.Count
x = IIf(r(i, 2).Value = "", 2, Range(r(i), r(i).End(xlToRight)).Count)
For ii = 2 To x
xx = xx + 1
r(xx, 12).Value = r(i).Value & r(i, ii).Value
Next
Next
End Sub

Greg

"Magenta" wrote:

I am trying to change data from:

A 1 2 3
B 1 2
C 1 2 3 4

(presume that is 3 rows and 5 columns)

to

A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3
C 4

Can anyone help with formula, or hints on how to get there?

Manipulating a matrix of data
 

I am trying to change data from:

A 1 2 3
B 1 2
C 1 2 3 4

(presume that is 3 rows and 5 columns)

to

A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3
C 4

Can anyone help with formula, or hints on how to get there?

Here's a way that might get things started. It's not elegant, but it lets
you see both the original data and the reorganized data at the same time,
and lets you edit the original data with the reorganized data tracking
it.

Begin with the data in columns F:R (insert blank columns if needed). The
result will be in A:B. Helper columns are C:E (doncha love-'em?).

In E1 put 0 (zero).

In E2 put
=E1+COUNTA(G1:R1)
and copy down to one row past the end of column F (at least).

In A1 put
=IF(ROW()MAX(E:E),"",VLOOKUP(ROW()-1,E:G,2))
and copy down for the maximum number of rows you could need in the
reorganized data.

In C1 put 0 (zero).

In C2 put
=IF(A1=A2,C1,C1+COUNTIF(E:E,ROW()-1))
and copy down as far as column A goes. (The COUNTIF is for the case when
a row has columns G:R all empty.)

In D1 put 1 (one).

In D2 put
=IF(A2=A1,D1+1,1)
and copy down as far as column A goes.

In B1 put
=IF(A1="","",OFFSET($F$1,C1,D1))
and copy down as far as column A goes.

Hide columns (or use more than one worksheet) if you want less clutter.

Limitation: this doesn't work if (for example) H4 contains a value but H3
is empty.

Manipulating a matrix of data
 

Thank you both very much. Both solutions work satisfactorily and will enable
me to transform my data.