Comparing 2 works books

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



0
noone3053 (5)
1/6/2004 7:06:54 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
394 Views

Similar Articles

[PageSpeed] 11

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>

--
Message posted from http://www.ExcelForum.com

0
1/6/2004 8:27:37 AM
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)

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
"Rich" <noone@none.org> wrote in message
news:btdmqd$m6o$1@hercules.btinternet.com...
> 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
>
>
>


0
demechanik (4694)
1/6/2004 9:55:34 AM
Reply:

Similar Artilces:

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Ctrl + ; stopped working
For the past week I've been able to enter the current date into a DB sub form field with formatted field using Ctrl = ; but now it's not working properly. Instead of entering the date, it moves the cursor to the center of the formatted field, and inserts nothing. I'm wondering what could have changed. I went directly to the sub form deleted the formatting, then redid it. But it's still not working even there in the sub form alone. My sincere thanks for any help you may offer. -- Norm Shimmel Butler, PA Strange that I should reply to my own message. But I was just surpr...

The Links from e-mails don't work
If I copy the links and insert it into IE 8 it reads it. Trying to link over from an e-mail directly is not working. If I copy the link and go to IE 8.0 the link (URL address) works. It was working before. pgman27 You mean if you click on the link in a message it does nothing, not even an error message? Are you certain IE has all its defaults? Open the Default Programs applet, which you can access either from the Start menu or via the Control Panel, then click the first item: "Set your default programs." After a few seconds, a list of programs comes up. Click on...

Money Central Portfolio #2
My print function has gotten corupted. When I print it flips to WORD / format and prints in a format that is 4x normal. ...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Statements #2
How are statements sorted? Alphabetically by name or by account number? Does anybody know? -- Any help is appriciated, Deb Mine are done alphabetically by name. -- Elizabeth M. "Deb" <Deb@discussions.microsoft.com> wrote in message news:D58FEF32-7509-473B-A554-6A48E02697CF@microsoft.com... > How are statements sorted? Alphabetically by name or by account number? > Does > anybody know? > -- > Any help is appriciated, Deb ...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Excel worksheet with VBE codes don't work elsewhere
Hi, Some of my excel worksheets with embedded controls and VBA codes don' work when I open it on another PC. Is there another way to make i work? Thx -- lazybea ----------------------------------------------------------------------- lazybear's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3519 View this thread: http://www.excelforum.com/showthread.php?threadid=54955 Specifically what problems are you having? Saying "don't work" means absolutely nothing. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ww...

copy does not work in word 2003
I can't seem to copy from word 2003. I select text but it won't take it. I can copy from an e-mail and paste into word, but word doesn't seem to want to copy, either from one word document to another or into my e-mail. It's been working fine for almost four years, it just acted up. Any suggestions? The usual reason is some third party add-in that is messing with the Office clipboard. If you start Word in Safe Mode, can you copy/paste normally then? From Start | Run (Winkey+R), type in winword /a and press Enter. Word will start in Safe Mode. Open two doc...

Setting up my work outlook account at home
I do not know how to set-up my outlook at home to check and receive email at home. This is really a question for your Mail Administrator. This depends on what ISP is used for work or if you use the Exchange Server. Then there may be a domain access issue. -- Nikki Peterson [MVP - Outlook] "Liz" <anonymous@discussions.microsoft.com> wrote in message news:049a01c39db2$d8df2cb0$a501280a@phx.gbl... I do not know how to set-up my outlook at home to check and receive email at home. ...

Macro
Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can an...

Set default address book automatically (registry)
I know I have done this before a few years back in another site - but I cant remember how. We need to set the default lookup address book for all our 2000+ OL2k3 users and to use a specific lookup order. I know they could each do this manually but we have 2000+ users and the support overhead would be huge. We dont want to users to use the GAL as their default lookup (which it is out of the box) because it contains lost of confusing system addresses and duplicate names for the same person in different security level networks; which the users find VERY confusing to distinguish between). We wan...

