|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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" <Rudy[ at ]discussions.microsoft.com> wrote in message
news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
[Quoted Text] > 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
>
|
|
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:
[Quoted Text] > 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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > 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
> >
>
>
>
|
|
Yeah....I realized my mistake *after* I posted.
See if this solution fills the bill:
Again...with your posted data in A1:C31
G1: Self-Sufficient
H1: Dependent
F1: Category
F2: Partner
F3: Executive
F4: Manager
F5: Consultant
F6: Analyst
ARRAY FORMULAS*
G2:
=COUNT(1/FREQUENCY(IF(($B$2:$B$31=F2)*($C$2:$C$31="")*ISNA(MATCH($A$2:$A$31&TRUE,$A$2:$A$31&($C$2:$C$31<>""),0)),IF($A$2:$A$31<>"",MATCH($A$2:$A$31,$A$2:$A$31,0))),ROW($A$2:$A$31)-ROW($A$2)+1))
H2:
=COUNT(1/FREQUENCY(IF(($B$2:$B$31=F2)*($C$2:$C$31<>"")*ISNUMBER(MATCH($A$2:$A$31&TRUE,$A$2:$A$31&($C$2:$C$31<>""),0)),IF($A$2:$A$31<>"",MATCH($A$2:$A$31,$A$2:$A$31,0))),ROW($A$2:$A$31)-ROW($A$2)+1))
*Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Note_2: Since text wrap will undoubtedly impact the display, there are NO
spaces in those formulas
Copy cells G2:H2
Paste into G3:H6
Using your data, those formulas returned these values:
Category_____Self-Sufficient_____Dependent
Partner________2_______________2
Executive______2_______________3
Manager______4_______________2
Consultant _____1_______________2
Analyst________2_______________3
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Rudy" wrote:
[Quoted Text] > 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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > 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
> > >
> >
> >
> >
|
|
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:
[Quoted Text] > 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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > 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
> > >
> >
> >
> >
|
|
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:
[Quoted Text] > 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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
|
|
Please post your formula, so I can see exactly what you're working with.
***********
Regards,
Ron
XL2002, WinXP
"Rudy" wrote:
[Quoted Text] > 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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
|
|
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$5000,$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:
[Quoted Text] > 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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > 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
> > > > > >
> > > > >
> > > > >
> > > > >
|
|
Rudy
When I re-arranged my data to match your structure, the formula returned
correct values. I'm inclined to think that the problem lies in your data
table.
Try this:
Make a backup copy of the workbook.
Reduce the data list to just 10 rows.
Check the values for anomalies: trailing spaces, false blanks, whatever.
If that doesn't work....try re-typing some values to see if that impacts the
formulas.
Let us know what you discover.
***********
Regards,
Ron
XL2002, WinXP
"Rudy" wrote:
[Quoted Text] > 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$5000,$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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > > 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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
|
|
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:
[Quoted Text] > 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$5000,$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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > > 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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
|
|
Hi Ron
Yes I have them all in Array [ctrl]+[shift]+[enter].
I think I discovered the faults. The data on my worksheet are linked from
anoher worksheet. This way only one formula work.
Then I tried to Copy and Paste values only to the same cells (rather than
linked value) and this way both formulas worked.
Do you think values from linked worksheet will return error values?
Regards,
Rudy
"Ron Coderre" wrote:
[Quoted Text] > Rudy
>
> When I re-arranged my data to match your structure, the formula returned
> correct values. I'm inclined to think that the problem lies in your data
> table.
>
> Try this:
> Make a backup copy of the workbook.
> Reduce the data list to just 10 rows.
> Check the values for anomalies: trailing spaces, false blanks, whatever.
> If that doesn't work....try re-typing some values to see if that impacts the
> formulas.
>
> Let us know what you discover.
>
> ***********
> 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$5000,$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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > > > 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
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
|
|
Do you mean that the values are linked to another WORKBOOK?
If yes, it seems that you are on the right track. You may need to have the
other workbook open. I generally avoid links to other workbooks. In my
experience, they are disasters waiting to happen. I prefer to import the
data from the other workbook.
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Rudy" wrote:
[Quoted Text] > Hi Ron
>
> Yes I have them all in Array [ctrl]+[shift]+[enter].
>
> I think I discovered the faults. The data on my worksheet are linked from
> anoher worksheet. This way only one formula work.
>
> Then I tried to Copy and Paste values only to the same cells (rather than
> linked value) and this way both formulas worked.
>
> Do you think values from linked worksheet will return error values?
>
> Regards,
> Rudy
>
>
>
> "Ron Coderre" wrote:
>
> > Rudy
> >
> > When I re-arranged my data to match your structure, the formula returned
> > correct values. I'm inclined to think that the problem lies in your data
> > table.
> >
> > Try this:
> > Make a backup copy of the workbook.
> > Reduce the data list to just 10 rows.
> > Check the values for anomalies: trailing spaces, false blanks, whatever.
> > If that doesn't work....try re-typing some values to see if that impacts the
> > formulas.
> >
> > Let us know what you discover.
> >
> > ***********
> > 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$5000,$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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
|
|
Hi Ron
Hope you are well and had a great weekend.
Yes, I did commit all the formulas with [ctrl]+[shift]+[enter].
"Ron Coderre" wrote:
[Quoted Text] > 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$5000,$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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > > > 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
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
|
|
HI Ron
I am not very sure what happaned with my data. If they are only 50 rows
those formulas work well. But if I extend them to my original data with
3035rows the formulas go funny, only one formula picks up values.
I also tried another way by importing my data from other workbook to the
same workbook.
I performed the following trials:
I Imported data to the same workbook but different worksheets (I located the
Formulas in one sheet as Summary Sheet and the Data sheet as Data Sheet).
This way ONLY Self-Sufficient formula works, though not accurate.
Hmm.. I wonder why..
Regards,
Rudy
"Ron Coderre" wrote:
[Quoted Text] > Do you mean that the values are linked to another WORKBOOK?
>
> If yes, it seems that you are on the right track. You may need to have the
> other workbook open. I generally avoid links to other workbooks. In my
> experience, they are disasters waiting to happen. I prefer to import the
> data from the other workbook.
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Rudy" wrote:
>
> > Hi Ron
> >
> > Yes I have them all in Array [ctrl]+[shift]+[enter].
> >
> > I think I discovered the faults. The data on my worksheet are linked from
> > anoher worksheet. This way only one formula work.
> >
> > Then I tried to Copy and Paste values only to the same cells (rather than
> > linked value) and this way both formulas worked.
> >
> > Do you think values from linked worksheet will return error values?
> >
> > Regards,
> > Rudy
> >
> >
> >
> > "Ron Coderre" wrote:
> >
> > > Rudy
> > >
> > > When I re-arranged my data to match your structure, the formula returned
> > > correct values. I'm inclined to think that the problem lies in your data
> > > table.
> > >
> > > Try this:
> > > Make a backup copy of the workbook.
> > > Reduce the data list to just 10 rows.
> > > Check the values for anomalies: trailing spaces, false blanks, whatever.
> > > If that doesn't work....try re-typing some values to see if that impacts the
> > > formulas.
> > >
> > > Let us know what you discover.
> > >
> > > ***********
> > > 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$5000,$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" <Rudy[ at ]discussions.microsoft.com> wrote in message
> > > > > > > > > news:304D9859-5EFE-4539-BB71-C89C20485B07[ at ]microsoft.com...
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
|
|
Hi, Rudy
I'm sorry, but I don't get incorrect values using the below ARRAY
FORMULAS....even when I extend my test data down through row 5,000.
B22:
=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$5000,$E$2:$E$5000,0))),ROW($E$2:$E$5000)-ROW($E$2)+1))
C22:
=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))
Consequently, I still think your data may be suspect.
Let us know if you discover any anomolies.
***********
Regards,
Ron
XL2002, WinXP
"Rudy" wrote:
[Quoted Text] > Hi Ron
> Hope you are well and had a great weekend.
>
> Yes, I did commit all the formulas with [ctrl]+[shift]+[enter].
>
>
>
>
>
>
> "Ron Coderre" wrote:
>
> > 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$5000,$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
> > > > > > > > < | | |