Replace diagonal value in a sequence

I'd like to replace the 1's in these matrixes in Sheet3 with values from the "age" row in Sheet2. Each matrix would be assigned one number.
So the 1's in the top matrix would be assigned 59, and the 1's in the second matrix would be assigned 51.
I would like to repeat this process with all values from the "age" row.

From what I'm gathering, this can only be done with VBA. I'd appreciate your help!

Thanks so much!

age.png
matrix.png

I want to create this:

matrix result.png

Hi ohnewbie and welcome to the forum,

Have you tried to select the range and perform a Search and Replace? It will only replace in the selected range. If that doesn't work then we need to see a sample workbook

Thanks MarvinP! That is one option but I have over 600 observations I would have to search and replace. I'd like to attach a link to my workbook but this forum is not permitting me to share a link

The matrix is 22 X 22. There are over 600 matrixes. One matrix per observation.

Since I cannot attach a sample workbook, I'm pasting ages from 20 respondents.

age:

59
51
58
41
43
46
38
28
44
42
47
35
37
30
27
52
57
42
40
56

Hey oh,

I need a better example to see how the data is arranged and why 59 or 51? Also is "1" the only numbers that appear in all matrices that need replacing and "1" doesn't appear anywhere else? We need a sample workbook to understand the problem better.

Thanks Marvin! I edited my previous post with an attachment to a sample workbook. I replaced the values in the first three matrices to show what I'm trying to achieve. Each color block represents one observation.

I'm trying to generate a matrix per observation in which the age value runs diagonally through the matrix.
The data has to be formatted this way for my analysis in R.

OK. Cut and paste won't work for sure!!

In A2, copied across and then down... and down..

=IF(1+MOD((ROWS(A$2:A2)-1),22)=COLUMNS($A2:A2),INDEX(Sheet2!$B$1:$YO$1,,1+INT((ROWS(A$2:A2)-1)/22)),0)

Fortunately, I have O365 and was able to copy it all the way to the bottom much more asily than you could... so I did it for you. The file has had to be zipped to get it onto the Forum.

May I ask ... why replace 1's when all you can do is just overwrite data...

Please Login or Register  to view this content.

Nothing there that requires O365. It was a quick way of copying the values down. That's all

Originally Posted by ohnewbie

Thank you so much Sintek! I didn't realize that was possible.

I think I can follow most of the code, but there are couple parts I don't understand.
"Sheet1.Range("A2").Resize(x, 22).Value = Temp" Where does Sheet1 come from? And what does Temp represent?

Your Sheet3 is in actual fact Sheet1 in index...
Temp refers to the Array that stored the data

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Hi.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.

Thread CLOSED.