|
convert a number to text
I have exported data from an employee database that lists the employees by
an ID number. I'd like to replace that number with the employee's name programatically. Also I'd like to replace the category column with a number between 1 and 5 Employee Category 59 Administrative\Purchase Order Request 223 Server\Exchange .... would become Employee Category Smith, Joe 5 Davis, Mike 3 I know that there is a Microsoft code sample out there for converting monetary amounts ($3.43) to how they are spelled out "three dollars and forty three cents". I need something kinda similar to that. Much obliged for anyone who can point me in the right direction. Thanks, JM |
convert a number to text
Take a look a the VLOOKUP function of Excel, you could store a conversion
table say on one sheet and use VLOOKUP to get the string for each numerical code. -- Cheers Nigel "Joe Murphy" wrote in message ... I have exported data from an employee database that lists the employees by an ID number. I'd like to replace that number with the employee's name programatically. Also I'd like to replace the category column with a number between 1 and 5 Employee Category 59 Administrative\Purchase Order Request 223 Server\Exchange ... would become Employee Category Smith, Joe 5 Davis, Mike 3 I know that there is a Microsoft code sample out there for converting monetary amounts ($3.43) to how they are spelled out "three dollars and forty three cents". I need something kinda similar to that. Much obliged for anyone who can point me in the right direction. Thanks, JM |
convert a number to text
Took me a while to grasp VLOOKUP, but I did finally figure it out. Thanks,
Nigel. I have another question now. I'd like to take the resulting data I created and sum it based on the employee name. Example: Smith 2 Gosselin 5 Gosselin 1 Gosselin 1 Smith 4 Smith 4 Lavoie 4 Lavoie 4 Rebinskas 5 Rebinskas 5 Collard 4 Collard 4 I'd like to automatically sum all the points for Smith, Gosselin, etc.... in a given array. Results would be Smith: 10 Gosselin: 7 Lavoie: 8 Rebinskas10 Collard: 8 Is there a function that can do this, or is at a VB Script thing? Thanks, JM "Nigel" wrote in message ... Take a look a the VLOOKUP function of Excel, you could store a conversion table say on one sheet and use VLOOKUP to get the string for each numerical code. -- Cheers Nigel "Joe Murphy" wrote in message ... I have exported data from an employee database that lists the employees by an ID number. I'd like to replace that number with the employee's name programatically. Also I'd like to replace the category column with a number between 1 and 5 Employee Category 59 Administrative\Purchase Order Request 223 Server\Exchange ... would become Employee Category Smith, Joe 5 Davis, Mike 3 I know that there is a Microsoft code sample out there for converting monetary amounts ($3.43) to how they are spelled out "three dollars and forty three cents". I need something kinda similar to that. Much obliged for anyone who can point me in the right direction. Thanks, JM |
convert a number to text
If your data is sorted, add headers (if you don't have them) and then select
your range and do: Data|Subtotals (Not Data|SubFilters (Hi, Jason!)). Then you can hide the details using the outlining symbols at the left. If your data is not sorted (well, it can be sorted, too!), Add headers Select your range Data|Pivottable... follow the wizard until you get to a step that has a "Layout" button on it. Hit that Layout button. Drag the header for column A to the Row field drag the header for column B to the Data field If you don't see "SUM OF" in that data field, then double click on it and make choose "Sum of" Finish up the wizard. If you want to read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Joe Murphy wrote: Took me a while to grasp VLOOKUP, but I did finally figure it out. Thanks, Nigel. I have another question now. I'd like to take the resulting data I created and sum it based on the employee name. Example: Smith 2 Gosselin 5 Gosselin 1 Gosselin 1 Smith 4 Smith 4 Lavoie 4 Lavoie 4 Rebinskas 5 Rebinskas 5 Collard 4 Collard 4 I'd like to automatically sum all the points for Smith, Gosselin, etc.... in a given array. Results would be Smith: 10 Gosselin: 7 Lavoie: 8 Rebinskas10 Collard: 8 Is there a function that can do this, or is at a VB Script thing? Thanks, JM "Nigel" wrote in message ... Take a look a the VLOOKUP function of Excel, you could store a conversion table say on one sheet and use VLOOKUP to get the string for each numerical code. -- Cheers Nigel "Joe Murphy" wrote in message ... I have exported data from an employee database that lists the employees by an ID number. I'd like to replace that number with the employee's name programatically. Also I'd like to replace the category column with a number between 1 and 5 Employee Category 59 Administrative\Purchase Order Request 223 Server\Exchange ... would become Employee Category Smith, Joe 5 Davis, Mike 3 I know that there is a Microsoft code sample out there for converting monetary amounts ($3.43) to how they are spelled out "three dollars and forty three cents". I need something kinda similar to that. Much obliged for anyone who can point me in the right direction. Thanks, JM -- Dave Peterson |
convert a number to text
=SUMIF($A$2:$A$1501,"Smith",$B$2:$B$1501)
This did the trick too. Thanks Dave! "Dave Peterson" wrote in message ... If your data is sorted, add headers (if you don't have them) and then select your range and do: Data|Subtotals (Not Data|SubFilters (Hi, Jason!)). Then you can hide the details using the outlining symbols at the left. If your data is not sorted (well, it can be sorted, too!), Add headers Select your range Data|Pivottable... follow the wizard until you get to a step that has a "Layout" button on it. Hit that Layout button. Drag the header for column A to the Row field drag the header for column B to the Data field If you don't see "SUM OF" in that data field, then double click on it and make choose "Sum of" Finish up the wizard. If you want to read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Joe Murphy wrote: Took me a while to grasp VLOOKUP, but I did finally figure it out. Thanks, Nigel. I have another question now. I'd like to take the resulting data I created and sum it based on the employee name. Example: Smith 2 Gosselin 5 Gosselin 1 Gosselin 1 Smith 4 Smith 4 Lavoie 4 Lavoie 4 Rebinskas 5 Rebinskas 5 Collard 4 Collard 4 I'd like to automatically sum all the points for Smith, Gosselin, etc.... in a given array. Results would be Smith: 10 Gosselin: 7 Lavoie: 8 Rebinskas10 Collard: 8 Is there a function that can do this, or is at a VB Script thing? Thanks, JM "Nigel" wrote in message ... Take a look a the VLOOKUP function of Excel, you could store a conversion table say on one sheet and use VLOOKUP to get the string for each numerical code. -- Cheers Nigel "Joe Murphy" wrote in message ... I have exported data from an employee database that lists the employees by an ID number. I'd like to replace that number with the employee's name programatically. Also I'd like to replace the category column with a number between 1 and 5 Employee Category 59 Administrative\Purchase Order Request 223 Server\Exchange ... would become Employee Category Smith, Joe 5 Davis, Mike 3 I know that there is a Microsoft code sample out there for converting monetary amounts ($3.43) to how they are spelled out "three dollars and forty three cents". I need something kinda similar to that. Much obliged for anyone who can point me in the right direction. Thanks, JM -- Dave Peterson |
convert a number to text
But then you have to have a formula for each person. And if the list changes,
it can become a pain to keep up. But if you like that formula, you could build a list of unique names by following some of the techniques at Debra Dalgleish's site: http://www.contextures.com/xladvfilter01.html#FilterUR Then you could use a formula like this in B2 (of a different sheet): =SUMIF(sheet1!$A$2:$A$1501,A2,sheet1!$B$2:$B$1501) and drag down Depending on how often your data changes (and how many are in that list), it might even be worth doing! Joe Murphy wrote: =SUMIF($A$2:$A$1501,"Smith",$B$2:$B$1501) This did the trick too. Thanks Dave! "Dave Peterson" wrote in message ... If your data is sorted, add headers (if you don't have them) and then select your range and do: Data|Subtotals (Not Data|SubFilters (Hi, Jason!)). Then you can hide the details using the outlining symbols at the left. If your data is not sorted (well, it can be sorted, too!), Add headers Select your range Data|Pivottable... follow the wizard until you get to a step that has a "Layout" button on it. Hit that Layout button. Drag the header for column A to the Row field drag the header for column B to the Data field If you don't see "SUM OF" in that data field, then double click on it and make choose "Sum of" Finish up the wizard. If you want to read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Joe Murphy wrote: Took me a while to grasp VLOOKUP, but I did finally figure it out. Thanks, Nigel. I have another question now. I'd like to take the resulting data I created and sum it based on the employee name. Example: Smith 2 Gosselin 5 Gosselin 1 Gosselin 1 Smith 4 Smith 4 Lavoie 4 Lavoie 4 Rebinskas 5 Rebinskas 5 Collard 4 Collard 4 I'd like to automatically sum all the points for Smith, Gosselin, etc.... in a given array. Results would be Smith: 10 Gosselin: 7 Lavoie: 8 Rebinskas10 Collard: 8 Is there a function that can do this, or is at a VB Script thing? Thanks, JM "Nigel" wrote in message ... Take a look a the VLOOKUP function of Excel, you could store a conversion table say on one sheet and use VLOOKUP to get the string for each numerical code. -- Cheers Nigel "Joe Murphy" wrote in message ... I have exported data from an employee database that lists the employees by an ID number. I'd like to replace that number with the employee's name programatically. Also I'd like to replace the category column with a number between 1 and 5 Employee Category 59 Administrative\Purchase Order Request 223 Server\Exchange ... would become Employee Category Smith, Joe 5 Davis, Mike 3 I know that there is a Microsoft code sample out there for converting monetary amounts ($3.43) to how they are spelled out "three dollars and forty three cents". I need something kinda similar to that. Much obliged for anyone who can point me in the right direction. Thanks, JM -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 06:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com