# Simple matrix transformation

#### rolo

##### Member
Hello,

I have a matrix like this (N rows x N columns)

What I need is a macro to convert that matrix to 1 column x N rows matrix (without repetitions and ascending order). Like this:

See attached example

#### Attachments

• 8.8 KB Views: 2

#### Nebu

##### Excel Ninja
Hi:

Its really easy to do this using power query, find the attached.

Thanks

#### Attachments

• 17 KB Views: 2

#### Marc L

##### Excel Ninja
Hi !​
A beginner way :​
• Copy each data column to column F
• Sort column F
• Apply the Excel feature Remove Duplicates …

#### rolo

##### Member
I need a vba macro to do it

Thanks

#### rolo

##### Member
Hello, I was able to code this macro myself!

Code:
``````Function Convert(matrix As Range, destiny As Range)

Dim Counter As Long: Counter = 0

Range(destiny, destiny.End(xlDown)).Clear

Dim cell As Range

For Each cell In matrix

If cell <> Empty Then
destiny.Offset(Counter, 0) = cell
Counter = Counter + 1
End If

Next cell

End Function``````
Code:
``````Sub GoConvert()
ActiveSheet.Select 'select sheet where macro must execute
Call Convert(Range("A1:C4"), Range("A10"))
End Sub``````

Thanks for the other suggestions!

#### Marc L

##### Excel Ninja
Well done ‼​
As you can see easy stuff can often be achieved with Excel basics …​
Following my post #3 by copying column by column (instead of cell by cell like yours)​
without the need of the source data sheet active :​
Code:
``````Sub Range2UniqueColumn(Rg As Range, Dest As Range)
Dim Rc As Range, R&
Application.ScreenUpdating = False
Dest.CurrentRegion.Clear
For Each Rc In Rg.Columns
Rc.Copy Dest.Offset(R)
R = R + Rc.Cells.Count
Next
With Dest.Resize(R)