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

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

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

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

=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

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