Create List from Range Based on Cell Value

[GuyGadois]

I have the following data that I would like to see how best to create a list from. The data range is currently A1:C46. I would like to enter the Model Name in E1 and a list is generated below it from E3 to G(end of the list) based on the Model name. The example below I entered in Model B in cell E1 (Model_Choice) and the function would look for each instance of Model B and display the Model Name in column F, ID in column G, and the Weight in column H for each instance. Is this possible?

Cheers,

GG

Book3
	A	B	C	D	E	F	G	H
1	Model Name	ID	Weight		Model B	Model Name	ID	Weight
2	Model A	K	13.60%			Model B	O	8.60%
3	Model A	O	11.33%			Model B	K	8.50%
4	Model A	A	10.00%			Model B	L	8.50%
5	Model C	K	10.00%			Model B	B	8.00%
6	Model C	L	10.00%			Model B	D	5.73%
7	Model A	D	9.07%			Model B	G	5.73%
8	Model A	H	9.07%			Model B	I	5.73%
9	Model A	M	9.07%			Model B	H	5.33%
10	Model A	C	9.06%			Model B	J	5.00%
11	Model C	B	9.00%			Model B	Q	4.00%
12	Model B	O	8.60%			Model B	N	2.50%
13	Model B	K	8.50%			Model B	A	1.34%
14	Model B	L	8.50%			Model B	M	1.34%
15	Model B	B	8.00%			Model B	C	1.33%
16	Model A	N	6.80%			Model B	P	1.33%
17	Model A	J	6.00%
18	Model A	L	6.00%
19	Model C	H	6.00%
20	Model C	J	6.00%
21	Model C	O	6.00%
22	Model B	D	5.73%
23	Model B	G	5.73%
24	Model B	I	5.73%
25	Model B	H	5.33%
26	Model A	B	5.00%
27	Model B	J	5.00%
28	Model C	Q	5.00%
29	Model B	Q	4.00%
30	Model C	D	4.00%
31	Model C	G	4.00%
32	Model C	I	4.00%
33	Model C	N	3.00%
34	Model B	N	2.50%
35	Model C	A	2.00%
36	Model C	C	2.00%
37	Model C	M	2.00%
38	Model C	P	2.00%
39	Model A	I	1.50%
40	Model A	P	1.50%
41	Model B	A	1.34%
42	Model B	M	1.34%
43	Model B	C	1.33%
44	Model B	P	1.33%
45	Model A	G	1.00%
46	Model A	Q	1.00%
Sheet1

 

[Peter_SSs]

Try this in F2 and copy across and down - after checking/amending the table name in the formula.

=IFERROR(INDEX(Table1[Model Name],AGGREGATE(15,6,(ROW(Table1[Model Name])-ROW(Table1[#Headers]))/(Table1[[Model Name]:[Model Name]]=$E$1),ROWS(F$2:F2))),"")

GuyGadois.xlsm
	A	B	C	D	E	F	G	H
1	Model Name	ID	Weight		Model B	Model Name	ID	Weight
2	Model A	K	0.136			Model B	O	0.086
3	Model A	O	0.1133			Model B	K	0.085
4	Model A	A	0.1			Model B	L	0.085
5	Model C	K	0.1			Model B	B	0.08
6	Model C	L	0.1			Model B	D	0.0573
7	Model A	D	0.0907			Model B	G	0.0573
8	Model A	H	0.0907			Model B	I	0.0573
9	Model A	M	0.0907			Model B	H	0.0533
10	Model A	C	0.0906			Model B	J	0.05
11	Model C	B	0.09			Model B	Q	0.04
12	Model B	O	0.086			Model B	N	0.025
13	Model B	K	0.085			Model B	A	0.0134
14	Model B	L	0.085			Model B	M	0.0134
15	Model B	B	0.08			Model B	C	0.0133
16	Model A	N	0.068			Model B	P	0.0133
17	Model A	J	0.06
18	Model A	L	0.06
19	Model C	H	0.06
20	Model C	J	0.06
21	Model C	O	0.06
22	Model B	D	0.0573
23	Model B	G	0.0573
24	Model B	I	0.0573
25	Model B	H	0.0533
26	Model A	B	0.05
27	Model B	J	0.05
28	Model C	Q	0.05
29	Model B	Q	0.04
30	Model C	D	0.04
31	Model C	G	0.04
32	Model C	I	0.04
33	Model C	N	0.03
34	Model B	N	0.025
35	Model C	A	0.02
36	Model C	C	0.02
37	Model C	M	0.02
38	Model C	P	0.02
39	Model A	I	0.015
40	Model A	P	0.015
41	Model B	A	0.0134
42	Model B	M	0.0134
43	Model B	C	0.0133
44	Model B	P	0.0133
45	Model A	G	0.01
46	Model A	Q	0.01
Sheet1

 

[GuyGadois]

Thank you! That works perfectly and I have no idea why. I have never used this formula before. i will need to read more about it.

Cheers,

GG

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the follow-up.