Comparing 2 works books

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I need to do this hopefully without VBA.

I have 2 large work books, this months sales data and last months sales
data. Each customer has a refence number, but if the customer hasn't made
any transactions, they don't appear in the work book, so in effect customer
12345 may be in line 20 in book1, but in line 50 in book 2.

What I want to do is take the sales data for each customer in book 1, and
add it to a new column against the same customer in book 2.

There are several thousand lines in each book.

Whats the most efficient way ?

Rich
 
Seems like the typical situation that we all get into - starting fro
the wrong place. *It took me some years before realising that th
"Database" approach is the right one. Keep a separate unformatted tabl
of customers data with a "Month" column (add new data to the bottom
and analyse that into reports rather than trying to collate all sort
of different spreadsheets.

(* Well I did start before Pivot Tables were invented <grin>
 
One approach to get it done using OFFSET & MATCH

Assuming you have:

In Sheet1 of Nov03.xls
in cols A & B, data from row2 down

CustRef...Sales
12345.......100
23456.......700
etc

In Sheet1 of Dec03.xls
in cols A & B, data from row2 down

CustRef...Sales...SalesNov03
23456.......200.....??
12345.......500.....??
etc

with col C containing formula to pull sales for the same cust from Nov03.xls

Put in C2:
=OFFSET([Nov03.xls]Sheet1!$A$1,MATCH(A2,[Nov03.xls]Sheet1!$A:$A,0)-1,1)

Copy C2 down col C

And if you want to return "blanks" for cases where there's no match (instead
of #NA's),

Put instead in C2:

=IF(ISNA(OFFSET([Nov03.xls]Sheet1!$A$1,MATCH(A2,[Nov03.xls]Sheet1!$A:$A,0)-1
,1)),"",OFFSET([Nov03.xls]Sheet1!$A$1,MATCH(A2,[Nov03.xls]Sheet1!$A:$A,0)-1,
1))

Note:
Nov03.xls must be open for the above to work in Dec03.xls.
CustRefs are assumed unique in col A for each month (appears max. once only)
 
Back
Top