# Array Comparison Return Single Result

#### wein3967

##### New Member
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.

Cell Formulas
RangeFormula
D5:D40D5=LEFT([@[Scenario/Threat]],FIND("\",[@[Scenario/Threat]])-1)
E5:E40E5=XLOOKUP([@[Scenario/Threat]],Scenarios!\$A\$2:\$A\$121,Scenarios!\$D\$2:\$D\$121,"Error",0)
F5:F40F5=XLOOKUP([@[Scenario/Threat]],Scenarios!\$A\$2:\$A\$121,Scenarios!\$B\$2:\$B\$121,"Error",0)
J5:J22J5=UNIQUE(Table1_2[Item],FALSE)
Dynamic array formulas.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### lrobbo314

##### Well-known Member
Using Power Query.

20210511 Stat Pull Lozano Brazilian.xlsx
ABCDEF
1ItemSetUnitsItemCount
2005-0515005005-051005
3005-0515005005-053005
4005-0515005009-014008/009
5005-0515005010-099010, 10022
6005-0515005012-020012
7005-0515005015-023015
8005-0515005015-130015, 020
9005-0515005
10005-0535005
11005-0535005
12005-0535005
13009-0141008/009
14010-0992010
15010-0992010
16010-0992010
17010-0992010
18010-0992010
19010-099210022
20012-0202012
21012-0202012
22012-0202012
23012-0202012
24012-0202012
25012-0202012
26012-0202012
27015-0232015
28015-0232015
29015-0232015
30015-0232015
31015-0232015
32015-0232015
33015-0232015
34015-0232015
35015-0232015
36015-1302015
37015-1302020
Sheet2

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

#### lrobbo314

##### Well-known Member
Or with formulas.

20210511 Stat Pull Lozano Brazilian.xlsx
ABCDEF
1ItemSetUnitsItemCombo
2005-0515005005-051005
3005-0515005005-053005
4005-0515005009-014008/009
5005-0515005010-099010, 10022
6005-0515005012-020012
7005-0515005015-023015
8005-0515005015-130015, 020
9005-0515005
10005-0535005
11005-0535005
12005-0535005
13009-0141008/009
14010-0992010
15010-0992010
16010-0992010
17010-0992010
18010-0992010
19010-099210022
20012-0202012
21012-0202012
22012-0202012
23012-0202012
24012-0202012
25012-0202012
26012-0202012
27015-0232015
28015-0232015
29015-0232015
30015-0232015
31015-0232015
32015-0232015
33015-0232015
34015-0232015
35015-0232015
36015-1302015
37015-1302020
Sheet2
Cell Formulas
RangeFormula
E2:E8E2=UNIQUE(Table1[Item])
F2:F8F2=TEXTJOIN(", ",1,UNIQUE(FILTER(Table1[Units],Table1[Item]=E2)))
Dynamic array formulas.

#### wein3967

##### New Member
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.

Replies
0
Views
69
Replies
7
Views
236
Replies
11
Views
157
Replies
4
Views
128
Replies
4
Views
240

1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

### 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.

### Which adblocker are you using?

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

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