| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Scenario of Text value
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 dont 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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<"")*ISNU MBER(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: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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$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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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$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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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$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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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$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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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: 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$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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Scenario of Text value
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$5 000,$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: 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$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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting of text in a shared workbook. | Excel Worksheet Functions | |||
| grayscale conditional formatting of text | Excel Discussion (Misc queries) | |||
| conditional formatting on specific text in cells | Excel Discussion (Misc queries) | |||
| Sort or Filter option? | Excel Worksheet Functions | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
