Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
set operations: union, intersection, etc. and alignment
Hi,
Lets say I have 3 lists: A: 1, 2, 3, 4, 5 B: 3, 4, 5, 6 C: 3, 6, 9 (In real life I have many more lists and they are much longer...) Is there any relatively easy way to do set operations? So say I want a count and list of each member that is in all 3 sets. This should give me a list of 1 member: 3. Or if I want just the intersection of A & B: 3, 4, 5. Or I want members that are unique to A: 1, 2. etc. Ideally, I'd like to be able to tell Excel which sets I want to compare, and have it tell me which members are in the intersection of all sets, and which members are unique to each set. The other thing I'd like to be able to do is automatically align different lists. Lets say the lists above aren't a single row/column but a pair of values: A: 1 a 2 b 3 c 4 d 5 e B: 3 three 4 four 5 five 6 six C: 3 x 6 y 9 z So I want to automatically generate a table that would look like: A B C 1 a 2 b 3 c three x 4 d four 5 e five 6 six y 9 z Any way to do that? Thanks! T. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
set operations: union, intersection, etc. and alignment
Use SUMPRODUCT for first and VLOOKUP for the second.
I will give details a little later. "T Magritte" wrote: Hi, Lets say I have 3 lists: A: 1, 2, 3, 4, 5 B: 3, 4, 5, 6 C: 3, 6, 9 (In real life I have many more lists and they are much longer...) Is there any relatively easy way to do set operations? So say I want a count and list of each member that is in all 3 sets. This should give me a list of 1 member: 3. Or if I want just the intersection of A & B: 3, 4, 5. Or I want members that are unique to A: 1, 2. etc. Ideally, I'd like to be able to tell Excel which sets I want to compare, and have it tell me which members are in the intersection of all sets, and which members are unique to each set. The other thing I'd like to be able to do is automatically align different lists. Lets say the lists above aren't a single row/column but a pair of values: A: 1 a 2 b 3 c 4 d 5 e B: 3 three 4 four 5 five 6 six C: 3 x 6 y 9 z So I want to automatically generate a table that would look like: A B C 1 a 2 b 3 c three x 4 d four 5 e five 6 six y 9 z Any way to do that? Thanks! T. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
set operations: union, intersection, etc. and alignment
On Aug 26, 6:11*pm, Sheeloo wrote:
Use SUMPRODUCT for first and VLOOKUP for the second. I will give details a little later. I've been using VLOOKUP for doing the combined table and it seems to work okay. I don't really understand SUMPRODUCT or how I'd use it as described. However, once I've got the combined table, I can use add some columns with COUNT functions to determine which rows have members in which columns or groups of columns then filter based on those. However, VLOOKUP seems to always return a "0" for empty cells and a "#N/A" error when the lookup value can't be found. So I've been having to copy and paste as values the resulting table then do a search and replace to get restore "0" and "#N/A" into blank cells. I suppose I could use an IF function, but then I'd potentially have to do a VLOOKUP twice for each cell: once for the IF, and once to return the value. Probably not that big a deal. It would be nice, if Excel made better use of multicore and memory, though... I do find it's a lot of manual editing to make this stuff, work though. I suppose I could try and write a VBA program to do automate it, but it's been a long time since I've done something like that and I'm not sure I'd save much time in the long run. I'm also wondering if Access might be a better tool for these sorts of problems. I'm finding the learning curve on Access is rather steep, though... Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
set operations: union, intersection, etc. and alignment
Unless you have huge amount of data, using VLOOKUP twice won't have any
significant impact. You should use IF and ISNA functions to avoid manual effort. Excel is designed for use of people who just enter a bunch of numbers and have row/column totals to sophisticated users who use VBA or even other languages to use it. I would suggest you gradually move towards more and more automation based on your requirements. To learn about ARRAY formulae go to http://www.cpearson.com/excel/ArrayFormulas.aspx. SUMPRODUCT essentially operates on ARRAYS of values. If you have two arrays and want to see how many meet a condition then it evaluates each value against the condition and returns TRUE or FALSE. By summing up TRUE values it gives you a count. You can convert TRUE to 1 by adding -- before the array, multiply it with the ARRAY to get total of values meeting the condition etc. One must learn these plus VLOOKUP to use EXCEL effectively without doing any programming. "T Magritte" wrote: On Aug 26, 6:11 pm, Sheeloo wrote: Use SUMPRODUCT for first and VLOOKUP for the second. I will give details a little later. I've been using VLOOKUP for doing the combined table and it seems to work okay. I don't really understand SUMPRODUCT or how I'd use it as described. However, once I've got the combined table, I can use add some columns with COUNT functions to determine which rows have members in which columns or groups of columns then filter based on those. However, VLOOKUP seems to always return a "0" for empty cells and a "#N/A" error when the lookup value can't be found. So I've been having to copy and paste as values the resulting table then do a search and replace to get restore "0" and "#N/A" into blank cells. I suppose I could use an IF function, but then I'd potentially have to do a VLOOKUP twice for each cell: once for the IF, and once to return the value. Probably not that big a deal. It would be nice, if Excel made better use of multicore and memory, though... I do find it's a lot of manual editing to make this stuff, work though. I suppose I could try and write a VBA program to do automate it, but it's been a long time since I've done something like that and I'm not sure I'd save much time in the long run. I'm also wondering if Access might be a better tool for these sorts of problems. I'm finding the learning curve on Access is rather steep, though... Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
set operations: union, intersection, etc. and alignment
Thanks for the tips.
I ended up just writing a couple of custom functions to do the lookup and filter out 0's and N/A's where appropriate. It makes for easier function entry and Vlookup only has to be run once per cell since it can be saved in a temporary variable for error checking. Running Vlookup twice probably wouldn't make that much of a deal, however these are rather large datasets (potentially up to about a million entries per list) so I figure it's a good idea to keep things as efficient as possible.... I'll look into the array operations. I've never seen much use for them, but maybe it's finally time to see what they have to offer... Thanks, T. On Aug 28, 1:26*am, Sheeloo wrote: Unless you have huge amount of data, using VLOOKUP twice won't have any significant impact. You should use IF and ISNA functions to avoid manual effort. Excel is designed for use of people who just enter a bunch of numbers and have row/column totals to sophisticated users who use VBA or even other languages to use it. I would suggest you gradually move towards more and more automation based on your requirements. To learn about ARRAY formulae go tohttp://www.cpearson.com/excel/ArrayFormulas.aspx. SUMPRODUCT essentially operates on ARRAYS of values. If you have two arrays and want to see how many meet a condition then it evaluates each value against the condition and returns TRUE or FALSE. By summing up TRUE values it gives you a count. You can convert TRUE to 1 by adding -- before the array, multiply it with the ARRAY to get total of values meeting the condition etc. One must learn these plus VLOOKUP to use EXCEL effectively without doing any programming. "T Magritte" wrote: On Aug 26, 6:11 pm, Sheeloo wrote: Use SUMPRODUCT for first and VLOOKUP for the second. I will give details a little later. I've been using VLOOKUP for doing the combined table and it seems to work okay. I don't really understand SUMPRODUCT or how I'd use it as described. However, once I've got the combined table, I can use add some columns with COUNT functions to determine which rows have members in which columns or groups of columns then filter based on those. However, VLOOKUP seems to always return a "0" for empty cells and a "#N/A" error when the lookup value can't be found. So I've been having to copy and paste as values the resulting table then do a search and replace to get restore "0" and "#N/A" into blank cells. I suppose I could use an IF function, but then I'd potentially have to do a VLOOKUP twice for each cell: once for the IF, and once to return the value. Probably not that big a deal. It would be nice, if Excel made better use of multicore and memory, though... I do find it's a lot of manual editing to make this stuff, work though. I suppose I could try and write a VBA program to do automate it, but it's been a long time since I've done something like that and I'm not sure I'd save much time in the long run. I'm also wondering if Access might be a better tool for these sorts of problems. I'm finding the learning curve on Access is rather steep, though... Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Union Query | Excel Discussion (Misc queries) | |||
union range | Excel Discussion (Misc queries) | |||
Intersection, union and difference | Excel Worksheet Functions | |||
UNION of Arrays - is possible? | Excel Discussion (Misc queries) | |||
Help w/ Union Queries | Excel Discussion (Misc queries) |