I have created a series of spreadsheets to help my daughter in her role as a
part-time Avon rep. Generally, everything works fine but I am stuck in one
particular area. Each 3 week period (campaign) her customers order roughly
200 items. I type these into a sheet and then use the Pivot Table wizard to
produce a summary of the item numbers and quantity of each required. The
problem is that I can't manipulate the ouput from the Pivot Table so is
there a different way to produce a summary? The reason I want to use the
data is that I have another sheet with 19 columns (A is the item number and
the rest are the 18 annual campaign numbers). I input (manually) the item
numbers with the quantity ordered at the bottom of this master sheet, then
sort it by item number so that I have duplicate item numbers in column A
with quantities under various campaign numbers. I want to merge this data so
that I only have 1 row for each item number. For example, row 1 shows item
12345 in column A then each of the related 18 columns will have the quantity
of item 12345 ordered for that campaign. Can anybody tell me if there is a
way to merge the rows using code please?

Regards,

Peter

If you have the master sheet, and separate sheets for each campaign, you
could use the SUMIF function on the master sheet to aggregate the quantity
for each p/n for each campaign. If your input data is on a sheet named
Campaign1, with p/n in column B and qty in column C, then on the master sheet
in row B2, you'd enter =sumif(Campaign1!$B:$B,$A2,Campaign1!$C:$C). You'd
just change the sheet reference to Campaign2, etc as you work across.
Alternatively, you could continue with the pivot table, and on the master
sheet use a vlookup to get each p/n's quantity for that campaign:
=vlookup($A2,PivotCampaign1!$A:$B,2,false). (And in columns next to the
pivot you could use the match function to identify new p/n's:
=match(a2,Master!A:A,false) will return #N/A for the new items.)


"Peter Horrocks" wrote:

I have created a series of spreadsheets to help my daughter in her role as a
part-time Avon rep. Generally, everything works fine but I am stuck in one
particular area. Each 3 week period (campaign) her customers order roughly
200 items. I type these into a sheet and then use the Pivot Table wizard to
produce a summary of the item numbers and quantity of each required. The
problem is that I can't manipulate the ouput from the Pivot Table so is
there a different way to produce a summary? The reason I want to use the
data is that I have another sheet with 19 columns (A is the item number and
the rest are the 18 annual campaign numbers). I input (manually) the item
numbers with the quantity ordered at the bottom of this master sheet, then
sort it by item number so that I have duplicate item numbers in column A
with quantities under various campaign numbers. I want to merge this data so
that I only have 1 row for each item number. For example, row 1 shows item
12345 in column A then each of the related 18 columns will have the quantity
of item 12345 ordered for that campaign. Can anybody tell me if there is a
way to merge the rows using code please?

Regards,

Peter