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