MACRO NEEDED

[Guest]

I need a macro to convert a matrix of 2 columns and "n" rows into a matrix of
16 columns and n/8 rows, for example, to convert matrix AB to matrix A....P

A B A B C D E F G H I J K L M N O P
a1 b1 a1 b1 a2 b2 a3 b3...............................a8 b8
a2 b2 a9 b9
a10b10.......................................a16b16
.... ... .. .. .. .. . . .. . .. . . ... .. . .. . . . ..
.. . . .. . . ...
an bn
............................................................an bn

Tnkx

 

[bplumhoff]

Hello,

I suggest to take this UDF:
Function copy_row_wise(r As Range)
'Copy each element in r row-wise to selected area.
'PB V0.9
Dim ri As Range
Dim i As Long, j As Long
Dim vR As Variant

If TypeName(Application.Caller) <> "Range" Then
copy_row_wise = CVErr(xlErrRef)
Exit Function
End If

If Application.Caller.Rows.Count * Application.Caller.Columns.Count <>
_
r.Rows.Count * r.Columns.Count Then
copy_row_wise = CVErr(xlErrNum)
Exit Function
End If

ReDim vR(1 To Application.Caller.Rows.Count, _
1 To Application.Caller.Columns.Count)

i = 1
j = 1
For Each ri In r
If IsEmpty(ri) Then
vR(i, j) = ""
Else
vR(i, j) = ri
End If
j = j + 1
If j > Application.Caller.Columns.Count Then
j = 1
i = i + 1
End If
Next ri

copy_row_wise = vR

End Function

HTH,
Bernd

 

[Guest]

Sorry that I'm a beginer, but How do i use this function to create the new
matrix?

 

[bplumhoff]

Hello,

You press ALT + F11, insert a module, copy my source code into that
module and switch back to your Excel sheet.

Then you select your destination area and enter
=copy_row_wise(A1:B32)
as an array formula (enter with CTRL + SHIFT + ENTER). Make sure that
the number of source and destination cells match.

HTH,
Bernd

 

[Guest]

Thanks a Lot!

It works very good, there was a little syntaxis error on it.

Thanks again.

 

[Guest]

Hello,
Im sorry to bore you again, but I need to add a new action to the function
you defined. Any idea about how to get it?

Thanks a lot!

MATRIX:
NAME VAL1 VAL2
A a 1
A b 2
A c 3
B d 4
C e 5
C f 6
C g 7
C h 8
D i 9
D j 0
D k 1

Current Results
a 1 b 2
c 3 d 4
e 5 f 6
g 7 h 8
i 9 j 0
k 1

What I need..

A a 1 b 2
A c 3
B d 4
C e 5 f 6
C g 7 h 8
D i 9 j 0
D k 1

 

[bplumhoff]

Hello,

I think you need to define 5 calls to my function:

1. Select 4 cells in one row and enter =copy_row_wise(B1:C2) as array
formula.
2. Select 4 cells in two columns and two rows and enter
=copy_row_wise(B3:C4) as array formula.
3. Select 8 cells in four columns and two rows and enter
=copy_row_wise(B5:C8) as array formula.
4. Select 4 cells in one column and enter =copy_row_wise(B9:C10) as
array formula.
5. Select 2 cells in one column and enter =copy_row_wise(B11:C11) as
array formula.

HTH,
Bernd

 

[Guest]

Thanks for your hepl, but I need that the function do the breaks
automatically for every change in the first column (they are variables) cuz
the number of rows is too high