combining columns from to different files so that the rows match

I have three files, all with a common column B labeled Serial Numbe
(numeric).  The other two files have additonal columns, Program Clas
Code (text) in column C and Division (text) in Column D.  Not all row
have data in them, as some are blank.  I would like to combine al
three files, so that the information from the two files with th
Division Code and the Program Class code match the serial numbers.  B
combining the row information from three different Excel files, I wil
create one file with all the information.  The only criteria is tha
the serial numbers that identify each row, regardless of the fil
source, matches.  Thanks

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

0
12/11/2003 7:48:39 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
296 Views

Similar Articles

[PageSpeed] 7

You want to do VLOOKUPS on column B.

To the right of the information in file1

=vlookup(B2,'C:\Documents and Settings\Administrator\My 
Documents\[file2.xls]Sheet1'!$B:$B,2,"false")

Obviously your path and fileneme will be different. The 2 
after $B:B$ is the column number for the information you 
are pulling.  i.e. b is 1, c is 2, d is 3  etc.

Do the same to the right of all this for file 3. Then copy 
the whole sheet onto itself using paste-special> values.


Good Luck



>-----Original Message-----
>I have three files, all with a common column B labeled 
Serial Number
>(numeric).  The other two files have additonal columns, 
Program Class
>Code (text) in column C and Division (text) in Column D.  
Not all rows
>have data in them, as some are blank.  I would like to 
combine all
>three files, so that the information from the two files 
with the
>Division Code and the Program Class code match the serial 
numbers.  By
>combining the row information from three different Excel 
files, I will
>create one file with all the information.  The only 
criteria is that
>the serial numbers that identify each row, regardless of 
the file
>source, matches.  Thanks.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
12/11/2003 8:04:45 PM
Reply:

Similar Artilces:

Text to columns question
I have some data in the following format: Number firstname lastname schoolname The first three fields are one word each. Schoolname can be more than one word. I'm trying to parse the data using the data -> Text to Columns wizard, but I cannot figure out how to tell the wizard that there are only 4 space delimited columns in my data and not 5 for those instances where the schoolname is two words. Any help is appreciated. Thanks. On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote: > I have some data in the following format: > > Number firstname lastname schoolname >...

How do I bring page from one file into the other?
How do i bring a page from one publisher file into the other? I try copy/paste but it doesnt seem to copy the text along with the text boxes. Ill highlight everything, copy, go to new document, paste, and everything pastes but the text boxes are all empty. Is there any more "normal" way to do this? Are the text boxes linked to other text boxes? If they are, break the link, the text will still be there. Copy/paste again and then re-link. -- Mary Sauer http://msauer.mvps.org/ "Greg" <Greg@discussions.microsoft.com> wrote in message news:A9F73A4D-8FC4-4935-8...

Find/replace with different text colour messes up
When doing a Find/Replace on a certain word that needs to have a different colour than default - say, red - Excel incorrectly colours the whole cell instead of just the word that was searched on. To see this in action, try this: 1.. Open up a blank Excel sheet 2.. Enter some text in a few cells - "This is a test", for instance. Now, let's try to use search/replace to colour only the word "test" in red. 3.. Open up Search/Replace 4.. On the "Search for"-line, enter: test 5.. On the "Replace with"-line, enter: test 6.. For the "R...

How do I import OE .dbx files into outlook?
Old machine crashed but had fortunately backed up data, including important OE .dbx files. How do I import these .dbx files into outlook (no longer have OE) so I can access the content? Simon wrote: > Old machine crashed but had fortunately backed up data, including important > OE .dbx files. > > How do I import these .dbx files into outlook (no longer have OE) so I can > access the content? Why do you no longer have OE on the old machine that crashed that we are to assume where you will reinstall the same version of Windows that before had OE? See http...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

document for file I/O with VBA
Hi all, Where can I find a complete document about File I/O with VBA? I find it hard to find in help due to the way it is organized. I want to find ways to deal with files especially ".INI" alike text files using Open, Input, etc. (let me know if there are better ways). The functions I need are to seek lables like [General] and delte or replace data in the section. Thank you, muster <muster@gmail.com> wrote: >Where can I find a complete document about File I/O with VBA? I find >it hard to find in help due to the way it is organized. Access Type Writing Data ...

Change Opportunities Product functionality to match that of the qu
It would be very useful to allow the Unit Price displayed within Opportunity Products as you can within the quotes area. We sell a number of application and services and these are priced on a deal by deal basis. Currently it looks like you have to create a Price List per Opportunity which adds additional steps to the process of creating valid opportuniites with adequate product analysis. We have added the ispriceoverridden field to the opportunity product form in an attempt to allow us to change the unit price for the product line. however the form does not then allow the unit price to...

Adding numbers in columns with letters
I need a little help. How do you add up the values of numbers in column which has letters next to the numbers. I have a column o numbers, but the letters "mb" are a space bar away from the numbers. Everytime I use the function to add the numbers I get a value of zero. Could anyone please help me out? Thanks in advance -- Message posted from http://www.ExcelForum.com Assuming you mean the "numbers" all end with " mb", e.g. "123 mb", you can add a "Helper" column containing a formula like this: =--LEFT(A1,LEN(A1)-3) Copy down as far as...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

sharing offline folders among different profiles
I would like to share (ie use the same folders) my offline Outlook contacts and calendar folders in two different profiles. How? Many thanks. OST files can be opened only in the profile/mailbox for which they were created. What is it you need to accomplish? There may be another way. rbuce@btoauto.com wrote: > I would like to share (ie use the same folders) my offline > Outlook contacts and calendar folders in two different > profiles. How? Many thanks. rbuce@btoauto.com <anonymous@discussions.microsoft.com> wrote: > I would like to share (ie use the same folder...

How do I unwrap text to columns?
I have a spreadsheet where the addresses have been saved as wrapped text in one column. I want to spilt the addresses into columns as Address1, Address2 etc but I can't enter the wrap character (which I think is ALT+ENTER) as a delimiter in the Text to Columns wizard. I have also tried search and replace but again Excel will not recognise ALT+ENTER as a delimiter Any ideas much appreciated! Thanks Re-format the column to eliminate the wrap.............then use Data > TextToColumns with Alt010 (from the keypad) as the delimiter Vaya con Dios, Chuck, CABGx3 "ChristineR&...

Transaction Matching #4
I was doing some data clean-up (from 2005) and noticed I had incorrectly categorized a few transactions, so I went back and corrected them this morning. All these transactions involved a credit card payment that I errornously had entered as a "household" expense, rather than calling it a "transfer: [account]". I went back and changed the transaction in my checking account so it represented a "transfer: [creditcard]". When I did this, like Money should, it created the corresponding entry in my creditcard account. My problem is that I now have duplicated ...

Outlook 2000. Trouble importing PST files from another Outlook 2000
I recently had to reformat my hard drive and reinstall Windows on my computer. I have Windows 98, and Office 2000. I made PST files of my curent outlook contacts ..., the reinstallation went fine until I attempted to import the PST file into Outlook. While using the Impoert/Export Wizard, just after brousing to the file in question, as soon as I clicked on the "Next" button, I got the following pop up message "Properties for this Information Service must be defined prior to use" Click "OK" When I clicked "OK", I got another message "File ac...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Highlight Cells with different values
Hi, I have a column that I want to locate the differences. I can use the F5---Special---Column Differences. But I wanted to know if I can do it with Conditional Formatting. Like if Value in A1 is different than the values in Column A:A, then it is highlighted... Any help appreciated Fuad Sounds like an Excel question..try one of the Excel newsgroups, this one is for Publisher. "Fuad" <Fuad@discussions.microsoft.com> wrote in message news:0536FB95-362E-412F-BD1C-99BF9B10E8CD@microsoft.com... | Hi, | | I have a column that I want to locate the differences. I can use th...

hiding selected rows
I have a list of items QUAN DESC PRICE TOTAL PRICE All the items are listed on the PRICELIST sheet I want to display only those items with QUAN greater than zero on the ESTIMATE sheet. I'd rather do this with some formula which combines IF and HIDE ROW. If necessary, I could use a macro, but I'd rather not (to keep it simple) Thanks, John John Gilchrist wrote... .... >All the items are listed on the PRICELIST sheet > >I want to display only those items with QUAN greater than zero on the >ESTIMATE sheet. > >I'd rather do this with some formula w...

print 3 column range in six columns
I have an excel database with three columns as follows: Column: A B C Column Title: Tail # A/C Type Owner There are about 300 records in my database so when I print I have to either manually format my data into more (6) columns or print these three columns over multiple pages. If I format the worksheet manually to print 6 columns then I cannot sort or add records to the end of my list. I have to enter my new information and then cut/paste to the correct location. I would like to be able to continue entering my data in one lo...

Dragging by keeping the column the same
Hi, Can anybody help me? I have created a formula and I want to drag i horizontally but keeping the same column. I used $G3, but it is no working. Thanks for the help. Marco -- Message posted from http://www.ExcelForum.com Hi Marcos $G3 works fine for me, could you please copy & paste your exact formula (from the original cell and from the first cell you drag it to) Regards JulieD "marksuza >" <<marksuza.1bjtx6@excelforum-nospam.com> wrote in message news:marksuza.1bjtx6@excelforum-nospam.com... > Hi, > > Can anybody help me? I have created a for...

Q) Last row/column ------ excel formula query
Kindly suggest what is the best way to reference the following in excel formulas: +) last row of a worksheet that has some data +) last column of a worksheet that has some data Regards, Sandeep Hi Sandeep In VBA the best thing is to use this functions You can use this in your code then for example Lr = LastRow(Sheets("Sheet2")) Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:...

importing mmf files
I have just upgraded (at work) to Outlook 2002. In previous versions I used to be able to open/import my old msmail files with the .mmf extension. In the new version opening data files only gives .pst open/import, is there a program that will convert or a way round this problem, I'm stuck without all my old archived data. ...

Reports in column format
My report page set-up is for 3 columns with zero spacing between columns. There is a box around the set of controls. I have the palette narrowed to the absolute minimum width so that there is no working space to the right of my controls. The result is a calendar-like presentation. The problem arises when I want to center text in the headers and footers. If I simply place my header text in the header section, it will appear more or less towards the left side of the page. In order to get it centered, I have to widen the palette and then center the text manually. If I do this, then...

Can't divide the columns by twelve anymore
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC On this forum someone taught me to divide columns by twelve -- ie everything above by twelve you just put: <br> =B52/12&#09; <br> =C52/12 <br> =D52/12 <br> =E52/12 <br> =F52/12 <br> etc. <br> Now when I do that I get this: <br> <a href="http://tinypic.com/r/15wnc3o/6">http://tinypic.com/r/15wnc3o/6</a> Click OK and it will open a debugging tool that will point to the problem for you. On 27/11/09 4:15 AM, in article 59bada6c.-1@...