Merging rows

[Peter Horrocks]

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

 

[Guest]

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.)