Making a Matrix into a column.......

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex Rauket - 21 May 2008 14:46 GMT

I have what seems to be an easy problem which I am unable to solve....

I have a matrix, lets say

A B C D
E F G H
I J K L

and I want to extrat every 6th element reading from left to right, i.e. F, L
etc.....

Any ideas? I though I could put the matrix into a vertical series, i.e.
A
B
C
D
etc....
and then choose every 6th number but I can't even get that far!!!!

Thanks for any help....

Reply to this Message

Stefi - 21 May 2008 15:30 GMT

Try this macro! Adjust values for mrows and mcols to suit your matrix sizes!

Sub element6()
Dim c6 As Range
mrows = 3
mcols = 4
For melem = 1 To mrows
Range(Cells(melem, 1), Cells(melem, mcols)).Select
Selection.Copy
Cells((melem - 1) * mcols + 1, mcols + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next melem
Application.CutCopyMode = False
ccounter = 0
For Each c6 In Range(Cells(1, mcols + 1), Cells(mrows * mcols, mcols + 1))
If c6.Row Mod 6 = 0 Then
ccounter = ccounter + 1
Cells(ccounter, mcols + 2).Value = c6.Value
End If
Next c6
End Sub

Regards,
Stefi

„Alex Rauket” ezt írta:

> I have what seems to be an easy problem which I am unable to solve....
>
[quoted text clipped - 16 lines]
>
> Thanks for any help....

Reply to this Message

Alex Rauket - 21 May 2008 17:24 GMT

I'm sorry but I'm not familiar with using macros in excel. How would I
implement this trick?

Thanks

> Try this macro! Adjust values for mrows and mcols to suit your matrix sizes!
>
[quoted text clipped - 45 lines]
> >
> > Thanks for any help....

Reply to this Message

Stefi - 22 May 2008 10:18 GMT

This is an improved version:

Sub element6_2()
Dim c6 As Range, topleft As Range, bottomright As Range
Set topleft = Range("E15") 'replace E15 by cell reference of the top
left cell of the matrix
mrows = 5 'replace 5 by No of rows of the matrix
mcols = 4 'replace 4 by No of columns of the matrix
Set bottomright = Cells(topleft.Row + mrows - 1, topleft.Column + mcols
- 1)
ccounter = 0
c6counter = 0
For Each c6 In Range(topleft, bottomright)
ccounter = ccounter + 1
If ccounter Mod 6 = 0 Then
c6counter = c6counter + 1
Cells(topleft.Row + c6counter - 1, topleft.Column + mcols).Value
= c6.Value
End If
Next c6
End Sub

To implement macro:
Press Alt+F11 (Microsoft Visual Basic window appears)
If you don't see Project window on the left, press Ctrl+R
Right click on VBAProject(yourxlsfilename)
Choose Insert>Module from the local menu, yourxlsfilename - appears
Copy macro code into Module1(Code) window

To run macro:
Make sure the cursor is inside the macro code and press F5
You can also assign a hot key to the macro in Excel's Alt+F8>Options.

Regards,
Stefi

„Alex Rauket” ezt írta:

> I'm sorry but I'm not familiar with using macros in excel. How would I
> implement this trick?
[quoted text clipped - 50 lines]
> > >
> > > Thanks for any help....

Reply to this Message

Stefi - 22 May 2008 12:01 GMT

I was thinking a little bit and realized that the job can be done without VBA:

Being your matrix in columns A:D, enter formula
=ROW()*6 (6 represents looking for every 6th element)
in E1, drag it down as necessary (in your example to row 3)
enter No of columns of matrix in F1 (in your example 4)
enter formul
=INDIRECT(ADDRESS(CEILING(E1/$F$1,1),IF(MOD(E1/$F$1,1)>0,MOD(E1/$F$1,1),1)*4,4,1))
in G1, drag it down as necessary (in your example to row 3)
It will return F L R in G1:G3

Regards,
Stefi

„Stefi” ezt írta:

> This is an improved version:
>
[quoted text clipped - 88 lines]
> > > >
> > > > Thanks for any help....

Reply to this Message