Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to create a 4x4 matrix in Excel 2007 by taking three or more columns or conditions, for example:

Column index  Row index     Name
 1             2             x
 2             3             y
 3             4             z
 4             1             p

This is how data looks, and I want it for 1x1 cell as p and 1x2 cell as x and so on, and I want output as follows:

matrix  1   2   3   4
1       p   x   y   z
2       p   x   y   z
3       p   x   y   z
4       p   x   y   z

And I have very huge data like this. Sometimes the matrix size goes up to 60x60.

share|improve this question
    
Not sure I understand what you are asking. It seems the numbers in the "Column index" column are irrelevant. You basically create a row with x,y,z,p ordered by their "Row Index", then copy that row 4 times, is that it? –  assylias May 31 '12 at 14:34
    
I have to agree with @assylias. Your depicted output doesn't match what you describe as "...for 1*1 cell as p and 1*2 cell as x...". But please see my answer below. –  F106dart May 31 '12 at 17:25
    
Hello assylias because it is a small data thats y it is appearing like that we represent a matrix in terms of 1*1, 1*2, 1*3, 1*4, and 2*1, 2*2 w.r.to column index. and I want to fill the same matrix w.r.to row index also for that reason only I have mentioned the out put matrix in that fashion. when we see 1*2 and 2*1 both the values are same I want to write all in a single matrix. some times the matrix size reaches to 60*60 also please give me a better solution for this –  sandeep Jun 1 '12 at 7:32

2 Answers 2

You need to use a PivotTable. This is available from Insert > PrivotTable. Then you can put the RowInd in the Row Labels and ColInd in the Column Labels, and your Name into the Values box. However, it appears that PivotTables can only deal with numeric values (!!!), so you'd have to convert your x,y,z,p into something numeric first.

share|improve this answer
    
pivot tables can deal with text –  Raystafarian Jul 29 '13 at 17:04

I think you are wanting a multi-condition lookup. Here is a great example

Following the techniques in that link, you can do something like this:

enter image description here

Set up 3 named ranges covering your data. Note that the formula in J9 is an array formula, entered with CONTROL+SHIFT+ENTER. The nice thing about that formula is once you enter it as an array formula for just one cell, you can then drag it over and down as far as you need.

Also: the IFERROR() part puts in blanks (instead of #N/A or #VALUE errors) if an error is encountered in the INDEX(MATCH() part.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.