We have a fairly large amount of data that must be sifted through monthly to
create some of our financial reports. We need to combine sales data for each
item this year with sales data for each item last year. This years data
comes from a pivot table that accesses the database. To work with the values
we copy and paste the data into a new workbook. Last years data is saved in
another workbook. Complicating matters is the fact that since last year we
have started selling some new lines and have gotten rid of some oldones.
Thus the product lines won't match perfectly. We need to bring last years
data into this year and have it put itself in the column we specify while
matching itself to rows that allready exist, skipping rows that don't exist
in last years data, and creating rows for lines that existed last year but
not this year. I appreciate all your help on this matter. Thank you all for
you time.

Jesse

last year.......................................thisyea r
A....55..........................................A .....333
B....77........................................... D.....444
C....88..........................................A ......666

COMBINE THE 2


A....55.......YEAR1
B....77.......YEAR1
C....88......YEAR1
A.....333.....YEAR2
D.....444......YEAR2
A......666......YEAR2

Now use a sumproduct formula

=sumproduct(($a$1:$a$6="A")*($c$1:$c$6="year2")*($ b$1:$b$6))

this gives you the A totals for year2 = 999

you can make a table with A,B,C,D IN CELLS A2:A5 AND YEAR1 IN CELL B1
AND YEAR2 IN CELL C1. Then let your sumproduct formula reference the
row and column headings to pull all the totals for all the years. Much
better, in my opinion, than a pivot table.

in B2 the formula would change by the A in the first part of the
formula would be replaced by $a2, year1 by b$1 and year2 by c$1

now copy it across and down


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=574143

Robert,

Thank you for your response. We currently do something to that effect. The
problem is with 8000 lines it is labor intensive. We don't need the sum we
just need to bring the data in and have it line up. Thus far we have went
through and added lines manually so that when the data is copied in it will
match. We were hoping there would be a more automatic way to do that. Some
way to bring the data in and have it compare the two sets adding lines where
neccesary. Thanks again for your response.

Jesse

"Jesse R." wrote:

We have a fairly large amount of data that must be sifted through monthly to
create some of our financial reports. We need to combine sales data for each
item this year with sales data for each item last year. This years data
comes from a pivot table that accesses the database. To work with the values
we copy and paste the data into a new workbook. Last years data is saved in
another workbook. Complicating matters is the fact that since last year we
have started selling some new lines and have gotten rid of some oldones.
Thus the product lines won't match perfectly. We need to bring last years
data into this year and have it put itself in the column we specify while
matching itself to rows that allready exist, skipping rows that don't exist
in last years data, and creating rows for lines that existed last year but
not this year. I appreciate all your help on this matter. Thank you all for
you time.

Jesse