Array Comparison Return Single Result

[wein3967]

While I'm quite familiar with Excel, I don't have much experience with array-style formulas. I've attempted to include a portion of the data set I"m working with. In column J, I was able to simply get a unique list of column D shown in column J. Now for each unique item in column J, I'd like to list all the unique items in column F. For example, the first item is 005-051. I'd scan down column D, and anytime the value matches 005-051 I'd grab the respective row value in column F. So ideally cell K5 would return (for 005-051): 005 repeated 8 times, then deduped down to just 005. For cell K8 (010-099), I'd expect 010 repeated 5 times and then 010,022. This would then be deduped down to 010,022. Hopefully, that makes some sense. I could easily do this via macros, but I hoping to avoid them in this case. Thanks for any input.

List R6.xlsx
	D	E	F	G	H	I	J	K
4	Item	Set	Units
5	005-051	5	005				005-051
6	005-051	5	005				005-053
7	005-051	5	005				009-014
8	005-051	5	005				010-099
9	005-051	5	005				012-020
10	005-051	5	005				015-023
11	005-051	5	005				015-130
12	005-051	5	005				026-033
13	005-053	5	005				026-034
14	005-053	5	005				027-036
15	005-053	5	005				045-073
16	009-014	1	008/009				126-159
17	010-099	2	010				205-046
18	010-099	2	010				212-086
19	010-099	2	010				214-089
20	010-099	2	010				215-059
21	010-099	2	010				220-062
22	010-099	2	010,022				234-068
23	012-020	2	012
24	012-020	2	012
25	012-020	2	012
26	012-020	2	012
27	012-020	2	012
28	012-020	2	012
29	012-020	2	012
30	015-023	2	015
31	015-023	2	015
32	015-023	2	015
33	015-023	2	015
34	015-023	2	015
35	015-023	2	015
36	015-023	2	015
37	015-023	2	015
38	015-023	2	015
39	015-130	2	015
40	015-130	2	015
Detailed Sets

Cell Formulas
Range	Formula
D5:D40	D5	=LEFT([@[Scenario/Threat]],FIND("\",[@[Scenario/Threat]])-1)
E5:E40	E5	=XLOOKUP([@[Scenario/Threat]],Scenarios!$A$2:$A$121,Scenarios!$D$2:$D$121,"Error",0)
F5:F40	F5	=XLOOKUP([@[Scenario/Threat]],Scenarios!$A$2:$A$121,Scenarios!$B$2:$B$121,"Error",0)
J5:J22	J5	=UNIQUE(Table1_2[Item],FALSE)
Dynamic array formulas.

 

[lrobbo314]

Using Power Query.

20210511 Stat Pull Lozano Brazilian.xlsx
	A	B	C	D	E	F
1	Item	Set	Units		Item	Count
2	005-051	5	005		005-051	005
3	005-051	5	005		005-053	005
4	005-051	5	005		009-014	008/009
5	005-051	5	005		010-099	010, 10022
6	005-051	5	005		012-020	012
7	005-051	5	005		015-023	015
8	005-051	5	005		015-130	015, 020
9	005-051	5	005
10	005-053	5	005
11	005-053	5	005
12	005-053	5	005
13	009-014	1	008/009
14	010-099	2	010
15	010-099	2	010
16	010-099	2	010
17	010-099	2	010
18	010-099	2	010
19	010-099	2	10022
20	012-020	2	012
21	012-020	2	012
22	012-020	2	012
23	012-020	2	012
24	012-020	2	012
25	012-020	2	012
26	012-020	2	012
27	015-023	2	015
28	015-023	2	015
29	015-023	2	015
30	015-023	2	015
31	015-023	2	015
32	015-023	2	015
33	015-023	2	015
34	015-023	2	015
35	015-023	2	015
36	015-130	2	015
37	015-130	2	020
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Item"}, {{"Count", each Text.Combine(List.Distinct(_[Units]),", "), type text}})
in
    Group

 

[lrobbo314]

Or with formulas.

20210511 Stat Pull Lozano Brazilian.xlsx
	A	B	C	D	E	F
1	Item	Set	Units		Item	Combo
2	005-051	5	005		005-051	005
3	005-051	5	005		005-053	005
4	005-051	5	005		009-014	008/009
5	005-051	5	005		010-099	010, 10022
6	005-051	5	005		012-020	012
7	005-051	5	005		015-023	015
8	005-051	5	005		015-130	015, 020
9	005-051	5	005
10	005-053	5	005
11	005-053	5	005
12	005-053	5	005
13	009-014	1	008/009
14	010-099	2	010
15	010-099	2	010
16	010-099	2	010
17	010-099	2	010
18	010-099	2	010
19	010-099	2	10022
20	012-020	2	012
21	012-020	2	012
22	012-020	2	012
23	012-020	2	012
24	012-020	2	012
25	012-020	2	012
26	012-020	2	012
27	015-023	2	015
28	015-023	2	015
29	015-023	2	015
30	015-023	2	015
31	015-023	2	015
32	015-023	2	015
33	015-023	2	015
34	015-023	2	015
35	015-023	2	015
36	015-130	2	015
37	015-130	2	020
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=UNIQUE(Table1[Item])
F2:F8	F2	=TEXTJOIN(", ",1,UNIQUE(FILTER(Table1[Units],Table1[Item]=E2)))
Dynamic array formulas.

 

[wein3967]

Awesome! Thanks a bunch to @Irobbo314. The Power Query method is neat. I've just started using the "Power" tools ... mainly to unpivot data. I'm certainly more familiar with the formula method. I completely forgot about Filter. Thanks again.