One more thing....

You DID remember to commit all of the formulas with [ctrl]+[shift]+[enter]
instead of just [enter], right?
***********
Regards,
Ron

XL2002, WinXP


"Rudy" wrote:

Hi Ron

The Self-Sufficient - No value returned or 0
=COUNT(1/FREQUENCY(IF(($H$2:$H$5000=A21)*($K$2:$K$5000="")* ISNA(MATCH($E$2:$E$5000&TRUE,$E$2:$E$5000&($K$2:$K $5000<""),0)),IF($E$2:$E$5000<"",MATCH($E$2:$E$5 000,$E$2:$E$5000,0))),ROW($E$2:$E$5000)-ROW($E$2)+1))

The Dependent - Value Returned - Worked well
=COUNT(1/FREQUENCY(IF(($H$2:$H$5000=A21)*($K$2:$K$5000<"") *ISNUMBER(MATCH($E$2:$E$5000&TRUE,$E$2:$E$5000&($K $2:$K$5000<""),0)),IF($E$2:$E$5000<"",MATCH($E$2 :$E$5000,$E$2:$E$5000,0))),ROW($E$2:$E$5000)-ROW($E$2)+1))

Thank you.

Regards,
Rudy





"Ron Coderre" wrote:

Please post your formula, so I can see exactly what you're working with.

***********
Regards,
Ron

XL2002, WinXP


"Rudy" wrote:

hahaha..
Thanks for the solution Ron.
I applied the formulas you gave me to my actual worksheet with 5000 names.
The second formula (The Dependent - H2) worked well and returned with
values, however, the first formula (The Self-Sufficient - G2) returned with 0
value. Any idea when I have done wrong?
I've checked and re-checked, all seem okay.

Thanks.





"Ron Coderre" wrote:

Hmmmm....Sorry, Roger/Rudy....I had come up with an erroneous solution for
Rudy and I guess I only *thought* I posted it to this thread. I didn't even
look at the name when I responded.

More coffee, anyone? I'm getting some. : \

***********
Regards,
Ron

XL2002, WinXP


"Rudy" wrote:

Hi Roger

Thank you so much for your response. Half way through, almost solve my query.

The Matrix/Formulaes you gave me are perfect, however, they count each Names
as one value. For example, as on my list Bill Purple occurs 3 times and
Richard McDonald once. Both of their job titles are Partner and they both
have Helpers.
The formula you gave me will count Helper 4. What I would like is to count
Bill Purple as 1 (although his name appears 3 times on the list) and Richard
McDonald as 1, hence, people with job title Partner has 2 bookings done by
Helper and so on.

Many thanks.







"Roger Govier" wrote:

Hi Rudy

Set yourself up a matrix as follows

No Helper Helper
Analyst 3 3
Consultant 2 2
Executive 3 4
Manager 5 2
Partner 2 4


With Analyst in E2, Consultant in E3 etc. and No Helper in F1, Helper
in G1
in cell F2 enter the following

=SUMPRODUCT(($B$2:$B$31=$E2)*($C$2:$C$31=""))
In cell G2 enter
=SUMPRODUCT(($B$2:$B$31=$E2)*($C$2:$C$31<""))
Copy F2:G2 down through cells F3:6

The results I got are as in the table
--
Regards

Roger Govier


"Rudy" wrote in message
...
Hi
I was wondering if you could help me out in here. For example, I have
this
scenario of a database of 5000 employees who do many business travels.
Some
of these people have their tickets booked by Helpers and some do their
own
bookings. Notes that some names on the database repeated on the list
and
their Helpers could be different and some of them simply don't have
Helpers
do the bookings for them.

Name - Job Title - Assistant
Amy Green - Analyst
Mike Brown - Consultant - Helper One
Lisa White - Manager
Bill Purple - Partner - Helper Two
Lucy Yellow - Analyst - Helper Three
Ian Dunlop - Consultant
Charlie White - Consultant - Helper Four
David Smith - Manager
John Bell - Executive - Helper Seven
Graham Bell - Executive
David Green - Analyst - Helper Five
Lucy Smith - Partner
Jan Dunlop - Analyst - Helper Eight
Mike Dunlop - Manager
John Brown - Executive - Helper Five
Bill Purple - Partner - Helper Two
Carol Smith - Executive
Margaret McDonald - Manager
Cindy Wright - Manager - Helper Five
Ian Wright - Partner
Richard McDonald - Partner - Helper Four
Matthew White - Analyst
Jason Yellow - Manager - Helper Seven
Graham Bell - Executive
Matt Smith - Executive - Helper Five
Lucy Yellow - Analyst
John Bell - Executive - Helper Eight
Mike Brown - Consultant
Mike Dunlop - Manager
Bill Purple - Partner - Helper Two



I am trying to find out How many Analyst, Consultant, Executive,
Manager,
Partner have their bookings done by Helpers and How many of them do
their own
bookings, and so on .
Any solutions are greatly appreciated.
Thanks