Advanced combination search

[mac_see]

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
2	Group1	A	B	D	E	H		Team1	A	B	C
3	Group2	A	B	G	H	J		Team2	A	B	D
4	Group3	B	C	D	H	J		Team3	A	B	E
5	Group4	B	G	H	I	J		Team4	A	C	D
6	Group5	A	B	E	F	I		Team5	A	C	E
7	Group6	B	C	D	E	F		Team6	A	D	E
8	Group7	A	E	G	H	J		Team7	B	C	D
9	Group8	D	E	G	H	I		Team8	B	C	E
10	Group9	D	E	F	H	I		Team9	B	D	E
11	Group10	D	E	F	G	H		Team10	C	D	E
12	Group11	C	D	E	I	J
13	Group12	A	C	H	I	J
14	Group13	B	E	F	G	J
15	Group14	C	D	E	I	J
16	Group15	A	B	D	I	J
17	Group16	B	C	F	G	H
18	Group17	A	C	D	E	H
19	Group18	C	D	E	F	J
20	Group19	B	C	D	E	I
21	Group20	B	C	D	I	J
Workout

I am looking for formulas in the range M1:M11. The crieteria is:

I want to search in which group all the 3 team members were present.
Example:
In Team1 A B C together were not present in any of the 20 groups.
In Team2 A B D together were present in Group1, Group15.

I hope, I am very much clear in explaining this.

I thought of a workaround but my data has 500 Groups and unforunately I cannot get any way to achieve what I am looking for.

Maxi

 

[just_jon]

Do you want A] a yes/no answer, or B] a group name, or C] all matching group names, or D] something else entirely as I've missed the point?

 

[mac_see]

I need a count

Example:
In Team1 A B C together were not present in any of the 20 groups.
In Team2 A B D together were present in Group1, Group15.

In example 1, cell M2 should populate with "0"
In example 2, cell M3 should populate with "2" coz its present in two groups

Maxi

 

[mac_see]

Hi! Jon,

It would be fine if I can get "C] all matching group names" but this is secondary and not so important. My primary goal is to get the count in the range M1:M11.

Maxi

 

[Yogi Anand]

Hi mac_see:

Here is a Data Table solution ...

y031227h1.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Field1	Field2	Field3	Field4	Field5	Field6
2	Group1	A	B	D	E	H							FALSE
3	Group2	A	B	G	H	J							onevariable
4	Group3	B	C	D	H	J							DataTable
5	Group4	B	G	H	I	J							Team	Count
6	Group5	A	B	E	F	I		Team1	A	B	C	ABC	ABC	0
7	Group6	B	C	D	E	F		Team2	A	B	D	ABD	ABD	2
8	Group7	A	E	G	H	J		Team3	A	B	E	ABE	ABE	2
9	Group8	D	E	G	H	I		Team4	A	C	D	ACD	ACD	1
10	Group9	D	E	F	H	I		Team5	A	C	E	ACE	ACE	1
11	Group10	D	E	F	G	H		Team6	A	D	E	ADE	ADE	2
12	Group11	C	D	E	I	J		Team7	B	C	D	BCD	BCD	4
13	Group12	A	C	H	I	J		Team8	B	C	E	BCE	BCE	2
14	Group13	B	E	F	G	J		Team9	B	D	E	BDE	BDE	3
15	Group14	C	D	E	I	J		Team10	C	D	E	CDE	CDE	6
16	Group15	A	B	D	I	J
Sheet4b

The formula in cell N2 is ...

=SUMPRODUCT(--ISNUMBER(FIND(MID($N$5,ROW(INDIRECT("1:3")),1),C2&D2&E2&F2&G2)))=3

The formula in cell O5 is ...

=DCOUNTA(B$1:G21,1,$N$1:$N$2)

The formula in cell M2 is ...

=J6&K6&L6

 

[mac_see]

Thank you for your reply Yogi.

I am trying to implement your solution. I have laid down all the data and the formula but somehow it is not working. When I put the formula {=TABLE(,N5)} in O6, it does not show the value, it simply shows the same thing "{=TABLE(,N5)}"

If I remove the { } and press CTRL+SHIFT+ENTER, I get an error "That function is not valid"

Maxi

 

[Aladin Akyurek]

...I am trying to implement your solution. I have laid down all the data and the formula but somehow it is not working. When I put the formula {=TABLE(,N5)} in O6, it does not show the value, it simply shows the same thing "{=TABLE(,N5)}"

If I remove the { } and press CTRL+SHIFT+ENTER, I get an error "That function is not valid"...

See

http://support.microsoft.com/default.aspx?scid=kb;en-us;282851

for instructions.

 

[mac_see]

Hi Aladin,

I saw the article and I spent around 20 mins but no clue !!!

Its very confusing for a person like me. In my example, do I have to name a range? What do I select for the Row input cell and Column input cell?

Maxi

 

[Yogi Anand]

Hi Maxi:

Let us see if I can steer you along in using the DataTable that I posted in response to your question. Let us refer to that table and see how to make it work ...

1. You enter all the formulas in the respective cells

2. You do not enter any thing in cells O6 through O15

3. The curly braces that you see as Part of Excel's DataTable are not to be manually entered -- those are entered by EXCEL in response to your request to create a Data Table

4. With all the formulas and the text entered, select the cells N5:O15, then do DATA|Table, then in the dialog box that pops up, in the Column_input_cell text box, enter $N$5; leave the Row_input_cell empty, and click OK

5. View the results in cells O5:O15 -- if you see the results -- celebrate success; if you don't see the results, go back to step 1, and make sure that all the preparatory work to create the Data Table has been correctly done.

If you still need further help, post back and let us take it from there.

 

[mac_see]

Hi! Yogi,

A minute of explanation is better than 20 minutes of confusion lol.

I got the results but not the LOGIC. I will understand the data by writing anoter example for myself. If I can do it without any help then I will sure celebrate the success. If not, I will come back to you.

Thanx a million !!

Maxi