mac_see

Active Member
Book1
BCDEFGHIJKLM
2Group1ABDEHTeam1ABC
3Group2ABGHJTeam2ABD
4Group3BCDHJTeam3ABE
5Group4BGHIJTeam4ACD
6Group5ABEFITeam5ACE
8Group7AEGHJTeam7BCD
9Group8DEGHITeam8BCE
10Group9DEFHITeam9BDE
11Group10DEFGHTeam10CDE
12Group11CDEIJ
13Group12ACHIJ
14Group13BEFGJ
15Group14CDEIJ
16Group15ABDIJ
17Group16BCFGH
18Group17ACDEH
19Group18CDEFJ
20Group19BCDEI
21Group20BCDIJ
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

Excel Facts

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.
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?

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

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

Hi mac_see:

Here is a Data Table solution ...
y031227h1.xls
BCDEFGHIJKLMNO
1Field1Field2Field3Field4Field5Field6
2Group1ABDEHFALSE
3Group2ABGHJonevariable
4Group3BCDHJDataTable
5Group4BGHIJTeamCount
6Group5ABEFITeam1ABCABCABC0
7Group6BCDEFTeam2ABDABDABD2
8Group7AEGHJTeam3ABEABEABE2
9Group8DEGHITeam4ACDACDACD1
10Group9DEFHITeam5ACEACEACE1
12Group11CDEIJTeam7BCDBCDBCD4
13Group12ACHIJTeam8BCEBCEBCE2
14Group13BEFGJTeam9BDEBDEBDE3
15Group14CDEIJTeam10CDECDECDE6
16Group15ABDIJ
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

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

mac_see said:
...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.

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

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.

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

Replies
4
Views
156
Replies
1
Views
970
Replies
2
Views
508
Replies
7
Views
2K
Replies
5
Views
1K

1,196,485
Messages
6,015,472
Members
441,898
Latest member
kofafa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back