Clustering Matrix values- how to sort and index?

[simz92]

Dear all,

I am trying to code a Macro which utilizes the Direct Clustering Algorithm.
As input, I have a binary matrix.
For example:

	123	22	101	56	310	12	944
a	1	0	0	1	0	0	1
b	1	1	0	0	0	0	1
c	1	0	1	0	0	0	1
d	0	0	0	0	1	0	0
e	1	0	0	1	0	0	1
f	1	0	0	1	1	1	1

[note that the first row and column are headers]

Now, the rows of the matrix are interpreted as a binary value, which then should be sorted descendingly:

First: Sort rows

	123	22	101	56	310	12	944
b	1	1	0	0	0	0	1
c	1	0	1	0	0	0	1
f	1	0	0	1	1	1	1
a	1	0	0	1	0	0	1
e	1	0	0	1	0	0	1
d	0	0	0	0	1	0	0

The row wise sorted matrix then should be sorted columnwise, also descendingly.

	123	944	22	101	56	310	12
b	1	1	1	0	0	0	0
c	1	1	0	1	0	0	0
f	1	1	0	0	1	1	1
a	1	1	0	0	1	0	0
e	1	1	0	0	1	0	0
d	0	0	0	0	0	1	0

What I have so far is the preperation to have arrays with the binary values stored:

Sub directcluster()
Dim lastrow As Integer, lastcolumn As Integer
Dim arraybinR(), arraybinC() As Double
Dim arraysum() As Integer

Dim i, j, k, l As Integer


lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lastcolumn = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

'fill blank cells with 0


For i = 2 To lastrow

    For j = 2 To lastcolumn
    
    If Cells(i, j) = "" Then
    Cells(i, j) = 0
    End If
    
    Next j
Next i

'concenate Matrix to binary arrays

ReDim arraybinR(lastrow)

For i = 2 To lastrow
    For j = 2 To lastcolumn
    arraybinR(i) = arraybinR(i) & Cells(i, j)
    Next j
Next i


'sort and rearrange matrix, row wise, descending




'same for columns

ReDim arraybinC(lastcolumn)

For k = 2 To lastrow
    For l = 2 To lastcolumn
    arraybinC(k) = arraybinC(k) & Cells(l, k)
    Next l
Next k


'sort and rearrange matrix, column wise, descending


'identify cluster and colour them

End Sub

What now puzzles me is: how to I sort an array and efficiently save its index, so I can swap the rows and columns? And also, if the problem gets bigger, lets say a matrix with 100+ columns and rows, does my way of concatenating the cells still work? I am already getting overflow errors when I use integers for the arrays arraybinC and arraybinR, I hope double arrays have enough storage...

Maybe someone can help me, that would be aweseome Please find a sample attached. (in the first sheet there is a sample problem, in the second there is how I imagine the code should work like)

BTW: If someone knows the last step, how to determine cluster and colour the matrix accordingly, please feel free to tell me As of now, I am trying to get the matrix first

Thank you so much and have a nice weekend!