Merging 2 files

Hi all,
I have 2 workbooks that are similar to the structure below.
Workbook 1                                        Workbook 2
ColA   Col B                   ColC     ||         ColA   Col B       
ColC
A        Desciption of A      1        ||          A        Desciptio
of A      3
B        Desciption of B      2        ||          C        Desciptio
of C      2
C        Desciption of C     1        ||          D        Desciptio
of D     3

Is there any way that I can combine the two of these to resemble th
following.

ColA   Col B                   ColC               ColD
A        Desciption of A      1                  3
B        Desciption of B      2                  
C        Desciption of C      1                  2
D        Desciption of D                          3

As can be seen I want to keep all of the values in seperate columns
I've look on the forum but everything seems a bit over my head. What'
the easiest way of doing this. The files are too big to cut and paste
Any help greatly appreciated. Cheers
Sha

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

0
7/19/2004 10:16:37 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
586 Views

Similar Articles

[PageSpeed] 53

Perhaps this might help ..

Let's say you have:

In Book1.xls, Sheet1
---------------------------
in cols A, B and C, from row1 down

A Text1 1
B Text2 2
C Text3 1
D Text4 ..
etc

Note: Col B is assumed to contain only unique descriptions,
i.e. Text1, Text2, etc will occur once only (if they do occur) in col B

In Book2.xls, Sheet1
---------------------------
in cols A, B and C, from row1 down

A Text1 3
C Text3 2
D Text4 3
etc

Note: Same caveat on col B as for Book1.xls above

In a new Book3.xls, Sheet1
-------------------------------------
List in cols A and B
from row2 down

A Text1
B Text2
C Text3
D Text4
etc

Put the 2 source filenames into C1 and D1, viz.:

In C1: Book1
In D1: Book2

Put in C2:

=IF(ISNA(MATCH($B2,INDIRECT("["&TRIM(C$1)&".xls]Sheet1!$B:$B"),0)),"",OFFSET
(INDIRECT("["&TRIM(C$1)&".xls]Sheet1!$C$1"),MATCH($B2,INDIRECT("["&TRIM(C$1)
&".xls]Sheet1!$B:$B"),0)-1,))

Copy C2 across to D2, then copy down as many rows as there is data

Cols C and D will return what you're after

For a neater look, extraneous zeros can be suppressed from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

Note: The 2 source files: Book1.xls and Book2.xls have to be open
along with Book3.xls for the above to work

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"shav >" <<shav.19nwen@excelforum-nospam.com> wrote in message
news:shav.19nwen@excelforum-nospam.com...
> Hi all,
> I have 2 workbooks that are similar to the structure below.
> Workbook 1                                        Workbook 2
> ColA   Col B                   ColC     ||         ColA   Col B
> ColC
> A        Desciption of A      1        ||          A        Desciption
> of A      3
> B        Desciption of B      2        ||          C        Desciption
> of C      2
> C        Desciption of C     1        ||          D        Desciption
> of D     3
>
> Is there any way that I can combine the two of these to resemble the
> following.
>
> ColA   Col B                   ColC               ColD
> A        Desciption of A      1                  3
> B        Desciption of B      2
> C        Desciption of C      1                  2
> D        Desciption of D                          3
>
> As can be seen I want to keep all of the values in seperate columns.
> I've look on the forum but everything seems a bit over my head. What's
> the easiest way of doing this. The files are too big to cut and paste.
> Any help greatly appreciated. Cheers
> Shav
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
7/20/2004 2:41:49 AM
Reply:

Similar Artilces:

Smith's SK2 2-Stone Sharpening Kit
Price:$13.95 Image: http://thediscountlocator.info/image.php?id=B000B5JXU2 Best deal: http://thediscountlocator.info/index.php?id=B000B5JXU2 Deluxe Sharpening Kit, Contains 1 Each 5" X 5/8" Medium Arkansas Stone, 1 Each 4" X 1" Fine Arkansas Stone, 2 OZ Honing Oil. SIMILAR PRODUCTS: 550lb. Type III Paracord:http://thediscountlocator.info/index.php?id=B000S5ODO6 Swedish Firesteel- Army Model:http://thediscountlocator.info/index.php?id=B0013L8D9K Enter The Kettlebell! Strength Secret of The Soviet Supermen:http://thediscountlocator.info/index.php?id=0938045695...

Excel Drop Down Boxes #2
Question; does anybody know how to add additional data to a previous drop down box? I have created a form that has several drop down boxes a while ago, and due to some information that has been change I need to input more info in the drop down boxes but for some odd reason I cant remember how I executed these actions!! If it helps I am using Excel 2000. -- CRS ------------------------------------------------------------------------ CRS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27444 View this thread: http://www.excelforum.com/showthread.php?threadid=46...

