I am trying to convert myself from an Access VBA to Excel VBA. I
appreciate it if I can get some help with this. This is my data:

salesman amount date product
279 8965 6/27/2005 A
279 78515 6/27/2005 B
774 63903 7/4/2005 A
774 61027 6/27/2005 A
774 60624 6/20/2005 A
774 60103 6/13/2005 A
774 56807 6/6/2005 A
774 57618 5/30/2005 A
774 58433 5/23/2005 A
774 59119 5/16/2005 A
774 60971 5/9/2005 A
774 62601 5/2/2005 A
774 65245 4/25/2005 A
774 64662 4/18/2005 A
774 56342 4/11/2005 A
774 332566 7/4/2005 B
774 324033 6/27/2005 B
774 324078 6/20/2005 B
774 323889 6/13/2005 B
774 329421 6/6/2005 B
774 321219 5/30/2005 B
774 319549 5/23/2005 B
774 321294 5/16/2005 B
774 322247 5/9/2005 B
774 318629 5/2/2005 B
774 314424 4/25/2005 B
774 318827 4/18/2005 B
774 320495 4/11/2005 B
1098 22969 6/27/2005 A
1098 122443 6/27/2005 B
1669 75769 7/4/2005 A
1669 74577 6/27/2005 A
1669 75822 6/20/2005 A
1669 78669 6/13/2005 A
1669 76823 6/6/2005 A
1669 75022 5/30/2005 A
1669 74109 5/23/2005 A
1669 73381 5/16/2005 A
1669 75301 5/9/2005 A
1669 72788 5/2/2005 A
1669 72541 4/25/2005 A
1669 75504 4/18/2005 A
1669 75605 4/11/2005 A
1669 182880 7/4/2005 B
1669 181325 6/27/2005 B
1669 182537 6/20/2005 B
1669 186734 6/13/2005 B
1669 185827 6/6/2005 B
1669 182972 5/30/2005 B
1669 181364 5/23/2005 B
1669 182485 5/16/2005 B
1669 182727 5/9/2005 B
1669 181576 5/2/2005 B
1669 180550 4/25/2005 B
1669 183157 4/18/2005 B
1669 183729 4/11/2005 B
..
..
..

I would like to extract the data and transpose it as this:

sales/
man A B
279 8965 78515
774 65245 332566
1098 22969 122443
1669 78669 186734
..
..
..

Each salesman has only one row of record, which is the maximum of
Product A and Product B. I know I have to loop through salesman and
then maximum of A and B. I am not sure how to do this in Excel. Thanks
for any input.

Why use code? This is easily done with a pivot table. Not to mention you can
attach the pivot table directly to the Access Database allowing for easy
refreshes.

Select Data - Pivot Table
Follow the wizard attaching either to the Access db or to the data that you
have imported into Excel.

Place the Salesman in the left column and the product on the top row. Add
the amounts to the center data section. Right click on the amounts and select
Field Settings. Change from Sum to Max and... Bob's your uncle.
--
HTH...

Jim Thomlinson


"Faye" wrote:

I am trying to convert myself from an Access VBA to Excel VBA. I
appreciate it if I can get some help with this. This is my data:

salesman amount date product
279 8965 6/27/2005 A
279 78515 6/27/2005 B
774 63903 7/4/2005 A
774 61027 6/27/2005 A
774 60624 6/20/2005 A
774 60103 6/13/2005 A
774 56807 6/6/2005 A
774 57618 5/30/2005 A
774 58433 5/23/2005 A
774 59119 5/16/2005 A
774 60971 5/9/2005 A
774 62601 5/2/2005 A
774 65245 4/25/2005 A
774 64662 4/18/2005 A
774 56342 4/11/2005 A
774 332566 7/4/2005 B
774 324033 6/27/2005 B
774 324078 6/20/2005 B
774 323889 6/13/2005 B
774 329421 6/6/2005 B
774 321219 5/30/2005 B
774 319549 5/23/2005 B
774 321294 5/16/2005 B
774 322247 5/9/2005 B
774 318629 5/2/2005 B
774 314424 4/25/2005 B
774 318827 4/18/2005 B
774 320495 4/11/2005 B
1098 22969 6/27/2005 A
1098 122443 6/27/2005 B
1669 75769 7/4/2005 A
1669 74577 6/27/2005 A
1669 75822 6/20/2005 A
1669 78669 6/13/2005 A
1669 76823 6/6/2005 A
1669 75022 5/30/2005 A
1669 74109 5/23/2005 A
1669 73381 5/16/2005 A
1669 75301 5/9/2005 A
1669 72788 5/2/2005 A
1669 72541 4/25/2005 A
1669 75504 4/18/2005 A
1669 75605 4/11/2005 A
1669 182880 7/4/2005 B
1669 181325 6/27/2005 B
1669 182537 6/20/2005 B
1669 186734 6/13/2005 B
1669 185827 6/6/2005 B
1669 182972 5/30/2005 B
1669 181364 5/23/2005 B
1669 182485 5/16/2005 B
1669 182727 5/9/2005 B
1669 181576 5/2/2005 B
1669 180550 4/25/2005 B
1669 183157 4/18/2005 B
1669 183729 4/11/2005 B
..
..
..