Time update as a limited user not working
I added time update permisssion to my limited user acct. but it does not work. When I try, the time synchonization is greyed out. How can I get it to work. Thanks. On Apr 4, 12:33=A0pm, Mint <chocolatemint77...@yahoo.com> wrote: > I added time update permisssion to my limited user acct. but it does > not work. > When I try, the time synchonization is greyed out. > > How can I get it to work. > > Thanks. Is this Windows MCE SP2? What method did you use to add time update permission to your limited user account? Does your unlimited user accou...

Sum amount if = 2 value's
I have a spreadsheet of payment types for which I want to sum the tota amount per type per month A B C Type Amount Month I'm able to get the total amount per type by usin =SUMIF(A:A,"TYPE",B:B), but can't work out how to get a total for eac type each month Somthing along these lines: =SUMIF((A:A,"TYPE",B:B)&C:C,"MONTH")) ???? Any idea's -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.ph...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

Outlook Express 6 Spell Check #2
I am using Outlook Express 6 on Windows XP, and I have a problem with spell check. When you send an email, it says "spell check has been haulted, do you want to send anyway" and if I ask it to spell check, it says an error has occured in spell check. All my spelling options are correctly set, so I don't know what the problem is. Kimberly McNabb <knmcnabb@hotmail.com> wrote: > I am using Outlook Express 6 on Windows XP, and I have a > problem with spell check. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Where can I get a good holiday booking form for my golf business?
I need a quality Booking Form for my Golf Holiday business on the Costa Del Sol. One which includes sections for accommodation, dates, numbers, flight enquiries and car hire enquiries. Any help would be much appreciated on a tight budget. Thanks Dave Wrong forum. Try templates or doing it yourself. :-) -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Dave" wrote: > I need a quality Booking Form for my Golf Holiday business on the Costa Del > Sol. > One which includes sections for accommodation, dates, numbe...

Database #2
Hi I'm new to Outlook. I hear of an Outlook database in other threads. Is this synonymous with the ..pst or .ost files people mention? Does it corrupt often and if so are there tools to repair it? Regards Craig. There's a utility called ScanPST.EXE that will scan and attempt to repair the file. Yes, typically when a user says Outlook database they're referring to their PST file "Craig Roberts" <craigroberts@jkr.co.uk> wrote in message news:C4C8E141.211DE%craigroberts@jkr.co.uk... > Hi > > I'm new to Outlook. > > I hear of an Outlook d...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

host unreachable #2
hi, if i send a email with a wrong address or mispelled it, i don't get a message that the address, destination is wrong until 4 days later after being deletedj from the outgoi queue im using E5,5 sp4 thks Is that for all domains, even for Yahoo and Hotmail? Rick wrote: > hi, > > if i send a email with a wrong address or mispelled it, i > don't get a message that the address, destination is wrong > until 4 days later after being deletedj from the outgoi > queue > > im using E5,5 sp4 > > thks ...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

Great Plains Books
All, Searching Amazon I noticed that there are a couple of Great Plains Dynamics books on the way. Does anyone have any thoughts about them or the authors? I'm trying to decide whether it's worth pre-ordering or not. Computerized Accounting Using Great Plains Dynamics http://www.amazon.com/exec/obidos/tg/detail/-/0324159676/qid=1129750076/sr=8-2/ref=pd_bbs_2/102-8755097-3487330?v=glance&s=books&n=507846 (not due until January 2007 though!) Introduction to Microsoft Great Plains : An Integrated Approach (Paperback) http://www.amazon.com/exec/obidos/tg/detail/-/013186064X/qid...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...

Closing Excel #2
I have inherited an App desigend by an ex employee Sometimes the program leaves Excel in the task manager The programe uses various routines and calling various functions that define Excel sheets etc (badly named) I'm finding it hard to follow (my 1st experince of .NET) is there a way to KILL any Excel instence when exiting the program. I have come across some posts about GC (garbage collection) like this System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWsheet) oXLWsheet = Nothing 'System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWs) oXLWs = Nothing oBook.Clos...