Look up column name, match rows (a/cnumber) & summing up....its confusing!

[syedobaidullah]

Hi All,
Here is what I'v been trying to do but..!
I'v 2 sets of data (data1 & data2) Column name may be sane/different in
each data set and same applies to acct_no.. I want to prepare a report
that combine product & accounts data show accumulatd result on another
sheet/file.


Data 1 - upto Jan 2006

Acct. No Total Prod - A Prod - B Prod-C
1001 51 10 30 11
1002 47 15 20 12
1003 80 20 15 45
1004 64 25 16 23
Total 242 70 81 91

Data 2 - Feb 2006

Acct. No Total Prod - A Prod - B
1002 7 5 2
1004 16 10 6
1009 9 3 6
Total 32 18 14

Report required

Acct upto Jan'06 Prod - A upto Jan'06 Prod - B upto Jan'06-
Prod-C
Prod-A Prod-B
Prod-C
1001 10 30 0 11 0
1002 15 5 20 2 12 0
1003 20 15 0 45 0
1004 25 10 16 6 23 0
1009 0 3 0 6 0 0
Total 70 18 81 14 91 0

Above is the report required..Data from Data 1 for Product A (upto
Jan'06) appear in one column according to corresponding account number
and beside this Current months data (feb 2006) from Data2 is listed.
Report automatically match column name (very next to it) and shows data
of prior month in respective account row. Here in "Upto Jan'06
Product-A" column shows 0 for account # 1009 as there was no data for
this column in Data1 set.

Hope I m clear .!!!!
thanks for your support & assistance..
Syed

 

[kletcho]

You could use a pivot table and use multiple consolidation ranges.
It's sometimes difficult to work with, but is a quick way to marry up
information from different sources.