Hi Jmacmd,
I'm not sure I understand the question but here's and example of exclusions for combinations.
I assume you know how to create an ms-query so this is an abbreviated step by step, if you are not familiar with ms-query on self excel file you can read this great in depth step by step explanation
Use MS Query to Treat Excel as a Relational Data Source
Abbreviated step by step:
1. define named ranges, these must start with a header name
2. each named range should consist of only one column and you must define as many as you need for your combination for example:
| char1 | char2 | char3 |
| AA | 1 | WX |
| AB | 3 | WY |
| AC | 5 | |
| 7 | |
<tbody>
</tbody>
3. run an ms-query and point-it at your own file, you will see it will come up with the 3 named ranges as if they were tables4. execute the query and this will produce:
| char1 | char2 | char3 |
| AA | 1 | WX |
| AA | 1 | WY |
| AB | 1 | WX |
| AB | 1 | WY |
| AC | 1 | WX |
| AC | 1 | WY |
| AA | 3 | WX |
| AA | 3 | WY |
| AB | 3 | WX |
| AB | 3 | WY |
| AC | 3 | WX |
| AC | 3 | WY |
| AA | 5 | WX |
| AA | 5 | WY |
| AB | 5 | WX |
| AB | 5 | WY |
| AC | 5 | WX |
| AC | 5 | WY |
| AA | 7 | WX |
| AA | 7 | WY |
| AB | 7 | WX |
| AB | 7 | WY |
| AC | 7 | WX |
| AC | 7 | WY |
<tbody>
</tbody>
5. if you wish to have exclusions you may try editing the query, the original would look like this:
SELECT char1.char1, char2.char2, char3.char3
FROM char1 char1, char2 char2, char3 char3
and say you want AA only to combine with WX, then add the following to the query:
WHERE (char1='AA' and char3='WX') or char1<>'AA'
as you can see for combinations with AA we only have WX and no WY's:
| char1 | char2 | char3 |
| AA | 1 | WX |
| AB | 1 | WX |
| AB | 1 | WY |
| AC | 1 | WX |
| AC | 1 | WY |
| AA | 3 | WX |
| AB | 3 | WX |
| AB | 3 | WY |
| AC | 3 | WX |
| AC | 3 | WY |
| AA | 5 | WX |
| AB | 5 | WX |
| AB | 5 | WY |
| AC | 5 | WX |
| AC | 5 | WY |
| AA | 7 | WX |
| AB | 7 | WX |
| AB | 7 | WY |
| AC | 7 | WX |
| AC | 7 | WY |
<tbody>
</tbody>
6. you can have additional exclusions to the where statements using AND, for example:
WHERE ((char1='AA' AND char3='WX') OR char1<>'AA') AND ((char1='AC' AND char2=3) OR char1<>'AC')
this will produce:
| char1 | char2 | char3 |
| AA | 1 | WX |
| AB | 1 | WX |
| AB | 1 | WY |
| AA | 3 | WX |
| AB | 3 | WX |
| AB | 3 | WY |
| AC | 3 | WX |
| AC | 3 | WY |
| AA | 5 | WX |
| AB | 5 | WX |
| AB | 5 | WY |
| AA | 7 | WX |
| AB | 7 | WX |
| AB | 7 | WY |
<tbody>
</tbody>
Hope I've responded to you question, sorry for the delayed response.
Angel
Hey angelEG,I was wondering how you removed the duplicates. I can't seem to figure it out. Thanks.