How to read XML file without "Root" Node
Try to read following type of XML file, but only get data back on first node: <log id="1234"> <message type="Action"/> </log> <log id="5678"> <message type="Drama"/> </log> <log id="1357"> <message type="Art"/> </log> Here is my very simple VB program: Imports System.IO Imports System.Xml Dim readWNInfo As XmlTextReader readWNInfo = New XmlTextReader("test.log") While readWNInfo.Read() If readWNInfo.NodeType = XmlNodeType....

Mail Merge problem.
Hi, I'm trying to 'mail merge' a simple list of names into a Publisher 2000 document. I created the source database using Publisher and I'm only using one field name in the document, ie. "name". However, because the 'documents' are small I have three per page - but when I merge the database with the page all the documents/field show the same 'name". Is there a way or indicating to publisher that the three fields on the page are infact on the next document and hence it should index to the next record in the database ? Hope this makes sense -...

750 KB file saves 10 times longer than a 900 KB file
We use a spreadsheet in our office that has numerous macros. This spreadsheet is approximately 750 kb. I created another file with no macros that is about 900 kb. The 900 kb file saves enormously faster that the 750 kb file with macros I spend a lot of time waiting for these files to save all day long. I have tried moving the macros to another sheet to see if that would help - but it only helped minutely. I have no add-ins checked (I saw that suggestion on another post). Any suggestions are hugely appreciated. Thank you. I've never noticed a difference in time based on if a workbook cont...

Box when opening File
Quick question...When I open up one of my saved files, Visio automatically puts some kind of box (function box possibly) around my heading and some of the text. I'll take it off and save and it isn't there. But when I close the file and then open it up again later it's there again. I try to delete the box but it deletes my text with it. I can't resize or delete the box (actually conjoined boxes) by itself. Any help would be greatly appreciated. Feel free to call...214-981-6324. Thanks! Try changing the line weight of the shape to none. John... Visio MVP Nee...

Money 2002 Died #2
Experiencing the same situation. Please inform us of the situation. Thanks! We have the same problem here. Money won't start up. We reinstalled and it ran initially, but now it has the same problem again. Has anyone contacted Microsoft?! >-----Original Message----- >Experiencing the same situation. Please inform us of the >situation. Thanks! >. > Same thing here. This is scary! >-----Original Message----- >We have the same problem here. Money won't start up. We >reinstalled and it ran initially, but now it has the same >problem again. Has a...

Email addresses in a comma-delimited (CSV) file
I am trying to create a csv from another application to be imported into Excel. One of the columns will contain email addresses. Is there a way that I can format the data in the column to have the addresses be 'links' when brought into Excel. First, hyperlinks are not just formatting. The Insert|hyperlink style of hyperlinks are objects in excel. Second, CSV files are plain old text. They don't hold these kinds of objects. (And they also don't contain any formatting properties, either.) But third, you may be able to cheat a bit. Can you go back to the othe...

Event id: 474 2 days after offline defrag
Last week my customers exchange 2000 information store dismounted and stopped due to it's size exceeding the max 16gigs. I had to run an offline defrag which lowered the databse to below 16 gigs and it mounted properly. That night the was a good backup. Since then there has not been a good backup - basically because of Event id:474 - a chksum error. I an afraid to restart that server or touch it because all of the email is working - all is OK except it cannot be backed up. We are prepared to order another server and run exmerge to migrate the data - is this the best method - or is...

Changing Inventory Item Types #2
In GP7.5, is there any way to change inventory item types once they have been used? Can the item be deleted and then recreated at the end of a year? Are there any utility programs that can do this? In particular, I've got some "sales inventory" items that I want to convert to "kits" and others that need to be "services" We are on 8.0, but I don't think it is possible to change the item type - not even with Professional System Tools. We have changed the item name (via tools) to include the word Discontinued. Then we set up a new item to repl...