I would like to extract the data and transpose it as this:

sales/
man A B
279 8965 78515
774 65245 332566
1098 22969 122443
1669 78669 186734
..
..
..

Each salesman has only one row of record, which is the maximum of
Product A and Product B. I know I have to loop through salesman and
then maximum of A and B. I am not sure how to do this in Excel. Thanks
for any input.

Create a Pivot table based on your data.
Salesman, Date as Row fields (or vice-versa, not clear what you want on that
point)
Product as Column field
(Sum of) Amount as Data field

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Faye" wrote in message
ups.com...
I am trying to convert myself from an Access VBA to Excel VBA. I
appreciate it if I can get some help with this. This is my data:

salesman amount date product
279 8965 6/27/2005 A
279 78515 6/27/2005 B
774 63903 7/4/2005 A
774 61027 6/27/2005 A
774 60624 6/20/2005 A
774 60103 6/13/2005 A
774 56807 6/6/2005 A
774 57618 5/30/2005 A
774 58433 5/23/2005 A
774 59119 5/16/2005 A
774 60971 5/9/2005 A
774 62601 5/2/2005 A
774 65245 4/25/2005 A
774 64662 4/18/2005 A
774 56342 4/11/2005 A
774 332566 7/4/2005 B
774 324033 6/27/2005 B
774 324078 6/20/2005 B
774 323889 6/13/2005 B
774 329421 6/6/2005 B
774 321219 5/30/2005 B
774 319549 5/23/2005 B
774 321294 5/16/2005 B
774 322247 5/9/2005 B
774 318629 5/2/2005 B
774 314424 4/25/2005 B
774 318827 4/18/2005 B
774 320495 4/11/2005 B
1098 22969 6/27/2005 A
1098 122443 6/27/2005 B
1669 75769 7/4/2005 A
1669 74577 6/27/2005 A
1669 75822 6/20/2005 A
1669 78669 6/13/2005 A
1669 76823 6/6/2005 A
1669 75022 5/30/2005 A
1669 74109 5/23/2005 A
1669 73381 5/16/2005 A
1669 75301 5/9/2005 A
1669 72788 5/2/2005 A
1669 72541 4/25/2005 A
1669 75504 4/18/2005 A
1669 75605 4/11/2005 A
1669 182880 7/4/2005 B
1669 181325 6/27/2005 B
1669 182537 6/20/2005 B
1669 186734 6/13/2005 B
1669 185827 6/6/2005 B
1669 182972 5/30/2005 B
1669 181364 5/23/2005 B
1669 182485 5/16/2005 B
1669 182727 5/9/2005 B
1669 181576 5/2/2005 B
1669 180550 4/25/2005 B
1669 183157 4/18/2005 B
1669 183729 4/11/2005 B
.
.
.

I would like to extract the data and transpose it as this:

sales/
man A B
279 8965 78515
774 65245 332566
1098 22969 122443
1669 78669 186734
.
.
.

Each salesman has only one row of record, which is the maximum of
Product A and Product B. I know I have to loop through salesman and
then maximum of A and B. I am not sure how to do this in Excel. Thanks
for any input.

I appreciate both of your help. I was confused about my issue. I also
need the date associated with the row found. I can not find the
solution using pivat table. What I look for is this:

sales/
man A Date-A B Date-B
279 8965 6/27/2005 78515 6/27/2005
774 65245 4/25/2005 332566 7/4/2005
1098 22969 6/27/2005 122443 6/27/2005
1669 78669 6/13/2005 186734 6/23/2005

How do I produce this data list? Thanks again.