List Multiple Values Found in Column

[meppwc]

I have a spreadsheet where column A consists of printer models. Column B is a list of associated part numbers.
NOTE: The values in both columns can be repeated. The data starts in row 2.
For each part number starting in B2 continuing to B4000, I am hoping to list the associated printer models found in column A starting at column C and continuing to the right until all values are found.
In the enclosed table you will see that the value in B2 through B5 resulted in one result found in column A. Also notice that in B6 the result was 7 printer models found in column A.
The formula that I seek will provide the results as seen from column C through column I. Using my real data the results could go beyond column I.

IB_CONTR_Asset_Listing_Account_Number_Report_MS_L1 (2).xls
	A	B	C	D	E	F	G	H	I
1	MODEL	PART	RESULT	RESULT	RESULT	RESULT	RESULT	RESULT	RESULT
2	1000Z-HEW	51641A-DS	1000Z-HEW
3	1000Z-HEW	51641A-RM-DS	1000Z-HEW
4	1000Z-HEW	51645A-DS	1000Z-HEW
5	1000Z-HEW	51645A-RM-DS	1000Z-HEW
6	1012-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
7	1012-HEW	Q2612AJ-RM-DS
8	1012-HEW	Q2612A-RM-DS
9	1012-HEW	Q2612A-DS
10	1012-HEW	Q2612AJ-RM-DS
11	1012-HEW	Q2612A-RM-DS
12	1020Z-HEW	Q2612A-DS
13	1020Z-HEW	Q2612AJ-RM-DS
14	1020Z-HEW	Q2612A-RM-DS
15	1020Z-HEW	Q2612A-DS
16	1022-HEW	Q2612A-DS
17	1022-HEW	Q2612AJ-RM-DS
18	1022-HEW	Q2612A-RM-DS
19	1022-HEW	Q2612A-DS
20	1022-HEW	Q2612AJ-RM-DS
21	1022-HEW	Q2612A-RM-DS
22	1022-HEW	Q2612A-DS
23	1022-HEW	Q2612AJ-RM-DS
24	1022-HEW	Q2612A-RM-DS
25	1100Z-HEW	C4092A-DS
Sheet4

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[meppwc]

Thank you for the advice. I updated my account as you suggested. I am using Office 365 and my version of Excel is 2012

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	MODEL	PART	RESULT	RESULT	RESULT	RESULT	RESULT	RESULT	RESULT
2	1000Z-HEW	51641A-DS	1000Z-HEW
3	1000Z-HEW	51641A-RM-DS	1000Z-HEW
4	1000Z-HEW	51645A-DS	1000Z-HEW
5	1000Z-HEW	51645A-RM-DS	1000Z-HEW
6	1012-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
7	1012-HEW	Q2612AJ-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
8	1012-HEW	Q2612A-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
9	1012-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
10	1012-HEW	Q2612AJ-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
11	1012-HEW	Q2612A-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
12	1020Z-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
13	1020Z-HEW	Q2612AJ-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
14	1020Z-HEW	Q2612A-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
15	1020Z-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
16	1022-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
17	1022-HEW	Q2612AJ-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
18	1022-HEW	Q2612A-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
19	1022-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
20	1022-HEW	Q2612AJ-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
21	1022-HEW	Q2612A-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
22	1022-HEW	Q2612A-DS	1012-HEW	1012-HEW	1020Z-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
23	1022-HEW	Q2612AJ-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
24	1022-HEW	Q2612A-RM-DS	1012-HEW	1012-HEW	1020Z-HEW	1022-HEW	1022-HEW	1022-HEW
25	1100Z-HEW	C4092A-DS	1100Z-HEW
26
Data

Cell Formulas
Range	Formula
C2:C5,C25,C23:H24,C22:I22,C20:H21,C19:I19,C17:H18,C15:I16,C13:H14,C12:I12,C10:H11,C9:I9,C7:H8,C6:I6	C2	=TRANSPOSE(FILTER($A$2:$A$100,$B$2:$B$100=B2))

 

[Fluff]

Or, if you only want the distinct models

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	MODEL	PART	RESULT	RESULT	RESULT	RESULT	RESULT	RESULT	RESULT
2	1000Z-HEW	51641A-DS	1000Z-HEW
3	1000Z-HEW	51641A-RM-DS	1000Z-HEW
4	1000Z-HEW	51645A-DS	1000Z-HEW
5	1000Z-HEW	51645A-RM-DS	1000Z-HEW
6	1012-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
7	1012-HEW	Q2612AJ-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
8	1012-HEW	Q2612A-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
9	1012-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
10	1012-HEW	Q2612AJ-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
11	1012-HEW	Q2612A-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
12	1020Z-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
13	1020Z-HEW	Q2612AJ-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
14	1020Z-HEW	Q2612A-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
15	1020Z-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
16	1022-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
17	1022-HEW	Q2612AJ-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
18	1022-HEW	Q2612A-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
19	1022-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
20	1022-HEW	Q2612AJ-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
21	1022-HEW	Q2612A-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
22	1022-HEW	Q2612A-DS	1012-HEW	1020Z-HEW	1022-HEW
23	1022-HEW	Q2612AJ-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
24	1022-HEW	Q2612A-RM-DS	1012-HEW	1020Z-HEW	1022-HEW
25	1100Z-HEW	C4092A-DS	1100Z-HEW
26
Data

Cell Formulas
Range	Formula
C2:C5,C25,C6:E24	C2	=TRANSPOSE(UNIQUE(FILTER($A$2:$A$100,$B$2:$B$100=B2)))

 

[meppwc]

Would I be correct in saying that I would copy =TRANSPOSE(FILTER($A$2:$A$100,$B$2:$B$100=B2)) to C2 and then copy it all the way down to C3295? That is what I tried and I did notice some positive results but also have many "#CALC!" errors as well. Did I do something incorrectly?

 

[meppwc]

Oh I see. I needed to adjust the range. I am good now and thank you so much. This will save me a ton of manual work.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alansidman]

Deleted. Mis-read the requirement.