Problems saving excel file from outlook
I have an EU that whenever he saves an excel file from outlook, it will rename the file (by adding the number "2" at the end of the file "file2.xls") and it also changes all the embedding links in the document. (The embedding link of F:\users\file.xls changes to c:\documents and settings\file.xls) Are you setting a Hyperlink base for your links? See Menu > File > Property > Summary tab. Put the original folder location there and when you move or save the file the links will not be renamed. Also assigning a letter to the drive can cause problems (not ...

IF statement help #2
Hi What im trying to do is, Calculate a Cell x say 0.5. If the result is a negative number, sho 0. If it is a positive number, work it out and show the answer. Jus tried, and cant seem to get it right for some reason. I dont want t have to use another cell for no reaon, as I need to present th worksheet This is what I tried, IF((E22*0.5)>0), (E22*0.5), Thank -- RudeYut ----------------------------------------------------------------------- RudeYute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3295 View this thread: http://www.excelforum.com/showthr...

pdf. file
Help!! Someone has sent me an e-mail with a pdf file attachment. For some reason, unknown to me, I can't open it. Any suggestions, tips or clues??? thanx in advance Do you have Adobe Acrobat/Acrobat Reader installed? If you save the file to your hard drive, can you open it? deb wrote: > Help!! > > Someone has sent me an e-mail with a pdf file attachment. > For some reason, unknown to me, I can't open it. Any > suggestions, tips or clues??? > > thanx in advance do you have adobe reader? "deb" <nikko-cat@rogers.com> wrote in message news:0...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

help attaching files
Hi... Using XP Pro and Outlook 2003. For some odd reason, I am unable to attach files when sending emails. Click on the 'paperclip', window opens to the My Doc folder. then when I click on a sub folder to open, nothing happens... finally, a Microsoft Word window opens that it is unable to open.... It is almost as if it is trying to launch Word to open the folder... Help? Thanks! -- B'rgds, Vinnie FWIW, from the Microsoft side a new Knowledge Base Article (KBA) was released on Apr 15th. http://support.microsoft.com/?kbid=918165 It lists two known conflicts so far....

No more new fonts may be applied to this workbook #2
I have Excel 2003 (11.6113.5703) running on windows XP. I have loaded the latest update from the web. When I try to change the font size of text in the axis or label of a chart, a message shows "No more new fonts may be applied to this workbook." When I do a print preview of the charts, the same message shows up and then the print previews would be displayed. I'd appreciate if you can tell me the fix. Jon Peltier has some information on this in his Charting FAQ article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon025 and at his web site (use th...

Converting Eml files to Msg files with code
Hi. Sorry if this question is overly simple. I'm developing an integration project between Exchange and SPS. I read the message on a public folder of Exchange with ADO. I use ADO's stream object and save the message as a file. Created file formatted as .EML file and has .EML extension. We want to read this file in Outlook but it can't open the file. Because of this I need to format this file as .MSG file. I don't need a tool. I should make this process in code. Is there any API or code sample. Thanks in advance. ...

Show this folder as an e-mail Address Book #2
Hi all, I have just created a new contacts list in our public folders. When I go to all the workstations, the "Show this folder as an e-mail Address Book" is unchecked. To save me going to all workstations and checking this setting, is there some way I can set it to "Show this folder as an e-mail Address Book" automatically? Cheers Marty... ....ms... <martin@adg.com.au> wrote: > Hi all, > > I have just created a new contacts list in our public folders. > When I go to all the workstations, the "Show this folder as an e-mail > Address Book&q...

Transferring contacts from OAB file
Hello, I am trying to transfer the entire GAL from a work PC to a home PC. I copied the oab, ost, and pst files to an external harddrive but have not been able to open the offline address book in Outlook at home. The pst opens fine. Any assistance or ideas appreciated. Thanks Love your attempt in regards to the oab/ost, but take a peek at http://www.slipstick.com/exs/portagal.htm <dextrorama@gmail.com> wrote in message news:1148816290.691150.313800@i40g2000cwc.googlegroups.com... > Hello, > > I am trying to transfer the entire GAL from a work PC to a home PC. > > ...

updating #2
My microsoft money mutual fund page does not update anymore. Is there any reason? I did not change anything Thanks Dada ...

how do I get old versions of modified files?
I made a mistake and modified a file, how do I get back the old version? Thanks From your backup, which, of course you take every evening !!! John G "Power Image" <Power Image@discussions.microsoft.com> wrote in message news:910DFC71-460A-4169-A56E-AB90D905E9C5@microsoft.com... >I made a mistake and modified a file, how do I get back the old version? >Thanks ...

I'm sending 2 (duplicate) emails each time I send emai
I'm using Office 2007, Outlook and, each time I send an email.. the receiver gets two copies.... any thoughts? Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'd still be sufficiently protected by your on-access scanner part of the virus scanner. For more details see; http://www.msoutlook.info/question/20 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http:/...

Open & update another file (through macro) while running macro
I am using MS Excel 2003 and I need help to solve my problem. One excel file (suppose A.xls) in which I’ll update my graph and the data is available in another file (suppose B.xls). What I am doing (through macro) is that I have created one button (in A.xls) and when I pressed it; its open the file B.xls (the data file) and at their I select month from drop down from which its update the pivot table based on the selection month and then same I was doing with another data excel file (suppose C.xls). Query : Is there any solution that when I pressed button from file A.xls its ...

Compare two Sheets with Conditional Formatting #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

merge cells with a twist
A B C joe @ domain.com trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and that result is joe@domain.com Thank you. =A1&B1&C1 -- Regards, Peo Sjoblom (No private emails please) "jd" <jdumont@@novuscom.net> wrote in message news:uaAWelbgFHA.3436@tk2msftngp13.phx.gbl... >A B C > joe @ domain.com > > trying to merge cells in a row so that column a(joe) b(@) c(domain.com) > and > that result is joe@domain.com > Thank you. > > =hyper...