Create a Matrix from a List

[Alv_elm]

Hi everyone,
It is my first post and first of all, thanks for all the support you have provided me in the past.
I need your help in a complicated (at least for me) issue.
I would like to populate the content of a matrix based on a list.

I have got a list of 10 products to be develop A,B,C,D,E,F,G,H,I,J
In some cases this products has inter dependencies during their development, there are 4 kinds of inter dependencies INPUT, OUTPUT, BLOCKING or SYNERGIES (in some cases there is no dependencies at all).
I have created a list with all the inter dependencies based on 3 parameters (Product , Product affected by the inter dependency, Type of inter dependency)
Now I would like to put it in a more visual way, creating a 10x10 matrix where :
- Rows= Product
- Columns= Product affected by the inter dependency
- Cell (Product, Product affected by the inter dependency)= Type of interdependency.

I have tried to create a function for each of the cells based on the parameters of the list, but I always fail as I am not very good programming.
Could some one be so kind to help me please?
Thanks in advance.
Have a good weekend!!!

 

[DRSteele]

Welcome to the forum.

It is unlikely anyone can help unless you provide some sample data together with expected outcome. You can use this forum's XL2BB add-in to do so.

 

[Dermot]

If I understand you correctly, you have a list of dependencies with 3 columns - product1, product2 and the dependency

Then you have a matrix of all the products, and you want to place the items from your list into this matrix

If this is correct, this is what you could do. The main problem is you need to look up items based on two products at once. The trick is to turn it into a single lookup, and that is quite easy.

First add an extra column to your list, which = product1 & "-" & product2 (ie a combination of the two names separated by a dash)
So if a row has P001, P003, Dep1, the extra column would have P001-P003

In each cell of your matrix, use MATCH to look up the combination of the row name and the column name (again separated by a comma) of that cell, in the lookup column, and then use INDEX to get the dependency. If MATCH fails (ie there is no dependency), use IFERROR to make the result blank.

Hopefully you can use this example below to see how it works

qaz.xlsb
	G	H	I	J	K	L	M	N	O	P	Q
2	Product1	Product2	Dependency	Lookup Key
3	a	f	11	a-f		Matrix	f	g	h	j	k
4	a	h	13	a-h		a	11		13
5	b	g	22	b-g		b		22
6	c	j	34	c-j		c		32		34	35
7	c	k	35	c-k		d			43
8	c	g	32	c-g		e	51				55
9	d	h	43	d-h
10	e	f	51	e-f
11	e	k	55	e-k
Sheet1

Cell Formulas
Range	Formula
M4:Q8	M4	=IFERROR(INDEX($I$3:$I$11,MATCH($L4 & "-" & M$3,$J$3:$J$11,0)),"")
J3:J11	J3	=G3 & "-" & H3

 

[Alv_elm]

Hi Dermot,
This is exactly what I was looking for.
Thanks for your help.
Also thanks to DrSteele!!

Have a good day

If I understand you correctly, you have a list of dependencies with 3 columns - product1, product2 and the dependency

Then you have a matrix of all the products, and you want to place the items from your list into this matrix

If this is correct, this is what you could do. The main problem is you need to look up items based on two products at once. The trick is to turn it into a single lookup, and that is quite easy.

First add an extra column to your list, which = product1 & "-" & product2 (ie a combination of the two names separated by a dash)
So if a row has P001, P003, Dep1, the extra column would have P001-P003

In each cell of your matrix, use MATCH to look up the combination of the row name and the column name (again separated by a comma) of that cell, in the lookup column, and then use INDEX to get the dependency. If MATCH fails (ie there is no dependency), use IFERROR to make the result blank.

Hopefully you can use this example below to see how it works

qaz.xlsb
	G	H	I	J	K	L	M	N	O	P	Q
2	Product1	Product2	Dependency	Lookup Key
3	a	f	11	a-f		Matrix	f	g	h	j	k
4	a	h	13	a-h		a	11		13
5	b	g	22	b-g		b		22
6	c	j	34	c-j		c		32		34	35
7	c	k	35	c-k		d			43
8	c	g	32	c-g		e	51				55
9	d	h	43	d-h
10	e	f	51	e-f
11	e	k	55	e-k
Sheet1

Cell Formulas
Range	Formula
M4:Q8	M4	=IFERROR(INDEX($I$3:$I$11,MATCH($L4 & "-" & M$3,$J$3:$J$11,0)),"")
J3:J11	J3	=G3 & "-" & H3

 

[thigarette]

If I understand you correctly, you have a list of dependencies with 3 columns - product1, product2 and the dependency

Then you have a matrix of all the products, and you want to place the items from your list into this matrix

If this is correct, this is what you could do. The main problem is you need to look up items based on two products at once. The trick is to turn it into a single lookup, and that is quite easy.

First add an extra column to your list, which = product1 & "-" & product2 (ie a combination of the two names separated by a dash)
So if a row has P001, P003, Dep1, the extra column would have P001-P003

In each cell of your matrix, use MATCH to look up the combination of the row name and the column name (again separated by a comma) of that cell, in the lookup column, and then use INDEX to get the dependency. If MATCH fails (ie there is no dependency), use IFERROR to make the result blank.

Hopefully you can use this example below to see how it works

qaz.xlsb
	G	H	I	J	K	L	M	N	O	P	Q
2	Product1	Product2	Dependency	Lookup Key
3	a	f	11	a-f		Matrix	f	g	h	j	k
4	a	h	13	a-h		a	11		13
5	b	g	22	b-g		b		22
6	c	j	34	c-j		c		32		34	35
7	c	k	35	c-k		d			43
8	c	g	32	c-g		e	51				55
9	d	h	43	d-h
10	e	f	51	e-f
11	e	k	55	e-k
Sheet1

Cell Formulas
Range	Formula
M4:Q8	M4	=IFERROR(INDEX($I$3:$I$11,MATCH($L4 & "-" & M$3,$J$3:$J$11,0)),"")
J3:J11	J3	=G3 & "-" & H3

This has worked perfectly me! Exactly what I needed. Thank you much