|
Eliminate manual typing
I receive a report with a 1 or 2 alpha code/description in col A., such as:
A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Thanks for your reply.
Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
My manager has identified services/fees into 7 categories. No logic that I
can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
No logic that I can see. The information following the two letters are
basically the same too, either services or fees. The only difference in the whole report is the two letter code and the number my manger has assigned. This is only a quarterly report so I can continue to manually. Seems like just as much work, but can I put the letters in a separate column "b" then in cell a2 use an IF? IF(B2=ab or c or em, then put 2 in cell a2) Thanks to you both "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
I set all the letters down Column G and the corresponding codes in Column H.
Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Thanks you sooo much for your time. I will try this today and post back.
"Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
IT WORKS! (Now I just have to learn why)
Thanks again for your time. God bless! "Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Glad that you got it to work. If XL Help is not enough Debra has a good
explanation he http://www.contextures.com/xlFunctions02.html#Arguments Thanks for taking the time to post back. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... IT WORKS! (Now I just have to learn why) Thanks again for your time. God bless! "Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Sandy - I think I finally understand how this works but I have a question
about trim. I noticed in my file that some entries have more than one space after the initial letter code(s). Am I correct that with trim 2 characters, "AB Service" will give me AB but will "A Service" give me "A " (A with a space)? And conversely if my table array has an extra space it will affect matching the column. Thanks again "Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Think i answered my own question - by playing with data in a file & found
that the space(s) do make a difference. So this is why I recd some #N/A's - it's great to understand. Also I was also reading other posts and found one that spoke of "text to columns" so I learned something else. Two things in one day! - doesn't get much better! "dp" wrote: Sandy - I think I finally understand how this works but I have a question about trim. I noticed in my file that some entries have more than one space after the initial letter code(s). Am I correct that with trim 2 characters, "AB Service" will give me AB but will "A Service" give me "A " (A with a space)? And conversely if my table array has an extra space it will affect matching the column. Thanks again "Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Yes. From Help:
"Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing." With "<space<spaceA <space <space <space Service<space<space" (without the quotes) in A1, =LEN(A1) returns 15 but: =LEN(TRIM(A1)) Returns 9 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Sandy - I think I finally understand how this works but I have a question about trim. I noticed in my file that some entries have more than one space after the initial letter code(s). Am I correct that with trim 2 characters, "AB Service" will give me AB but will "A Service" give me "A " (A with a space)? And conversely if my table array has an extra space it will affect matching the column. Thanks again "Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
Eliminate manual typing
Question on this same thread
Can I combine these two formulas below, or best to run each separately? in cell a2 =trim(left(b2,2)) in cell c2 =vlookup(a2,$F$2:$G$62,2,false) TIA "Sandy Mann" wrote: Yes. From Help: "Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing." With "<space<spaceA <space <space <space Service<space<space" (without the quotes) in A1, =LEN(A1) returns 15 but: =LEN(TRIM(A1)) Returns 9 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Sandy - I think I finally understand how this works but I have a question about trim. I noticed in my file that some entries have more than one space after the initial letter code(s). Am I correct that with trim 2 characters, "AB Service" will give me AB but will "A Service" give me "A " (A with a space)? And conversely if my table array has an extra space it will affect matching the column. Thanks again "Sandy Mann" wrote: I set all the letters down Column G and the corresponding codes in Column H. Then, assuming that the letters are in Column A by themselves, and do not have leading/trailing spaces I used the formula: =VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code. If the letters do/could have spaces around them then try: =VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE) If Services/fees are in the same cell as the letters then, (assuming that there is no leading space), try: =VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE) If the letters and Services/fees are in the same cell with a leading space then try: =VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E) Note that the range down to row 133 is much bigger then the codes will need so that when your manager invents more codes you can just addthen a the bottom of all the other codes. Also the codes do not have to be in any order, the formula will still find them. If none of the above work then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... My manager has identified services/fees into 7 categories. No logic that I can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number 2. In fact there are 12 letter combinations that = 2. I will only receive this report quarterly so I could continue to add the numbers manually. Thanks "Sandy Mann" wrote: What is the logic behind how the 1 - 7 codes are entered? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "dp" wrote in message ... Thanks for your reply. Tried what you said but the problem I have is that all the "A's" are not #1. A, B = #1 AB, C = #2 Thanks again "Sherin" wrote: A simple but practical solution would be to:- a. Type 1 and 2 in first two rows b. Block the above two cells c. Double click on the small box appearing in the right hand corner in the bottom part of the selection. Next option would be to:- a. Type 1 in the first row (say in A2) b. Type the formula A2+1 in the next row (i.e. in A3) c. Copy the cell A3 d. Block and select the remaining cells and paste Hope it helps. Sherin "dp" wrote: I receive a report with a 1 or 2 alpha code/description in col A., such as: A SERVICE AB SERVICE AL FEE AM SERVICE B SERVICE C SERVICE DI FEE I insert a column and manually type a single digit code (1 to 7) so I can use Sort by that digit, and use Data, Subtotal. 1 A SERVICE 2 AB SERVICE 6 AL FEE 6 AM SERVICE 1 B SERVICE 2 C SERVICE 5 DI FEE Is there a way to eliminate the manual typing of the numbers 1-7? There are about 70 rows. I am Using excel 2003, Windows 2000 Professional Hope I made this clear. Just learning. TIA |
| All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com