I need a Macro to unwrap a data matrix.

[jblan]

Hello everyone!

Thanks for taking the time to read my post.

My situation is as follows:

I have 11 Sheets (Sheet1, Sheet2,etc..) each has 215 columns and 215 rows.


# 1 2 3 4 5 ....
1 0
2 .4 0
3 .2 .4 0
4 .1 .1 .1 0
5 .4 .5 .6 .2 0


Each column and row represents a point and the matrix created is a distance from point a to point b.

I would like to unwrap the matrix so that all this information is represented in 3 columns ( point a : point b : distance )

Does anyone know of a macro that can be created to help me out?

I really appreciate your time and kindness!
Cheers!

 

[wigi]

Hello

This could work, please test it:

Sub TableToColumn()

    Dim rng As Range
    Dim rngTable As Range
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
    
        Set rngTable = ws.UsedRange.Offset(1, 1).SpecialCells(2, 1)
        
        For Each rng In rngTable
            If rng.Value <> 0 Then
                
                ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array( _
                    rng.Parent.Cells(rng.Row, 1).Value, _
                    rng.Parent.Cells(1, rng.Column).Value, _
                    rng.Value)
            End If
                    
        Next
        
    Next


End Sub

 

[jblan]

I think it has worked out just great! Thank you so very much!

What I am seeing is that it appends the new formatted data to the last row, correct?

 

[wigi]

That's correct.

 

[jblan]

ray: