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

#### w1ngz3ro

##### New Member
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Gerald Higgins

##### Well-known Member
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

##### New Member
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

##### Well-known Member
OK, this is probably do-able.

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

##### New Member
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

##### Active Member
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

##### MrExcel MVP
 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

##### New Member
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

##### Active Member
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)
Code:
``````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

Last edited:

#### shg

##### MrExcel MVP
... what "Me" is ...

Sorry. In G2:

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

Last edited:

Replies
5
Views
135
Replies
2
Views
402
Replies
7
Views
198
Replies
0
Views
124
Replies
2
Views
97

1,195,683
Messages
6,011,138
Members
441,587
Latest member
kbsgiri09

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back