Matching data in two sheets

C

CP

Hi thanks for reading

I have a project where I need to compare month to month data from two sheets
and then make further calulations

Sheet1 = current month
ColA = Client
Colb = Value

Sheet2 = previous month
ColA = Client
Colb = Value

I require sheet1 ColC to show difference in value from previous month
(sheet2) to current month (sheet1) using the value in ColB but also matching
the customer.
If no customer match show a text value "text"

Many many thanks if you can help me :)
 
M

Max

One way ..

In Sheet1,
In C2:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"Not
found",B2-INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)))
Copy down. Adapt to suit.

High-five? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
C

CP

Many thanks for quick response
thinking a little more logically - if sheet1 contains all my clients in ColA
then B, C, D is the past three months
Assuming sheet2 data goes to col B, sheet3 to C and so on - I could make a
more useful and logical table
Possible? if so how?

I am thinking long term this is more useful
 
M

Max

Your new query should actually be posted afresh.
It's a different ball game ...

In Sheet1,
List the "other" sheetnames in B1 across, eg: Sheet2, Sheet3, etc
Then place in B2
=IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0)),0,INDEX(INDIRECT("'"&B$1&"'!B:B"),MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0)))
Copy B2 across/fill down as far as required to populate col B's figures for
the clients listed in A2 down from each of the other sheets (all assumed
identically structured). I've made the "IF not found" return as zeros to
minimize downstream calculation distress, since you probably would be
calculating further from the summarizations that's compiled here. Adapt to
suit.

Celebrate success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top