Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to add values for all the same first words in cells in a r
I have the range of data like the following:
Description Column A Column B UAE Mobile 21 UAE 22 Bangladish-Sylhet (2) 33 Bangladish Mob 26 Bangladish Mob (1) 15 Bangladish Mob (2) 29 Bangladish Mob (3) 98 I want the totals of values in column B for the similar values in column A to be added up. Now I need different stages of totals: 1- I need totals for all the values in column A where the first word is the same. For example Bangladesh. But Bangladesh is separated from the remaining text in some cases by a "space" and in some cases by "-". 2- Second I need subtotal for all the values in column A where the first two words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In these cases the words are separated in the first instance by a "space" and in the second one by the "-". 3- I want further subtotal where the first three words of the value in column A are the same and so on and so on......... Ny help URGENTLY! Thanks SAM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to add values for all the same first words in cells in a r
Assuming your data in A1:B100
Criterias in: C1: holds Bangladish C2: holds Bangladish Mob and so on.... =SUMPRODUCT(--(LEFT($A$1:$A$100,LEN(C1))=C1),$B$1:$B$100) copy down "SAM" wrote: I have the range of data like the following: Description Column A Column B UAE Mobile 21 UAE 22 Bangladish-Sylhet (2) 33 Bangladish Mob 26 Bangladish Mob (1) 15 Bangladish Mob (2) 29 Bangladish Mob (3) 98 I want the totals of values in column B for the similar values in column A to be added up. Now I need different stages of totals: 1- I need totals for all the values in column A where the first word is the same. For example Bangladesh. But Bangladesh is separated from the remaining text in some cases by a "space" and in some cases by "-". 2- Second I need subtotal for all the values in column A where the first two words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In these cases the words are separated in the first instance by a "space" and in the second one by the "-". 3- I want further subtotal where the first three words of the value in column A are the same and so on and so on......... Ny help URGENTLY! Thanks SAM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to add values for all the same first words in cells in
Well thanks. It does seem to work but was just wondering if I can have a few
more filtering done to this formula for it to be more effective because the amount of data that I have is just immense. What I understand is that I have to put the characters in C1, C2, C3....... and the formula will match these chracters with the range mentioned and put a total infront of it. I can probably have a predetermined sort of list ready in a separate sheet and can apply that formula infornt of the list of those countries. But the problem is that I need to do this exercise on a weekly basis and every week there might be scenarios like the following whereby the data may differ from week to week: 1- Different countires names might appear in one week when those names were not in the list in the previous week. 2- Countires names are repeated more than once. An extract is given below: Bangladish (1) Bangladish (1) Bangladish (2) Bangladish (2) Bangladish (3) Bangladish (3) Bangladish-Chttagong (1) Bangladish-Chttagong (1) Bangladish-Chttagong (2) Bangladish-Chttagong (2) Bangladish-Chttagong (3) So can I have the formulas which can do the following for me: 1- Formula which can compare the range of the countries in the current week with the previous week and list down the new countries list 2- A formula which can refine the list and remove duplicated names. Using the results of the above formulas, I can come up with a refined list every week, whereby I can add the new countries (if any) week after week. Infront of this refined list I can then apply your original formula and get the magical results :) Thanks "Teethless mama" wrote: Assuming your data in A1:B100 Criterias in: C1: holds Bangladish C2: holds Bangladish Mob and so on.... =SUMPRODUCT(--(LEFT($A$1:$A$100,LEN(C1))=C1),$B$1:$B$100) copy down "SAM" wrote: I have the range of data like the following: Description Column A Column B UAE Mobile 21 UAE 22 Bangladish-Sylhet (2) 33 Bangladish Mob 26 Bangladish Mob (1) 15 Bangladish Mob (2) 29 Bangladish Mob (3) 98 I want the totals of values in column B for the similar values in column A to be added up. Now I need different stages of totals: 1- I need totals for all the values in column A where the first word is the same. For example Bangladesh. But Bangladesh is separated from the remaining text in some cases by a "space" and in some cases by "-". 2- Second I need subtotal for all the values in column A where the first two words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In these cases the words are separated in the first instance by a "space" and in the second one by the "-". 3- I want further subtotal where the first three words of the value in column A are the same and so on and so on......... Ny help URGENTLY! Thanks SAM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to add values for all the same first words in cells in
I believe the easiest solution is for you to use "Auto Filter"!
Check it out in the Help files, and post back with any questions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SAM" wrote in message ... Well thanks. It does seem to work but was just wondering if I can have a few more filtering done to this formula for it to be more effective because the amount of data that I have is just immense. What I understand is that I have to put the characters in C1, C2, C3....... and the formula will match these chracters with the range mentioned and put a total infront of it. I can probably have a predetermined sort of list ready in a separate sheet and can apply that formula infornt of the list of those countries. But the problem is that I need to do this exercise on a weekly basis and every week there might be scenarios like the following whereby the data may differ from week to week: 1- Different countires names might appear in one week when those names were not in the list in the previous week. 2- Countires names are repeated more than once. An extract is given below: Bangladish (1) Bangladish (1) Bangladish (2) Bangladish (2) Bangladish (3) Bangladish (3) Bangladish-Chttagong (1) Bangladish-Chttagong (1) Bangladish-Chttagong (2) Bangladish-Chttagong (2) Bangladish-Chttagong (3) So can I have the formulas which can do the following for me: 1- Formula which can compare the range of the countries in the current week with the previous week and list down the new countries list 2- A formula which can refine the list and remove duplicated names. Using the results of the above formulas, I can come up with a refined list every week, whereby I can add the new countries (if any) week after week. Infront of this refined list I can then apply your original formula and get the magical results :) Thanks "Teethless mama" wrote: Assuming your data in A1:B100 Criterias in: C1: holds Bangladish C2: holds Bangladish Mob and so on.... =SUMPRODUCT(--(LEFT($A$1:$A$100,LEN(C1))=C1),$B$1:$B$100) copy down "SAM" wrote: I have the range of data like the following: Description Column A Column B UAE Mobile 21 UAE 22 Bangladish-Sylhet (2) 33 Bangladish Mob 26 Bangladish Mob (1) 15 Bangladish Mob (2) 29 Bangladish Mob (3) 98 I want the totals of values in column B for the similar values in column A to be added up. Now I need different stages of totals: 1- I need totals for all the values in column A where the first word is the same. For example Bangladesh. But Bangladesh is separated from the remaining text in some cases by a "space" and in some cases by "-". 2- Second I need subtotal for all the values in column A where the first two words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In these cases the words are separated in the first instance by a "space" and in the second one by the "-". 3- I want further subtotal where the first three words of the value in column A are the same and so on and so on......... Ny help URGENTLY! Thanks SAM |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to add values for all the same first words in cells in
Well I have tried using the auto filter but it requires lot of manual
intervention to find the totals for all the countries, while the using the forlula provided by "teethless mama" gives me the totals just by copying the formula infront of the names list. Do you think macro can help in this thing? "Ragdyer" wrote: I believe the easiest solution is for you to use "Auto Filter"! Check it out in the Help files, and post back with any questions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SAM" wrote in message ... Well thanks. It does seem to work but was just wondering if I can have a few more filtering done to this formula for it to be more effective because the amount of data that I have is just immense. What I understand is that I have to put the characters in C1, C2, C3....... and the formula will match these chracters with the range mentioned and put a total infront of it. I can probably have a predetermined sort of list ready in a separate sheet and can apply that formula infornt of the list of those countries. But the problem is that I need to do this exercise on a weekly basis and every week there might be scenarios like the following whereby the data may differ from week to week: 1- Different countires names might appear in one week when those names were not in the list in the previous week. 2- Countires names are repeated more than once. An extract is given below: Bangladish (1) Bangladish (1) Bangladish (2) Bangladish (2) Bangladish (3) Bangladish (3) Bangladish-Chttagong (1) Bangladish-Chttagong (1) Bangladish-Chttagong (2) Bangladish-Chttagong (2) Bangladish-Chttagong (3) So can I have the formulas which can do the following for me: 1- Formula which can compare the range of the countries in the current week with the previous week and list down the new countries list 2- A formula which can refine the list and remove duplicated names. Using the results of the above formulas, I can come up with a refined list every week, whereby I can add the new countries (if any) week after week. Infront of this refined list I can then apply your original formula and get the magical results :) Thanks "Teethless mama" wrote: Assuming your data in A1:B100 Criterias in: C1: holds Bangladish C2: holds Bangladish Mob and so on.... =SUMPRODUCT(--(LEFT($A$1:$A$100,LEN(C1))=C1),$B$1:$B$100) copy down "SAM" wrote: I have the range of data like the following: Description Column A Column B UAE Mobile 21 UAE 22 Bangladish-Sylhet (2) 33 Bangladish Mob 26 Bangladish Mob (1) 15 Bangladish Mob (2) 29 Bangladish Mob (3) 98 I want the totals of values in column B for the similar values in column A to be added up. Now I need different stages of totals: 1- I need totals for all the values in column A where the first word is the same. For example Bangladesh. But Bangladesh is separated from the remaining text in some cases by a "space" and in some cases by "-". 2- Second I need subtotal for all the values in column A where the first two words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In these cases the words are separated in the first instance by a "space" and in the second one by the "-". 3- I want further subtotal where the first three words of the value in column A are the same and so on and so on......... Ny help URGENTLY! Thanks SAM |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to add values for all the same first words in cells in
There are a lot of things you are looking for and I am only visiting
the NG presently for a short time, so I might not be around for the followup... One thing that can help you is to identify the delimiter points. I.e. when the first word ends, when the second one ends etc. The following *array* formula will find the position of the first non- letter (i.e. delimiter, be it space, dash or whatever). In B1: =MIN(IF(ISERROR(SEARCH(MID(A1,ROW($1:$100), 1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),ROW($1:$100))) In C1 (and copied to D1): =MIN(IF(ISERROR(SEARCH(MID($A1,ROW($1:$100), 1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),B1+1),ROW($1:$100 ))) Now you have these delimiters (although for items like Bangladesh (1) we might need a different strategy). You can use them in the following manner: =LEFT(A1,B1-1) I know it is not a complete solution but it might be a start. HTH Kostis Vezerides On Jun 17, 4:52 pm, SAM wrote: Well thanks. It does seem to work but was just wondering if I can have a few more filtering done to this formula for it to be more effective because the amount of data that I have is just immense. What I understand is that I have to put the characters in C1, C2, C3....... and the formula will match these chracters with the range mentioned and put a total infront of it. I can probably have a predetermined sort of list ready in a separate sheet and can apply that formula infornt of the list of those countries. But the problem is that I need to do this exercise on a weekly basis and every week there might be scenarios like the following whereby the data may differ from week to week: 1- Different countires names might appear in one week when those names were not in the list in the previous week. 2- Countires names are repeated more than once. An extract is given below: Bangladish (1) Bangladish (1) Bangladish (2) Bangladish (2) Bangladish (3) Bangladish (3) Bangladish-Chttagong (1) Bangladish-Chttagong (1) Bangladish-Chttagong (2) Bangladish-Chttagong (2) Bangladish-Chttagong (3) So can I have the formulas which can do the following for me: 1- Formula which can compare the range of the countries in the current week with the previous week and list down the new countries list 2- A formula which can refine the list and remove duplicated names. Using the results of the above formulas, I can come up with a refined list every week, whereby I can add the new countries (if any) week after week. Infront of this refined list I can then apply your original formula and get the magical results :) Thanks "Teethless mama" wrote: Assuming your data in A1:B100 Criterias in: C1: holds Bangladish C2: holds Bangladish Mob and so on.... =SUMPRODUCT(--(LEFT($A$1:$A$100,LEN(C1))=C1),$B$1:$B$100) copy down "SAM" wrote: I have the range of data like the following: Description Column A Column B UAE Mobile 21 UAE 22 Bangladish-Sylhet (2) 33 Bangladish Mob 26 Bangladish Mob (1) 15 Bangladish Mob (2) 29 Bangladish Mob (3) 98 I want the totals of values in column B for the similar values in column A to be added up. Now I need different stages of totals: 1- I need totals for all the values in column A where the first word is the same. For example Bangladesh. But Bangladesh is separated from the remaining text in some cases by a "space" and in some cases by "-". 2- Second I need subtotal for all the values in column A where the first two words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In these cases the words are separated in the first instance by a "space" and in the second one by the "-". 3- I want further subtotal where the first three words of the value in column A are the same and so on and so on......... Ny help URGENTLY! Thanks SAM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert values in figures to words in EXCEL 2000? | Excel Worksheet Functions | |||
Assigning values to words | Excel Discussion (Misc queries) | |||
Why do my cell values/words duplicate themselves over and over? | Excel Discussion (Misc queries) | |||
How do I sum cells that have values from a countif formula? | Excel Worksheet Functions | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions |