Constructing a Tridiagonal Matrix from 3 Columns and x Number Rows of Data

[w1ngz3ro]

Hi Everyone,

I am trying to automate the construction of a tridiagonal matrix in Excel. I have attached a picture to illustrate what I am trying to do. All the nonzero elements in the matrix would reference the array shown in the picture, which is constituted by 101 rows and 3 columns of numerical data. Assuming that this can be done in Excel at all, I am hoping that scripting will not be necessary because I am unfamiliar with VBA syntax but if scripting is necessary I would appreciate it if someone could provide a snippet of code to get me started.

 

[Gerald Higgins]

Hi, welcome to the board.

This may well be do-able in Excel, with or without script, but personally I don't understand what you're trying to do.
How exactly is this matrix supposed to be assembled ?

I'm guessing that rows A:E (and I'm guessing the column references here) are your inputs, and columns H:Xx are your outputs.
But I don't understand the relationship between them.

Can you explain in more detail please ?

Don't worry too much about explaining the real world application of this data, just focus on the numbers, and what the relationship is between the inputs and outputs.

 

[w1ngz3ro]

Hi Gerald,

Thank you for your reply. So my inputs are on the left and my outputs are on the right. I have color coded the specific element inputs and their corresponding element outputs to match each other in color. The relationship between the inputs and their corresponding outputs is that the inputs and outputs are equal (identical). In other words, the red numbers on the left are simply equal to the red numbers on the right. The same goes for green numbers on the left and green numbers on the right and the blue numbers on the left and blue numbers on the right. The key difference though is that, on the right, the numbers are PROPERLY ASSEMBLED. I am trying to make an excel document that assembles a triadiagonal matrix using the numbers on the left (the inputs). I have attached a picture of a tridiagonal matrix below. All blank elements in the picture are zero.

 

[Gerald Higgins]

OK, this is probably do-able.

Some follow up questions.

1) What's happened to the green number on row 12 ?
Is it significant that that did not make it across ?

2) Is there any significance to the data in the first two columns, headed INDEX and T ?

3) Is there any significance to the fact that data in rows 18 downwards does not make it across ?

4) Is there any significance to the phrase "PROPERLY ASSEMBLED" or do you just mean they've been re-arranged into that diagonal grid ?

 

[w1ngz3ro]

ADVERTISEMENT

Hi Gerald,

1. The answer to this question necessitates a discussion about the specific application associated with this problem. It might be a lengthy discussion and I do not think it is necessary so I think the main takeaway should be that the uppermost and leftmost element in the assembled matrix should be the column D, row 12 element (-9.46).

2. The "T" stands for temperature. It is only a variable associated with the specific application associated with this problem and is not significant to the assemblage of the matrix. "INDEX" is just an indicator of the number of derivatives that have been calculated as one drags the table down further and further (starting from the top). It is not significant to the problem of assembling a tridiagonal matrix using only the three colored columns.

3. I would like the data in row 18 down to row 100 to make it across but I would like that to happen automatically. What I have posted on the right of my excel sheet was created manually by me to demonstrate what I would like to automate (program if necessary). I didn't want to manually create the entire matrix because it would have been too big and it would have been tedious.

4. I only meant that they have been re-arranged into that diagonal grid.

 

[ebea]

Hi! Have you tried, to use a VLOOKUP ? By this, you can use your index numbers, and (columns dTi-1 dTi dTi+1), then copy down as long the Columns you have to cover = numbers of rows, in your index.
But if I understand your explanation correct, it will end up with a very large numbers of columns (= rows).
I made a test as an example (20 rows), and it actually dont take long time, to make the example, with VLOOKUP

 

[shg]

ADVERTISEMENT

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

2​

C2​

D2​

E2​

​

D2​

E2​

0​

0​

0​

0​

0​

0​

0​

0​

G2: =IFERROR(INDEX($C2:$E2, COLUMNS($G$2:Me) - ROWS($G$2:Me) + 2), 0)

3​

C3​

D3​

E3​

​

C3​

D3​

E3​

0​

0​

0​

0​

0​

0​

0​

​

4​

C4​

D4​

E4​

​

0​

C4​

D4​

E4​

0​

0​

0​

0​

0​

0​

​

5​

C5​

D5​

E5​

​

0​

0​

C5​

D5​

E5​

0​

0​

0​

0​

0​

​

6​

C6​

D6​

E6​

​

0​

0​

0​

C6​

D6​

E6​

0​

0​

0​

0​

​

7​

C7​

D7​

E7​

​

0​

0​

0​

0​

C7​

D7​

E7​

0​

0​

0​

​

8​

C8​

D8​

E8​

​

0​

0​

0​

0​

0​

C8​

D8​

E8​

0​

0​

​

9​

C9​

D9​

E9​

​

0​

0​

0​

0​

0​

0​

C9​

D9​

E9​

0​

​

10​

C10​

D10​

E10​

​

0​

0​

0​

0​

0​

0​

0​

C10​

D10​

E10​

​

11​

C11​

D11​

E11​

​

0​

0​

0​

0​

0​

0​

0​

0​

C11​

D11​

​

 

[w1ngz3ro]

Hi shg,

I tried entering the code you have shown in your post and I got something that looks like what I was hoping for but not exactly. Can you elaborate a bit on what "Me" is and explain the code in words, if you don't mind? Thanks in advance! I have attached a picture of what I have so far.

 

[CsJHUN]

Hi WingZero!

For first it may not look nice, but here is a macro! Test it first but not on you original data, make a backup before:

Assuming the matrix elements in C:E and the desired target first cell is G2. And column B still containg the Temperatures (based on the OP/threadstarter)

Sub matrix1()
Range("G2:H2").Value = Range("D2:E2").Value
i = 2
Do
i = i + 1
Range("G" & i & ":I" & i).Offset(0, i - 2).Value = Range("c" & i & ":E" & i).Value
Loop Until i = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
End Sub

Good luck

 

[shg]

... what "Me" is ...

Sorry. In G2:

=IFERROR(INDEX($C2:$E2, COLUMNS($G$2:G2) - ROWS($G$2:G2) + 2), 0)