JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Getting a unique list from the results of the UNIQUE Function ... is this possible?
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 B C D E F G H I J K L M N 1 lookup Array Unique List 1 Unique List 2 Desired Result from the (2) Unique Lists *** WITHOUT EMPTY CELLS *** 2 3 ProductType ActivityType PlanCode MonthlyAccess PlanCode ProductType ACTIVITY FEATURE AAL UPGRADE 4 ACTIVITY REACT HIHMTI $55.00 HIHMTI ACTIVITY HIHMTI 5 FEATURE ACT JMP2IND $0.00 JMP2IND FEATURE JMP2IND 6 FEATURE ACT MCAFIND $0.00 MCAFIND FEATURE MCAFIND 7 FEATURE ACT MPUDAT40 $10.00 MPUDAT40 FEATURE MPUDAT40 8 FEATURE ACT P3602 $9.00 P3602 FEATURE P3602 9 FEATURE ACT ACIND $0.00 ACIND FEATURE ACIND 10 FEATURE DEACT MCAFIND $0.00 MGPLSADD AAL ACINDMI MGPLSADD 11 FEATURE DEACT MCAFIND $0.00 DWPDIRTI ACTIVITY DWPDIRTI P3601 12 FEATURE DEACT P3602 $9.00 HILT100 ACTIVITY HILT100 P3605 13 FEATURE DEACT P3602 $9.00 MPLSFAM ACTIVITY MPLSFAM P360Y5 14 AAL ACT MGPLSADD $35.00 ACINDMI FEATURE ACINDMI 15 AAL ACT MGPLSADD $35.00 P3601 FEATURE P3601 16 ACTIVITY ACT DWPDIRTI $15.00 P3605 FEATURE P3605 17 ACTIVITY ACT HILT100 $55.00 MAGUNL55 UPGRADE P360Y5 MAGUNL55 18 ACTIVITY ACT MPLSFAM $75.00 P360Y5 FEATURE P360Y5 19 ACTIVITY ACT MPLSFAM $75.00 MAGENTAD2 AAL MAGENTAD2 20 FEATURE ACT ACIND $0.00 HD4SNFLX FEATURE HD4SNFLX 21 FEATURE ACT ACIND $0.00 MAGDATA FEATURE MAGDATA 22 FEATURE ACT ACINDMI $0.00 MCAFINDMI FEATURE MCAFINDMI 23 FEATURE ACT MPUDAT40 $10.00 P360MI3 FEATURE P360MI3 24 FEATURE ACT MPUDAT40 $10.00 MAGTB ACTIVITY MAGTB 25 FEATURE ACT MPUDAT40 $10.00 UNLMTT ACTIVITY UNLMTT 26 FEATURE ACT MPUDAT40 $10.00 MGDATA FEATURE HIHMTE MGDATA 27 FEATURE ACT P3602 $9.00 P3603 FEATURE P3603 28 FEATURE DEACT ACIND $0.00 P360MI4 FEATURE P360MI4 29 FEATURE DEACT MCAFIND $0.00 ZBUSAALCI AAL ZBUSAALCI 30 FEATURE DEACT P3601 $7.00 HIHMTE ACTIVITY HIHMTE 31 FEATURE DEACT P3605 $18.00 ZBSUNC2TI ACTIVITY ZBSUNC2TI 32 UPGRADE ACT MAGUNL55 $40.00 ZDIGITSAW ACTIVITY ZDIGITSAW 33 FEATURE ACT P3602 $9.00 ZBSDTC100 FEATURE ZBSDTC100 34 FEATURE DEACT ACIND $0.00 35 FEATURE DEACT ACIND $0.00 36 FEATURE DEACT MCAFIND $0.00 37 FEATURE DEACT P360Y5 $18.00 38 AAL ACT MAGENTAD2 $25.00 39 FEATURE ACT HD4SNFLX $19.99 40 FEATURE ACT MAGDATA $0.00 41 UPGRADE ACT MAGENTAD2 $25.00 42 FEATURE DEACT ACINDMI $0.00 43 FEATURE DEACT MCAFINDMI $0.00 44 FEATURE DEACT P360MI3 $13.00 45 AAL ACT MAGENTAD2 $25.00 46 AAL ACT MGPLSADD $35.00 47 ACTIVITY ACT HIHMTI $55.00 48 ACTIVITY ACT MPLSFAM $75.00 49 ACTIVITY ACT MPLSFAM $75.00 50 ACTIVITY DEACT MAGTB $25.00 51 ACTIVITY DEACT UNLMTT $20.00 52 FEATURE ACT ACIND $0.00 53 FEATURE ACT ACINDMI $0.00 54 FEATURE ACT MCAFIND $0.00 55 FEATURE ACT MCAFIND $0.00 56 FEATURE ACT MCAFIND $0.00 57 FEATURE ACT MCAFIND $0.00 58 FEATURE ACT MCAFIND $0.00 59 FEATURE ACT MCAFINDMI $0.00 60 FEATURE ACT MGDATA $0.00 61 FEATURE ACT MPUDAT40 $10.00 62 FEATURE ACT MPUDAT40 $10.00 63 FEATURE ACT MPUDAT40 $10.00 64 FEATURE ACT P3602 $9.00 65 FEATURE ACT P3602 $9.00 66 FEATURE ACT P3602 $9.00 67 FEATURE ACT P3603 $13.00 68 FEATURE ACT P3605 $18.00 69 FEATURE ACT P360MI3 $13.00 70 FEATURE DEACT ACINDMI $0.00 71 FEATURE DEACT P360MI4 $16.00 72 FEATURE DEACT P360Y5 $18.00 73 UPGRADE ACT MAGENTAD2 $25.00 74 UPGRADE ACT MPLSFAM $75.00 75 FEATURE DEACT ACIND $0.00 76 ACTIVITY ACT HIHMTI $55.00 77 FEATURE ACT MCAFIND $0.00 78 FEATURE ACT MCAFIND $0.00 79 FEATURE ACT P3602 $9.00 80 FEATURE ACT P3605 $18.00 81 FEATURE DEACT MCAFIND $0.00 82 FEATURE DEACT MCAFIND $0.00 83 FEATURE DEACT MCAFIND $0.00 84 FEATURE DEACT P360Y5 $18.00 85 FEATURE DEACT P360Y5 $18.00 86 FEATURE DEACT P360Y5 $18.00 87 UPGRADE ACT MPLSFAM $75.00 88 FEATURE ACT ACIND $0.00 89 AAL ACT ZBUSAALCI $35.00 90 ACTIVITY ACT HIHMTE $55.00 91 ACTIVITY ACT ZBSUNC2TI $75.00 92 ACTIVITY ACT ZBSUNC2TI $75.00 93 ACTIVITY ACT ZDIGITSAW $15.00 94 FEATURE ACT ZBSDTC100 $10.00 95 FEATURE ACT ZBSDTC100 $10.00 96 FEATURE ACT ZBSDTC100 $10.00 97 FEATURE ACT MCAFIND $0.00 98 FEATURE ACT MCAFIND $0.00 99 FEATURE ACT MCAFIND $0.00
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 B C D E F G H I J K L M N 1 lookup Array Unique List 1 Unique List 2 Desired Result from the (2) Unique Lists *** WITHOUT EMPTY CELLS *** 2 3 ProductType ActivityType PlanCode MonthlyAccess PlanCode ProductType ACTIVITY FEATURE AAL UPGRADE 4 ACTIVITY REACT HIHMTI 55 HIHMTI ACTIVITY HIHMTI JMP2IND MGPLSADD MAGUNL55 5 FEATURE ACT JMP2IND 0 JMP2IND FEATURE DWPDIRTI MCAFIND MAGENTAD2 6 FEATURE ACT MCAFIND 0 MCAFIND FEATURE HILT100 MPUDAT40 ZBUSAALCI 7 FEATURE ACT MPUDAT40 10 MPUDAT40 FEATURE MPLSFAM P3602 8 FEATURE ACT P3602 9 P3602 FEATURE MAGTB ACIND 9 FEATURE ACT ACIND 0 ACIND FEATURE UNLMTT ACINDMI 10 FEATURE DEACT MCAFIND 0 MGPLSADD AAL HIHMTE P3601 11 FEATURE DEACT MCAFIND 0 DWPDIRTI ACTIVITY ZBSUNC2TI P3605 12 FEATURE DEACT P3602 9 HILT100 ACTIVITY ZDIGITSAW P360Y5 13 FEATURE DEACT P3602 9 MPLSFAM ACTIVITY HD4SNFLX 14 AAL ACT MGPLSADD 35 ACINDMI FEATURE MAGDATA 15 AAL ACT MGPLSADD 35 P3601 FEATURE MCAFINDMI 16 ACTIVITY ACT DWPDIRTI 15 P3605 FEATURE P360MI3 17 ACTIVITY ACT HILT100 55 MAGUNL55 UPGRADE MGDATA 18 ACTIVITY ACT MPLSFAM 75 P360Y5 FEATURE P3603 19 ACTIVITY ACT MPLSFAM 75 MAGENTAD2 AAL P360MI4 20 FEATURE ACT ACIND 0 HD4SNFLX FEATURE ZBSDTC100 21 FEATURE ACT ACIND 0 MAGDATA FEATURE 22 FEATURE ACT ACINDMI 0 MCAFINDMI FEATURE 23 FEATURE ACT MPUDAT40 10 P360MI3 FEATURE 24 FEATURE ACT MPUDAT40 10 MAGTB ACTIVITY 25 FEATURE ACT MPUDAT40 10 UNLMTT ACTIVITY 26 FEATURE ACT MPUDAT40 10 MGDATA FEATURE 27 FEATURE ACT P3602 9 P3603 FEATURE 28 FEATURE DEACT ACIND 0 P360MI4 FEATURE 29 FEATURE DEACT MCAFIND 0 ZBUSAALCI AAL 30 FEATURE DEACT P3601 7 HIHMTE ACTIVITY 31 FEATURE DEACT P3605 18 ZBSUNC2TI ACTIVITY 32 UPGRADE ACT MAGUNL55 40 ZDIGITSAW ACTIVITY 33 FEATURE ACT P3602 9 ZBSDTC100 FEATURE 34 FEATURE DEACT ACIND 0 35 FEATURE DEACT ACIND 0 36 FEATURE DEACT MCAFIND 0 37 FEATURE DEACT P360Y5 18 38 AAL ACT MAGENTAD2 25 39 FEATURE ACT HD4SNFLX 19.99 40 FEATURE ACT MAGDATA 0 41 UPGRADE ACT MAGENTAD2 25 42 FEATURE DEACT ACINDMI 0 43 FEATURE DEACT MCAFINDMI 0 44 FEATURE DEACT P360MI3 13 45 AAL ACT MAGENTAD2 25 46 AAL ACT MGPLSADD 35 47 ACTIVITY ACT HIHMTI 55 48 ACTIVITY ACT MPLSFAM 75 49 ACTIVITY ACT MPLSFAM 75 50 ACTIVITY DEACT MAGTB 25 51 ACTIVITY DEACT UNLMTT 20 52 FEATURE ACT ACIND 0 53 FEATURE ACT ACINDMI 0 54 FEATURE ACT MCAFIND 0 55 FEATURE ACT MCAFIND 0 56 FEATURE ACT MCAFIND 0 57 FEATURE ACT MCAFIND 0 58 FEATURE ACT MCAFIND 0 59 FEATURE ACT MCAFINDMI 0 60 FEATURE ACT MGDATA 0 61 FEATURE ACT MPUDAT40 10 62 FEATURE ACT MPUDAT40 10 63 FEATURE ACT MPUDAT40 10 64 FEATURE ACT P3602 9 65 FEATURE ACT P3602 9 66 FEATURE ACT P3602 9 67 FEATURE ACT P3603 13 68 FEATURE ACT P3605 18 69 FEATURE ACT P360MI3 13 70 FEATURE DEACT ACINDMI 0 71 FEATURE DEACT P360MI4 16 72 FEATURE DEACT P360Y5 18 73 UPGRADE ACT MAGENTAD2 25 74 UPGRADE ACT MPLSFAM 75 75 FEATURE DEACT ACIND 0 76 ACTIVITY ACT HIHMTI 55 77 FEATURE ACT MCAFIND 0 78 FEATURE ACT MCAFIND 0 79 FEATURE ACT P3602 9 80 FEATURE ACT P3605 18 81 FEATURE DEACT MCAFIND 0 82 FEATURE DEACT MCAFIND 0 83 FEATURE DEACT MCAFIND 0 84 FEATURE DEACT P360Y5 18 85 FEATURE DEACT P360Y5 18 86 FEATURE DEACT P360Y5 18 87 UPGRADE ACT MPLSFAM 75 88 FEATURE ACT ACIND 0 89 AAL ACT ZBUSAALCI 35 90 ACTIVITY ACT HIHMTE 55 91 ACTIVITY ACT ZBSUNC2TI 75 92 ACTIVITY ACT ZBSUNC2TI 75 93 ACTIVITY ACT ZDIGITSAW 15 94 FEATURE ACT ZBSDTC100 10 95 FEATURE ACT ZBSDTC100 10 96 FEATURE ACT ZBSDTC100 10 97 FEATURE ACT MCAFIND 0 98 FEATURE ACT MCAFIND 0 99 FEATURE ACT MCAFIND 0
Something like this?
test.xlsm B C D E F G H I J K L M N 1 lookup Array Unique List 1 Unique List 2 Desired Result from the (2) Unique Lists *** WITHOUT EMPTY CELLS *** 2 3 ProductType ActivityType PlanCode MonthlyAccess PlanCode ProductType ACTIVITY FEATURE AAL UPGRADE 4 ACTIVITY REACT HIHMTI 55 HIHMTI ACTIVITY HIHMTI JMP2IND MGPLSADD MAGUNL55 5 FEATURE ACT JMP2IND 0 JMP2IND FEATURE DWPDIRTI MCAFIND MAGENTAD2 6 FEATURE ACT MCAFIND 0 MCAFIND FEATURE HILT100 MPUDAT40 ZBUSAALCI 7 FEATURE ACT MPUDAT40 10 MPUDAT40 FEATURE MPLSFAM P3602 8 FEATURE ACT P3602 9 P3602 FEATURE MAGTB ACIND 9 FEATURE ACT ACIND 0 ACIND FEATURE UNLMTT ACINDMI 10 FEATURE DEACT MCAFIND 0 MGPLSADD AAL HIHMTE P3601 11 FEATURE DEACT MCAFIND 0 DWPDIRTI ACTIVITY ZBSUNC2TI P3605 12 FEATURE DEACT P3602 9 HILT100 ACTIVITY ZDIGITSAW P360Y5 13 FEATURE DEACT P3602 9 MPLSFAM ACTIVITY HD4SNFLX 14 AAL ACT MGPLSADD 35 ACINDMI FEATURE MAGDATA 15 AAL ACT MGPLSADD 35 P3601 FEATURE MCAFINDMI 16 ACTIVITY ACT DWPDIRTI 15 P3605 FEATURE P360MI3 17 ACTIVITY ACT HILT100 55 MAGUNL55 UPGRADE MGDATA 18 ACTIVITY ACT MPLSFAM 75 P360Y5 FEATURE P3603 19 ACTIVITY ACT MPLSFAM 75 MAGENTAD2 AAL P360MI4 20 FEATURE ACT ACIND 0 HD4SNFLX FEATURE ZBSDTC100 21 FEATURE ACT ACIND 0 MAGDATA FEATURE 22 FEATURE ACT ACINDMI 0 MCAFINDMI FEATURE 23 FEATURE ACT MPUDAT40 10 P360MI3 FEATURE 24 FEATURE ACT MPUDAT40 10 MAGTB ACTIVITY 25 FEATURE ACT MPUDAT40 10 UNLMTT ACTIVITY 26 FEATURE ACT MPUDAT40 10 MGDATA FEATURE 27 FEATURE ACT P3602 9 P3603 FEATURE 28 FEATURE DEACT ACIND 0 P360MI4 FEATURE 29 FEATURE DEACT MCAFIND 0 ZBUSAALCI AAL 30 FEATURE DEACT P3601 7 HIHMTE ACTIVITY 31 FEATURE DEACT P3605 18 ZBSUNC2TI ACTIVITY 32 UPGRADE ACT MAGUNL55 40 ZDIGITSAW ACTIVITY 33 FEATURE ACT P3602 9 ZBSDTC100 FEATURE 34 FEATURE DEACT ACIND 0 35 FEATURE DEACT ACIND 0 36 FEATURE DEACT MCAFIND 0 37 FEATURE DEACT P360Y5 18 38 AAL ACT MAGENTAD2 25 39 FEATURE ACT HD4SNFLX 19.99 40 FEATURE ACT MAGDATA 0 41 UPGRADE ACT MAGENTAD2 25 42 FEATURE DEACT ACINDMI 0 43 FEATURE DEACT MCAFINDMI 0 44 FEATURE DEACT P360MI3 13 45 AAL ACT MAGENTAD2 25 46 AAL ACT MGPLSADD 35 47 ACTIVITY ACT HIHMTI 55 48 ACTIVITY ACT MPLSFAM 75 49 ACTIVITY ACT MPLSFAM 75 50 ACTIVITY DEACT MAGTB 25 51 ACTIVITY DEACT UNLMTT 20 52 FEATURE ACT ACIND 0 53 FEATURE ACT ACINDMI 0 54 FEATURE ACT MCAFIND 0 55 FEATURE ACT MCAFIND 0 56 FEATURE ACT MCAFIND 0 57 FEATURE ACT MCAFIND 0 58 FEATURE ACT MCAFIND 0 59 FEATURE ACT MCAFINDMI 0 60 FEATURE ACT MGDATA 0 61 FEATURE ACT MPUDAT40 10 62 FEATURE ACT MPUDAT40 10 63 FEATURE ACT MPUDAT40 10 64 FEATURE ACT P3602 9 65 FEATURE ACT P3602 9 66 FEATURE ACT P3602 9 67 FEATURE ACT P3603 13 68 FEATURE ACT P3605 18 69 FEATURE ACT P360MI3 13 70 FEATURE DEACT ACINDMI 0 71 FEATURE DEACT P360MI4 16 72 FEATURE DEACT P360Y5 18 73 UPGRADE ACT MAGENTAD2 25 74 UPGRADE ACT MPLSFAM 75 75 FEATURE DEACT ACIND 0 76 ACTIVITY ACT HIHMTI 55 77 FEATURE ACT MCAFIND 0 78 FEATURE ACT MCAFIND 0 79 FEATURE ACT P3602 9 80 FEATURE ACT P3605 18 81 FEATURE DEACT MCAFIND 0 82 FEATURE DEACT MCAFIND 0 83 FEATURE DEACT MCAFIND 0 84 FEATURE DEACT P360Y5 18 85 FEATURE DEACT P360Y5 18 86 FEATURE DEACT P360Y5 18 87 UPGRADE ACT MPLSFAM 75 88 FEATURE ACT ACIND 0 89 AAL ACT ZBUSAALCI 35 90 ACTIVITY ACT HIHMTE 55 91 ACTIVITY ACT ZBSUNC2TI 75 92 ACTIVITY ACT ZBSUNC2TI 75 93 ACTIVITY ACT ZDIGITSAW 15 94 FEATURE ACT ZBSDTC100 10 95 FEATURE ACT ZBSDTC100 10 96 FEATURE ACT ZBSDTC100 10 97 FEATURE ACT MCAFIND 0 98 FEATURE ACT MCAFIND 0 99 FEATURE ACT MCAFIND 0
Exactly what I meant. Thank you for the quick response!
Exactly what I meant. Thank you for the quick response!
Happy to help, and thanks for the feedback
Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance
