Array Comparison Return Single Result

wein3967

New Member
Joined
Apr 1, 2007
Messages
6
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
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

wein3967

New Member
Joined
Apr 1, 2007
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

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