Getting a unique list from the results of the UNIQUE Function ... is this possible?

brockk

Board Regular
Joined
Jul 1, 2006
Messages
170
Office Version
  1. 2013
Platform
  1. Web
Greetings and Happy New Year to All,

I am trying to get used to the advantages of using the UNIQUE feature and I'm wondering if what I want to accomplish is even possible. Attached is a sample of an array in which I obtained 2 individual lists of data from my array (PlanCode & ProductType). I would like to obtain a unique listing from the 2 unique lists earlier mentioned as shown in desired output (Activity, Feature, AAL, & Upgrade) *** with the exception of having blank cells in the column***. I would prefer, if possible, a non VBA solution for this so it may be dynamic as more data is eventually added. Is this even possible?

Book2.xlsx
BCDEFGHIJKLMN
1lookup ArrayUnique List 1Unique List 2Desired Result from the (2) Unique Lists *** WITHOUT EMPTY CELLS ***
2
3ProductTypeActivityTypePlanCodeMonthlyAccessPlanCodeProductTypeACTIVITYFEATUREAALUPGRADE
4ACTIVITYREACTHIHMTI$55.00HIHMTIACTIVITYHIHMTI
5FEATUREACTJMP2IND$0.00JMP2INDFEATUREJMP2IND
6FEATUREACTMCAFIND$0.00MCAFINDFEATUREMCAFIND
7FEATUREACTMPUDAT40$10.00MPUDAT40FEATUREMPUDAT40
8FEATUREACTP3602$9.00P3602FEATUREP3602
9FEATUREACTACIND$0.00ACINDFEATUREACIND
10FEATUREDEACTMCAFIND$0.00MGPLSADDAALACINDMIMGPLSADD
11FEATUREDEACTMCAFIND$0.00DWPDIRTIACTIVITYDWPDIRTIP3601
12FEATUREDEACTP3602$9.00HILT100ACTIVITYHILT100P3605
13FEATUREDEACTP3602$9.00MPLSFAMACTIVITYMPLSFAMP360Y5
14AALACTMGPLSADD$35.00ACINDMIFEATUREACINDMI
15AALACTMGPLSADD$35.00P3601FEATUREP3601
16ACTIVITYACTDWPDIRTI$15.00P3605FEATUREP3605
17ACTIVITYACTHILT100$55.00MAGUNL55UPGRADEP360Y5MAGUNL55
18ACTIVITYACTMPLSFAM$75.00P360Y5FEATUREP360Y5
19ACTIVITYACTMPLSFAM$75.00MAGENTAD2AALMAGENTAD2
20FEATUREACTACIND$0.00HD4SNFLXFEATUREHD4SNFLX
21FEATUREACTACIND$0.00MAGDATAFEATUREMAGDATA
22FEATUREACTACINDMI$0.00MCAFINDMIFEATUREMCAFINDMI
23FEATUREACTMPUDAT40$10.00P360MI3FEATUREP360MI3
24FEATUREACTMPUDAT40$10.00MAGTBACTIVITYMAGTB
25FEATUREACTMPUDAT40$10.00UNLMTTACTIVITYUNLMTT
26FEATUREACTMPUDAT40$10.00MGDATAFEATUREHIHMTEMGDATA
27FEATUREACTP3602$9.00P3603FEATUREP3603
28FEATUREDEACTACIND$0.00P360MI4FEATUREP360MI4
29FEATUREDEACTMCAFIND$0.00ZBUSAALCIAALZBUSAALCI
30FEATUREDEACTP3601$7.00HIHMTEACTIVITYHIHMTE
31FEATUREDEACTP3605$18.00ZBSUNC2TIACTIVITYZBSUNC2TI
32UPGRADEACTMAGUNL55$40.00ZDIGITSAWACTIVITYZDIGITSAW
33FEATUREACTP3602$9.00ZBSDTC100FEATUREZBSDTC100
34FEATUREDEACTACIND$0.00
35FEATUREDEACTACIND$0.00
36FEATUREDEACTMCAFIND$0.00
37FEATUREDEACTP360Y5$18.00
38AALACTMAGENTAD2$25.00
39FEATUREACTHD4SNFLX$19.99
40FEATUREACTMAGDATA$0.00
41UPGRADEACTMAGENTAD2$25.00
42FEATUREDEACTACINDMI$0.00
43FEATUREDEACTMCAFINDMI$0.00
44FEATUREDEACTP360MI3$13.00
45AALACTMAGENTAD2$25.00
46AALACTMGPLSADD$35.00
47ACTIVITYACTHIHMTI$55.00
48ACTIVITYACTMPLSFAM$75.00
49ACTIVITYACTMPLSFAM$75.00
50ACTIVITYDEACTMAGTB$25.00
51ACTIVITYDEACTUNLMTT$20.00
52FEATUREACTACIND$0.00
53FEATUREACTACINDMI$0.00
54FEATUREACTMCAFIND$0.00
55FEATUREACTMCAFIND$0.00
56FEATUREACTMCAFIND$0.00
57FEATUREACTMCAFIND$0.00
58FEATUREACTMCAFIND$0.00
59FEATUREACTMCAFINDMI$0.00
60FEATUREACTMGDATA$0.00
61FEATUREACTMPUDAT40$10.00
62FEATUREACTMPUDAT40$10.00
63FEATUREACTMPUDAT40$10.00
64FEATUREACTP3602$9.00
65FEATUREACTP3602$9.00
66FEATUREACTP3602$9.00
67FEATUREACTP3603$13.00
68FEATUREACTP3605$18.00
69FEATUREACTP360MI3$13.00
70FEATUREDEACTACINDMI$0.00
71FEATUREDEACTP360MI4$16.00
72FEATUREDEACTP360Y5$18.00
73UPGRADEACTMAGENTAD2$25.00
74UPGRADEACTMPLSFAM$75.00
75FEATUREDEACTACIND$0.00
76ACTIVITYACTHIHMTI$55.00
77FEATUREACTMCAFIND$0.00
78FEATUREACTMCAFIND$0.00
79FEATUREACTP3602$9.00
80FEATUREACTP3605$18.00
81FEATUREDEACTMCAFIND$0.00
82FEATUREDEACTMCAFIND$0.00
83FEATUREDEACTMCAFIND$0.00
84FEATUREDEACTP360Y5$18.00
85FEATUREDEACTP360Y5$18.00
86FEATUREDEACTP360Y5$18.00
87UPGRADEACTMPLSFAM$75.00
88FEATUREACTACIND$0.00
89AALACTZBUSAALCI$35.00
90ACTIVITYACTHIHMTE$55.00
91ACTIVITYACTZBSUNC2TI$75.00
92ACTIVITYACTZBSUNC2TI$75.00
93ACTIVITYACTZDIGITSAW$15.00
94FEATUREACTZBSDTC100$10.00
95FEATUREACTZBSDTC100$10.00
96FEATUREACTZBSDTC100$10.00
97FEATUREACTMCAFIND$0.00
98FEATUREACTMCAFIND$0.00
99FEATUREACTMCAFIND$0.00
Sheet1
Cell Formulas
RangeFormula
K3:N3K3=TRANSPOSE(UNIQUE(I4#))
H4:H33H4=UNIQUE(D4:D99)
I4:I33I4=XLOOKUP(H4#,$D$4:$D$99,$B$4:$B$99)
Dynamic array formulas.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Something like this?
test.xlsm
BCDEFGHIJKLMN
1lookup ArrayUnique List 1Unique List 2Desired Result from the (2) Unique Lists *** WITHOUT EMPTY CELLS ***
2
3ProductTypeActivityTypePlanCodeMonthlyAccessPlanCodeProductTypeACTIVITYFEATUREAALUPGRADE
4ACTIVITYREACTHIHMTI55HIHMTIACTIVITYHIHMTIJMP2INDMGPLSADDMAGUNL55
5FEATUREACTJMP2IND0JMP2INDFEATUREDWPDIRTIMCAFINDMAGENTAD2
6FEATUREACTMCAFIND0MCAFINDFEATUREHILT100MPUDAT40ZBUSAALCI
7FEATUREACTMPUDAT4010MPUDAT40FEATUREMPLSFAMP3602
8FEATUREACTP36029P3602FEATUREMAGTBACIND
9FEATUREACTACIND0ACINDFEATUREUNLMTTACINDMI
10FEATUREDEACTMCAFIND0MGPLSADDAALHIHMTEP3601
11FEATUREDEACTMCAFIND0DWPDIRTIACTIVITYZBSUNC2TIP3605
12FEATUREDEACTP36029HILT100ACTIVITYZDIGITSAWP360Y5
13FEATUREDEACTP36029MPLSFAMACTIVITYHD4SNFLX
14AALACTMGPLSADD35ACINDMIFEATUREMAGDATA
15AALACTMGPLSADD35P3601FEATUREMCAFINDMI
16ACTIVITYACTDWPDIRTI15P3605FEATUREP360MI3
17ACTIVITYACTHILT10055MAGUNL55UPGRADEMGDATA
18ACTIVITYACTMPLSFAM75P360Y5FEATUREP3603
19ACTIVITYACTMPLSFAM75MAGENTAD2AALP360MI4
20FEATUREACTACIND0HD4SNFLXFEATUREZBSDTC100
21FEATUREACTACIND0MAGDATAFEATURE
22FEATUREACTACINDMI0MCAFINDMIFEATURE
23FEATUREACTMPUDAT4010P360MI3FEATURE
24FEATUREACTMPUDAT4010MAGTBACTIVITY
25FEATUREACTMPUDAT4010UNLMTTACTIVITY
26FEATUREACTMPUDAT4010MGDATAFEATURE
27FEATUREACTP36029P3603FEATURE
28FEATUREDEACTACIND0P360MI4FEATURE
29FEATUREDEACTMCAFIND0ZBUSAALCIAAL
30FEATUREDEACTP36017HIHMTEACTIVITY
31FEATUREDEACTP360518ZBSUNC2TIACTIVITY
32UPGRADEACTMAGUNL5540ZDIGITSAWACTIVITY
33FEATUREACTP36029ZBSDTC100FEATURE
34FEATUREDEACTACIND0
35FEATUREDEACTACIND0
36FEATUREDEACTMCAFIND0
37FEATUREDEACTP360Y518
38AALACTMAGENTAD225
39FEATUREACTHD4SNFLX19.99
40FEATUREACTMAGDATA0
41UPGRADEACTMAGENTAD225
42FEATUREDEACTACINDMI0
43FEATUREDEACTMCAFINDMI0
44FEATUREDEACTP360MI313
45AALACTMAGENTAD225
46AALACTMGPLSADD35
47ACTIVITYACTHIHMTI55
48ACTIVITYACTMPLSFAM75
49ACTIVITYACTMPLSFAM75
50ACTIVITYDEACTMAGTB25
51ACTIVITYDEACTUNLMTT20
52FEATUREACTACIND0
53FEATUREACTACINDMI0
54FEATUREACTMCAFIND0
55FEATUREACTMCAFIND0
56FEATUREACTMCAFIND0
57FEATUREACTMCAFIND0
58FEATUREACTMCAFIND0
59FEATUREACTMCAFINDMI0
60FEATUREACTMGDATA0
61FEATUREACTMPUDAT4010
62FEATUREACTMPUDAT4010
63FEATUREACTMPUDAT4010
64FEATUREACTP36029
65FEATUREACTP36029
66FEATUREACTP36029
67FEATUREACTP360313
68FEATUREACTP360518
69FEATUREACTP360MI313
70FEATUREDEACTACINDMI0
71FEATUREDEACTP360MI416
72FEATUREDEACTP360Y518
73UPGRADEACTMAGENTAD225
74UPGRADEACTMPLSFAM75
75FEATUREDEACTACIND0
76ACTIVITYACTHIHMTI55
77FEATUREACTMCAFIND0
78FEATUREACTMCAFIND0
79FEATUREACTP36029
80FEATUREACTP360518
81FEATUREDEACTMCAFIND0
82FEATUREDEACTMCAFIND0
83FEATUREDEACTMCAFIND0
84FEATUREDEACTP360Y518
85FEATUREDEACTP360Y518
86FEATUREDEACTP360Y518
87UPGRADEACTMPLSFAM75
88FEATUREACTACIND0
89AALACTZBUSAALCI35
90ACTIVITYACTHIHMTE55
91ACTIVITYACTZBSUNC2TI75
92ACTIVITYACTZBSUNC2TI75
93ACTIVITYACTZDIGITSAW15
94FEATUREACTZBSDTC10010
95FEATUREACTZBSDTC10010
96FEATUREACTZBSDTC10010
97FEATUREACTMCAFIND0
98FEATUREACTMCAFIND0
99FEATUREACTMCAFIND0
Sheet3
Cell Formulas
RangeFormula
K3:N3K3=TRANSPOSE(UNIQUE(I4#))
H4:H33H4=UNIQUE(D4:D99)
I4:I33I4=XLOOKUP(H4#,$D$4:$D$99,$B$4:$B$99)
K4:K12,N4,M4:M6,L4:L20K4=UNIQUE(FILTER($H$4#,($I$4#=K$3)))
Dynamic array formulas.
 
Upvote 0
Solution
Something like this?
test.xlsm
BCDEFGHIJKLMN
1lookup ArrayUnique List 1Unique List 2Desired Result from the (2) Unique Lists *** WITHOUT EMPTY CELLS ***
2
3ProductTypeActivityTypePlanCodeMonthlyAccessPlanCodeProductTypeACTIVITYFEATUREAALUPGRADE
4ACTIVITYREACTHIHMTI55HIHMTIACTIVITYHIHMTIJMP2INDMGPLSADDMAGUNL55
5FEATUREACTJMP2IND0JMP2INDFEATUREDWPDIRTIMCAFINDMAGENTAD2
6FEATUREACTMCAFIND0MCAFINDFEATUREHILT100MPUDAT40ZBUSAALCI
7FEATUREACTMPUDAT4010MPUDAT40FEATUREMPLSFAMP3602
8FEATUREACTP36029P3602FEATUREMAGTBACIND
9FEATUREACTACIND0ACINDFEATUREUNLMTTACINDMI
10FEATUREDEACTMCAFIND0MGPLSADDAALHIHMTEP3601
11FEATUREDEACTMCAFIND0DWPDIRTIACTIVITYZBSUNC2TIP3605
12FEATUREDEACTP36029HILT100ACTIVITYZDIGITSAWP360Y5
13FEATUREDEACTP36029MPLSFAMACTIVITYHD4SNFLX
14AALACTMGPLSADD35ACINDMIFEATUREMAGDATA
15AALACTMGPLSADD35P3601FEATUREMCAFINDMI
16ACTIVITYACTDWPDIRTI15P3605FEATUREP360MI3
17ACTIVITYACTHILT10055MAGUNL55UPGRADEMGDATA
18ACTIVITYACTMPLSFAM75P360Y5FEATUREP3603
19ACTIVITYACTMPLSFAM75MAGENTAD2AALP360MI4
20FEATUREACTACIND0HD4SNFLXFEATUREZBSDTC100
21FEATUREACTACIND0MAGDATAFEATURE
22FEATUREACTACINDMI0MCAFINDMIFEATURE
23FEATUREACTMPUDAT4010P360MI3FEATURE
24FEATUREACTMPUDAT4010MAGTBACTIVITY
25FEATUREACTMPUDAT4010UNLMTTACTIVITY
26FEATUREACTMPUDAT4010MGDATAFEATURE
27FEATUREACTP36029P3603FEATURE
28FEATUREDEACTACIND0P360MI4FEATURE
29FEATUREDEACTMCAFIND0ZBUSAALCIAAL
30FEATUREDEACTP36017HIHMTEACTIVITY
31FEATUREDEACTP360518ZBSUNC2TIACTIVITY
32UPGRADEACTMAGUNL5540ZDIGITSAWACTIVITY
33FEATUREACTP36029ZBSDTC100FEATURE
34FEATUREDEACTACIND0
35FEATUREDEACTACIND0
36FEATUREDEACTMCAFIND0
37FEATUREDEACTP360Y518
38AALACTMAGENTAD225
39FEATUREACTHD4SNFLX19.99
40FEATUREACTMAGDATA0
41UPGRADEACTMAGENTAD225
42FEATUREDEACTACINDMI0
43FEATUREDEACTMCAFINDMI0
44FEATUREDEACTP360MI313
45AALACTMAGENTAD225
46AALACTMGPLSADD35
47ACTIVITYACTHIHMTI55
48ACTIVITYACTMPLSFAM75
49ACTIVITYACTMPLSFAM75
50ACTIVITYDEACTMAGTB25
51ACTIVITYDEACTUNLMTT20
52FEATUREACTACIND0
53FEATUREACTACINDMI0
54FEATUREACTMCAFIND0
55FEATUREACTMCAFIND0
56FEATUREACTMCAFIND0
57FEATUREACTMCAFIND0
58FEATUREACTMCAFIND0
59FEATUREACTMCAFINDMI0
60FEATUREACTMGDATA0
61FEATUREACTMPUDAT4010
62FEATUREACTMPUDAT4010
63FEATUREACTMPUDAT4010
64FEATUREACTP36029
65FEATUREACTP36029
66FEATUREACTP36029
67FEATUREACTP360313
68FEATUREACTP360518
69FEATUREACTP360MI313
70FEATUREDEACTACINDMI0
71FEATUREDEACTP360MI416
72FEATUREDEACTP360Y518
73UPGRADEACTMAGENTAD225
74UPGRADEACTMPLSFAM75
75FEATUREDEACTACIND0
76ACTIVITYACTHIHMTI55
77FEATUREACTMCAFIND0
78FEATUREACTMCAFIND0
79FEATUREACTP36029
80FEATUREACTP360518
81FEATUREDEACTMCAFIND0
82FEATUREDEACTMCAFIND0
83FEATUREDEACTMCAFIND0
84FEATUREDEACTP360Y518
85FEATUREDEACTP360Y518
86FEATUREDEACTP360Y518
87UPGRADEACTMPLSFAM75
88FEATUREACTACIND0
89AALACTZBUSAALCI35
90ACTIVITYACTHIHMTE55
91ACTIVITYACTZBSUNC2TI75
92ACTIVITYACTZBSUNC2TI75
93ACTIVITYACTZDIGITSAW15
94FEATUREACTZBSDTC10010
95FEATUREACTZBSDTC10010
96FEATUREACTZBSDTC10010
97FEATUREACTMCAFIND0
98FEATUREACTMCAFIND0
99FEATUREACTMCAFIND0
Sheet3
Cell Formulas
RangeFormula
K3:N3K3=TRANSPOSE(UNIQUE(I4#))
H4:H33H4=UNIQUE(D4:D99)
I4:I33I4=XLOOKUP(H4#,$D$4:$D$99,$B$4:$B$99)
K4:K12,N4,M4:M6,L4:L20K4=UNIQUE(FILTER($H$4#,($I$4#=K$3)))
Dynamic array formulas.
Exactly what I meant. Thank you for the quick response!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top