brockk
Board Regular
- Joined
- Jul 1, 2006
- Messages
- 170
- Office Version
- 2013
- Platform
- 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?
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 | |||||||||||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K3:N3 | K3 | =TRANSPOSE(UNIQUE(I4#)) |
| H4:H33 | H4 | =UNIQUE(D4:D99) |
| I4:I33 | I4 | =XLOOKUP(H4#,$D$4:$D$99,$B$4:$B$99) |
| Dynamic array formulas. | ||
