Create List from Range Based on Cell Value

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGH
1Model NameID WeightModel BModel NameID Weight
2Model AK13.60%Model BO8.60%
3Model AO11.33%Model BK8.50%
4Model AA10.00%Model BL8.50%
5Model CK10.00%Model BB8.00%
6Model CL10.00%Model BD5.73%
7Model AD9.07%Model BG5.73%
8Model AH9.07%Model BI5.73%
9Model AM9.07%Model BH5.33%
10Model AC9.06%Model BJ5.00%
11Model CB9.00%Model BQ4.00%
12Model BO8.60%Model BN2.50%
13Model BK8.50%Model BA1.34%
14Model BL8.50%Model BM1.34%
15Model BB8.00%Model BC1.33%
16Model AN6.80%Model BP1.33%
17Model AJ6.00%
18Model AL6.00%
19Model CH6.00%
20Model CJ6.00%
21Model CO6.00%
22Model BD5.73%
23Model BG5.73%
24Model BI5.73%
25Model BH5.33%
26Model AB5.00%
27Model BJ5.00%
28Model CQ5.00%
29Model BQ4.00%
30Model CD4.00%
31Model CG4.00%
32Model CI4.00%
33Model CN3.00%
34Model BN2.50%
35Model CA2.00%
36Model CC2.00%
37Model CM2.00%
38Model CP2.00%
39Model AI1.50%
40Model AP1.50%
41Model BA1.34%
42Model BM1.34%
43Model BC1.33%
44Model BP1.33%
45Model AG1.00%
46Model AQ1.00%
Sheet1
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows
Try this in F2 and copy across and down - after checking/amending the table name in the formula.

Excel 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
ABCDEFGH
1Model NameID WeightModel BModel NameID Weight
2Model AK0.136Model BO0.086
3Model AO0.1133Model BK0.085
4Model AA0.1Model BL0.085
5Model CK0.1Model BB0.08
6Model CL0.1Model BD0.0573
7Model AD0.0907Model BG0.0573
8Model AH0.0907Model BI0.0573
9Model AM0.0907Model BH0.0533
10Model AC0.0906Model BJ0.05
11Model CB0.09Model BQ0.04
12Model BO0.086Model BN0.025
13Model BK0.085Model BA0.0134
14Model BL0.085Model BM0.0134
15Model BB0.08Model BC0.0133
16Model AN0.068Model BP0.0133
17Model AJ0.06   
18Model AL0.06   
19Model CH0.06   
20Model CJ0.06   
21Model CO0.06   
22Model BD0.0573   
23Model BG0.0573   
24Model BI0.0573
25Model BH0.0533
26Model AB0.05
27Model BJ0.05
28Model CQ0.05
29Model BQ0.04
30Model CD0.04
31Model CG0.04
32Model CI0.04
33Model CN0.03
34Model BN0.025
35Model CA0.02
36Model CC0.02
37Model CM0.02
38Model CP0.02
39Model AI0.015
40Model AP0.015
41Model BA0.0134
42Model BM0.0134
43Model BC0.0133
44Model BP0.0133
45Model AG0.01
46Model AQ0.01
Sheet1
 
Solution

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad it worked for you. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,181,680
Messages
5,931,378
Members
436,788
Latest member
er19

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top