Creating a matrix with simple algebra

• Hi guys,

Sorry very new to this but was wondering whether could someone help me out with my problem?

I have a table similar to the one as below. I have around 300 rows
[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td]

Malaysia

[/td]

[td]

10

[/td]

[/tr]

[tr]

[td]

Australia

[/td]

[td]

6

[/td]

[/tr]

[tr]

[td]

Singapore

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

United Kingdom

[/td]

[td]

5

[/td]

[/tr]

[tr]

[td]

China

[/td]

[td]

1

[/td]

[/tr]

[/TABLE]

Trying to find the difference between each and every row value in the second column and output it into a matrix like below
[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td][/td]

[td]

Malaysia

[/td]

[td]

Australia

[/td]

[td]

Singapore

[/td]

[td]

United Kingdom

[/td]

[td]

China

[/td]

[/tr]

[tr]

[td]

Malaysia

[/td]

[td]

x

[/td]

[td]

x

[/td]

[td]

x

[/td]

[td]

x

[/td]

[td]

x

[/td]

[/tr]

[tr]

[td]

Australia

[/td]

[td]

4

[/td]

[td]

x

[/td]

[td]

x

[/td]

[td]

x

[/td]

[td]

x

[/td]

[/tr]

[tr]

[td]

Singapore

[/td]

[td]

7

[/td]

[td]

3

[/td]

[td]

x

[/td]

[td]

x

[/td]

[td]

x

[/td]

[/tr]

[tr]

[td]

United Kingdom

[/td]

[td]

5

[/td]

[td]

1

[/td]

[td]

-2

[/td]

[td]

x

[/td]

[td]

x

[/td]

[/tr]

[tr]

[td]

China

[/td]

[td]

9

[/td]

[td]

5

[/td]

[td]

2

[/td]

[td]

4

[/td]

[td]

x

[/td]

[/tr]

[/TABLE]

Greatly appreciate the help

• Hi Jack,

The first thing you should do is copy your table into sheet1, then add a button and add my code into the button-->click button and see your result is in sheet2. Hope can help.

Private Sub CommandButton1_Click()
Dim RowIndex, ColunmIndex, max As Integer
max = 5
For ColunmIndex = 2 To max + 1
For RowIndex = max To 1 Step -1
If RowIndex > ColunmIndex - 1 Then
Sheets("Sheet2").Range(Split(Cells(1, ColunmIndex).Address, "\$")(1) & Trim(Str(RowIndex + 1))).Value = _
Sheets("Sheet1").Range(Split(Cells(1, 2).Address, "\$")(1) & Trim(Str(ColunmIndex - 1))).Value - _
Else
Sheets("Sheet2").Range(Split(Cells(1, ColunmIndex).Address, "\$")(1) & Trim(Str(RowIndex + 1))).Value = "x"
End If
Next RowIndex

Next ColunmIndex
End Sub

• More information, if you have 300 rows, then you can change the max to 300.

• How does this work on a copy your Workbook?
Sheet2 needs to be there and empty.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!