Copy Unique data to a different worksheet
 

Worksheet 1

Date Prod Id Customer Amount
1/2 B123 John Doe 5
1/2 B123 Jane Doe 4
1/3 G456 John Smith 5
1/4 S789 Jane Smith 4

I need a formula, function, or Macro to do the following: resulting in WB 2
Copy unique Prod Id (ex. B123 is listed twice but I only need it displayed
once on WB 2 with the amount of both as displayed). This to help track sales
of a product quarterly.

Result for Worksheet 2
Prod ID Jan Feb Mar
B123 9 0 0
G456 5 0 0
S789 4 0 0

Copy Unique data to a different worksheet
 

This sounds like a perfect setup for using Data|Pivottable.

Select your range
data|pivottable
follow the wizard until you get to the dialog with a layout button on it.
Click that layout button
Drag the prod id field to the row field
drag teh date field to the column field
drag the amount field to the data field
(if it says "count of Amount", double click on it and change it to Sum.)

Then finish up the wizard.

If you want to read more about pivottables...

Here are a few 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

Sdbenn90 wrote:

Worksheet 1

Date Prod Id Customer Amount
1/2 B123 John Doe 5
1/2 B123 Jane Doe 4
1/3 G456 John Smith 5
1/4 S789 Jane Smith 4

I need a formula, function, or Macro to do the following: resulting in WB 2
Copy unique Prod Id (ex. B123 is listed twice but I only need it displayed
once on WB 2 with the amount of both as displayed). This to help track sales
of a product quarterly.

Result for Worksheet 2
Prod ID Jan Feb Mar
B123 9 0 0
G456 5 0 0
S789 4 0 0

--

Dave Peterson

Copy Unique data to a different worksheet
 

ps.

After you finish up the wizard, rightclick on the date header and select
choose group and show detail|group by months and years.



Sdbenn90 wrote:

Worksheet 1

Date Prod Id Customer Amount
1/2 B123 John Doe 5
1/2 B123 Jane Doe 4
1/3 G456 John Smith 5
1/4 S789 Jane Smith 4

I need a formula, function, or Macro to do the following: resulting in WB 2
Copy unique Prod Id (ex. B123 is listed twice but I only need it displayed
once on WB 2 with the amount of both as displayed). This to help track sales
of a product quarterly.

Result for Worksheet 2
Prod ID Jan Feb Mar
B123 9 0 0
G456 5 0 0
S789 4 0 0

--

Dave Peterson