| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create totals for close-matching names?
I want to insert a total in column C for names in column A that have the
first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create totals for close-matching names?
=IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMP RODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33))
-- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create totals for close-matching names?
Thanks for your help, Don. Unfortunately I can't get that formula to work.
I tried it, and changed your "G" and "H" columns to "A" and "B." In the sample data I posted, there are a few entries that should result in a 100 total appearing in column C, but the column remains blank when I paste your formula. Beyond that, I think there may be a larger problem. The formula you suggested seems to test for names with the first three characters "abd." My actual data table is thousands of rows, and contains names A through Z. Some names gave once, some twice, and some have given ten or more times. Basically, I just want to know who gave more than 100. What formula would total the contributions for close matches of a given name (say, matching the first 15 characters), wherever that name appears in the alphabet, and no matter how many time that name contributed? Let me know if I've messed up somehow, or if I misunderstood what you were trying to do... t_perkins "Don Guillett" wrote: =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMP RODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create totals for close-matching names?
Thanks much Don, I may take you up on that if I don't have a breakthrough
soon. One thing that confused me about your formula is that there were no names beginning with "abd" in my sample data that matched the specified criteria (i.e., having given more than 100) but there *were* names that began with "abb" or "abe." However, even when I tried entering those character strings in the formula, I still got no results. Can anyone else out there recommend another way to go about this? All suggestions are appreciated. "Don Guillett" wrote: I thought I gave you exactly what you asked for. " I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT So, I gave you one for the first 3 characters "abd" for rows 3:33. Of course, you would need to modify to suit your needs. If all else fails send your workbook to the address below with COMPLETE details and examples of what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks for your help, Don. Unfortunately I can't get that formula to work. I tried it, and changed your "G" and "H" columns to "A" and "B." In the sample data I posted, there are a few entries that should result in a 100 total appearing in column C, but the column remains blank when I paste your formula. Beyond that, I think there may be a larger problem. The formula you suggested seems to test for names with the first three characters "abd." My actual data table is thousands of rows, and contains names A through Z. Some names gave once, some twice, and some have given ten or more times. Basically, I just want to know who gave more than 100. What formula would total the contributions for close matches of a given name (say, matching the first 15 characters), wherever that name appears in the alphabet, and no matter how many time that name contributed? Let me know if I've messed up somehow, or if I misunderstood what you were trying to do... t_perkins "Don Guillett" wrote: =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMP RODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create totals for close-matching names?
ABDO MICHAEL 25
I DID test what I sent and got 190 for Abbott (Dorothy & Kate) and only 25 for abdo so NOT shown. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks much Don, I may take you up on that if I don't have a breakthrough soon. One thing that confused me about your formula is that there were no names beginning with "abd" in my sample data that matched the specified criteria (i.e., having given more than 100) but there *were* names that began with "abb" or "abe." However, even when I tried entering those character strings in the formula, I still got no results. Can anyone else out there recommend another way to go about this? All suggestions are appreciated. "Don Guillett" wrote: I thought I gave you exactly what you asked for. " I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT So, I gave you one for the first 3 characters "abd" for rows 3:33. Of course, you would need to modify to suit your needs. If all else fails send your workbook to the address below with COMPLETE details and examples of what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks for your help, Don. Unfortunately I can't get that formula to work. I tried it, and changed your "G" and "H" columns to "A" and "B." In the sample data I posted, there are a few entries that should result in a 100 total appearing in column C, but the column remains blank when I paste your formula. Beyond that, I think there may be a larger problem. The formula you suggested seems to test for names with the first three characters "abd." My actual data table is thousands of rows, and contains names A through Z. Some names gave once, some twice, and some have given ten or more times. Basically, I just want to know who gave more than 100. What formula would total the contributions for close matches of a given name (say, matching the first 15 characters), wherever that name appears in the alphabet, and no matter how many time that name contributed? Let me know if I've messed up somehow, or if I misunderstood what you were trying to do... t_perkins "Don Guillett" wrote: =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMP RODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create totals for close-matching names?
Thanks, Don. I'll keep working on a solution that will allow me to total
contributions from any and all names that are close matches. "Don Guillett" wrote: ABDO MICHAEL 25 I DID test what I sent and got 190 for Abbott (Dorothy & Kate) and only 25 for abdo so NOT shown. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks much Don, I may take you up on that if I don't have a breakthrough soon. One thing that confused me about your formula is that there were no names beginning with "abd" in my sample data that matched the specified criteria (i.e., having given more than 100) but there *were* names that began with "abb" or "abe." However, even when I tried entering those character strings in the formula, I still got no results. Can anyone else out there recommend another way to go about this? All suggestions are appreciated. "Don Guillett" wrote: I thought I gave you exactly what you asked for. " I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT So, I gave you one for the first 3 characters "abd" for rows 3:33. Of course, you would need to modify to suit your needs. If all else fails send your workbook to the address below with COMPLETE details and examples of what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks for your help, Don. Unfortunately I can't get that formula to work. I tried it, and changed your "G" and "H" columns to "A" and "B." In the sample data I posted, there are a few entries that should result in a 100 total appearing in column C, but the column remains blank when I paste your formula. Beyond that, I think there may be a larger problem. The formula you suggested seems to test for names with the first three characters "abd." My actual data table is thousands of rows, and contains names A through Z. Some names gave once, some twice, and some have given ten or more times. Basically, I just want to know who gave more than 100. What formula would total the contributions for close matches of a given name (say, matching the first 15 characters), wherever that name appears in the alphabet, and no matter how many time that name contributed? Let me know if I've messed up somehow, or if I misunderstood what you were trying to do... t_perkins "Don Guillett" wrote: =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMP RODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create totals for close-matching names?
let us know you final result
-- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks, Don. I'll keep working on a solution that will allow me to total contributions from any and all names that are close matches. "Don Guillett" wrote: ABDO MICHAEL 25 I DID test what I sent and got 190 for Abbott (Dorothy & Kate) and only 25 for abdo so NOT shown. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks much Don, I may take you up on that if I don't have a breakthrough soon. One thing that confused me about your formula is that there were no names beginning with "abd" in my sample data that matched the specified criteria (i.e., having given more than 100) but there *were* names that began with "abb" or "abe." However, even when I tried entering those character strings in the formula, I still got no results. Can anyone else out there recommend another way to go about this? All suggestions are appreciated. "Don Guillett" wrote: I thought I gave you exactly what you asked for. " I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT So, I gave you one for the first 3 characters "abd" for rows 3:33. Of course, you would need to modify to suit your needs. If all else fails send your workbook to the address below with COMPLETE details and examples of what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Thanks for your help, Don. Unfortunately I can't get that formula to work. I tried it, and changed your "G" and "H" columns to "A" and "B." In the sample data I posted, there are a few entries that should result in a 100 total appearing in column C, but the column remains blank when I paste your formula. Beyond that, I think there may be a larger problem. The formula you suggested seems to test for names with the first three characters "abd." My actual data table is thousands of rows, and contains names A through Z. Some names gave once, some twice, and some have given ten or more times. Basically, I just want to know who gave more than 100. What formula would total the contributions for close matches of a given name (say, matching the first 15 characters), wherever that name appears in the alphabet, and no matter how many time that name contributed? Let me know if I've messed up somehow, or if I misunderstood what you were trying to do... t_perkins "Don Guillett" wrote: =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMP RODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching names | Excel Discussion (Misc queries) | |||
| Matching names in two columns | Excel Discussion (Misc queries) | |||
| Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
| Matching Names in two different workbooks | Excel Discussion (Misc queries) | |||
| Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions | |||
Linear Mode
