merging 3 spreadsheets into one based upon a single common data field

OK I hope you guys can help me out.  Here is the scenario.  I have 3
spreadsheets.  The main spreadsheet I working with has 500 lines with a
unique number string.  The next two spreadsheets have about 12000 lines
but also contain the same unique string as the first spreadsheet.  I
want to search the 2nd and 3rd spreadsheet using the first spreadsheets
unique id number and then take the information from the 2nd and 3rd
spreadsheet and then merge it into the first.  Does that make since? 
Any help would be great.


-- 
xchosen
------------------------------------------------------------------------
xchosen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34476
View this thread: http://www.excelforum.com/showthread.php?threadid=542340

0
5/16/2006 2:11:39 AM
excel 39879 articles. 2 followers. Follow

3 Replies
550 Views

Similar Articles

[PageSpeed] 4

You need to use VLOOKUP. Assume the unique number filed is column A in
all sheets, and let's assume the sheets are called Sheet1, Sheet2 and
Sheet3.

In the first sheet (the one with 500 lines), you can enter a formula
like this in, say, D1:

=VLOOKUP(A1,Sheet2!A$1:F$12000,3,0)

This will search through the data in Sheet2 looking for an exact match
with the item in A1 of Sheet1. If a match is found, then the data from
column C of Sheet2 is returned. Change the references to suit your
situation, then copy the formula down.

Hope this helps.

Pete

0
pashurst (2576)
5/16/2006 8:12:06 AM
Thanks an enormous amount.  I was able to get what I wanted but I wen
about it in a very brute force way.  I copied all xls documents to tex
files then created a .bat file that searched each source line.  It the
created 528 separate files with the infomation I needed.  I then copie
all those files back into one .txt file.  I then imported it back int
xls.  It work but It was such a pain.  I knew there had to be a
efficient way to go about what I wanted.  Big thanks

--
xchose
-----------------------------------------------------------------------
xchosen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3447
View this thread: http://www.excelforum.com/showthread.php?threadid=54234

0
5/16/2006 12:55:55 PM
Thanks for feeding back.

Pete

0
pashurst (2576)
5/17/2006 12:21:12 AM
Reply:

Similar Artilces:

Preparation and Summarisation of data #2
Hi thanks a lot. The pivot table is great. I got another proble though. What do I do if I want to summarise my data like so: I have Time C D 930 423 231 930 312 233 930 314 245 931 224 432 931 234 435 931 244 431 And I want to summarise it as Time, C', D' 930 423 245 931 224 431 ... XYZ, (First value of C where Time is 930), (Last value of D where Tim is XYZ) Thanks again Debra Dalgleish Wrote: > YOu can create a pivot table from the data. There are instructions and > links on Jon Peltier site: > > http://www.pe...

Fields in recordset
(ADODB Recordset A2007 Open Dynamic) If I open a simple recordset whose recordsource is another A2007 database, change a field on a given record (x=y assignment), and then use that field (x) before issuing any move, update, etc. methods, will that assignment be valid? Will I be referencing whatever was in "y"? as opposed to what was in "x"? -- Jim It depends. If you mean: myRecordset!X = 2 and later without moving off the record you have not save you refer to then the new value will be returned. The old value has not been changed in the table,...

want to wss 3.0 infopath forms display in browser
i have wss 3.0 installed and make forms library in which some forms which i have created with Microsoft infopath 2007. I want the user which can access the site and forms can open the forms in browser not in infopath 2007. is it possible. Kindly reply me soon. Hello Sam, Yes, that's very possible. I don't have access to a 2007 box, but I believe it is the advanced settings section of a form library that contains the option "View in browser". Once you set it, Forms Services renders all InfoPath forms to HTML so end users will see it in their browsers. K...

Custom entity display within the form 4.0 behaving different than 3.0
In CRM 3.0, if I chose to display a custom entity in the Sales area of the Wonderbar, the entity would also display in the Sales section of the Account navigation bar (if the custom entity was related to the Account entity). However in CRM 4.0, a custom entity we created shows in the Details section of the Account form - as opposed to the Sales section. It shows under Sales in the main CRM wonderbar as expected - just not on the form of related records. Has anyone else noticed this and is there a workaround? Matt; Have a look at the relationship that is defined between the Account and your...

Formating Bars based on the value of a bar
I have created a bar chart in an access report. I want the bars to change color based on the value of the specific bar. For example, if the value of a bar is less than 1, it is red, if it is between 1 and 5, it is yellow, if it is greater than 5 it is green. How can I do this? Seems like an easy problem, but I've just spent a couple of hours playing with this and I can't make it work. The problem is that the Value property of the points in the SeriesCollection is not exposed in the VBA interface. It certainly exists (eg. with the chart in design mode, if you hover the mouse...

Help! ESEUTIL /r (3 character logfile base name)
Who knows the (3 character logfile base name) mean? Thank you so much! "Liu222" <liu222@discussions.microsoft.com> wrote in message news:<02cb01c4842f$028dbff0$a301280a@phx.gbl>... > Who knows the (3 character logfile base name) mean? > > Thank you so much! usually E00 "Liu222" <liu222@discussions.microsoft.com> wrote in message news:<02cb01c4842f$028dbff0$a301280a@phx.gbl>... > Who knows the (3 character logfile base name) mean? > > Thank you so much! usually E00 ...

Can I set a filter on the source data for a pivot table
I want to exclude certain records from a pivot table... i.e., Buy/Sell Quantity B 5,000,000.00 S 5,000,000.00 B 1,000,000.00 B 10,000,000.00 S 7,000,000.00 B 500,000,000.00 S 200,000,000.00 Only inlcude "B" records. Can a pivot table be set up to filter? Thanks ChrisP You could add the Buy/Sell field to the page area of the pivot table, and select B from the dropdown list there. ChrisP wrote: > I want to exclude certain records from a pivot table... i.e., > > Buy/Sell Quantity > B 5,000,000.00 > S 5,000,000.00 > B 1,000,000.00 > B 10,000,000.00 &...

Field in grid EMPTY when going offline
When online the overview grid from accounts shows data of a particular picklist field. When offline, this field stays off course in the grid , but without data ! When you doubleclick an account in the grid (with empty picklist data) the accounts opens and you can see that it has data in the particular picklist item. What can be the cause of this empty field in the grid? ...

Data import for custom entities
Hello I created a new entity (CRM 3.0) and i'd like to import a big amount of data for this entity. Unfortunately the import wizard is useable for accounts, contact, leads and campaign responses only. Have anyone an idea to import data for custom entities? Thanks a lot If you can't find any other ways to import data to custom entities, you probably will need to use Scribe or C360's import tool. Scribe have a data migration license for 30 days for as little as $500 I believed. Hope this will help. Darren Liu Crowe Chizek http://www.crowecrm.com On Jul 30, 10:28 am, Gerd Gott...

Purchase Order Error #3
The users are trying to get into the PO search to print off approved PO’s. Every time they get to the area of bringing up the PO thier machines locks up. We have checked and no one is in Dynamic’s entering PO’s at these time. If they try to end the task Dynamics closes. ...

Extender Field
I created an extender field type "LIST" and entered about 300 records. When I try to add more records it not saving for somehow. Is there a limitation on the number of records you can add on field type "LIST"? regards, What version of Great Plains are you using? Are you getting any error messages? On Apr 23, 5:04 pm, davidv <dav...@discussions.microsoft.com> wrote: > I created an extender field type "LIST" and entered about 300 records. > When I try to add more records it not saving for somehow. > Is there a limitation on the number of recor...

Field changed from Single to Double issue..
I have an Access 2000 DB (I think it's 2000, it's whatever comes with Visual Studio 6.0).. Simply put, I have a field that is of type "Single" that contains the value 0.3. When I modify that field and make it "Double", access automatically converts the values, as it should, but returns 0.300001192 in the field.. I understand that there can be precision issues sometimes in comparisons and such, but is this NORMAL for Access to do as well? Is there any simple work around or patch? I suppose I can write an SQL statement to take care of this, but that isn...

Macros in Excel
I am trying to automate two function for multiple users of a series of microsoft files. While running a macro to create a new file for archiving specific data, I would like the user to be able to assign a filename while running the macro that creates the file. Is this possible? If so, how? Second, for the above mentioned file I would like to create a macro the automatically launches the send to mail recipient function, but allows the user to select the recipients from a corporate mail list while running the macro. Any help would be greatly appreciated! Thanks! Take a look at Appl...

How to find Display Names that differ from form field labels
Given the fact that CRM can be customized to use a field label on the form that is different from the Display Name of the attribute, I would suggest that users be given some kind of tool to identify the Display Name and/or Schema Name when they are on the form. There are a few good reasons to have the field label differ from the display name, but users may be confused when they can't find that on the list of fields in the Advanced Find. In addition, some power users (who don't have admin rights) may create SRS reports, and therefore need the Schema Name. ---------------- This...

Referenced cell displays formula, not data
I downloaded a form template and linked it to my data; when I use the = sign to insert my data into the template, some – though not all – cells display the formula of the cell reference rather than the data that is actually contained in the cell Example: Cell D10 in datasheet contains text; instead of displaying the text in the template, it shows the following formula that is the reference address: ='[data sheet.xlsx]'!$D$10 -- MZ Press CNTRL + TILDE Key whch is available above the Left Tab Key. Tilde key (`) or (~) Remember to Click Yes, if this post helps! ...

How to append query w/lookup fields....
I have a table that I need to append w/new records on a weekly basis. format of original info: UserName TimeStamp RequestType #OfRequests Source pettaj 11/10/2009 ACT notes 2 BR Split the data into UserName, RequestType & Source table - as this information fairly static and the request is in Request table. When appending the Request table with the weekly data the Date & Number of Request, populates just fine, but the UserName, RequestType & Source ends up being blank (these are all lookup field to other tables). What do I need to do? Thanks! -- Krys...

Mail merge recipient list
When I select the recipients from my data source (xcel spreadsheet), it prints all of the recipients instead of just the few that I selected by checking the boxes. I have used/done this several times and never had a problem. I created a new spreadsheet for the new year. I know that it is pulling from the correct data, because it prints all from my spread sheet. Can somebody help? Assuming you are using Word for the mailmerge and Excel as data source. Excel just does what it is told by the settings in Word's mailmerge. See these sites for help on mailmerge. http://w...

Data Access Pages Data Source
I have several data access pages I created with tables as the source. The tables are in Microsoft SQL Server 2005 Express. I have since created queries and copied them into the database as a View. How do you change the data source from the linked tables to the Views? Thank you. it's not good idea to uase data access pages - they are not supported in latest Access versions. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Arturo" wrote: > I have several data access pages I created with...

How do I make a formula refer to given data even if I do a sort f.
I want to create a formula in a spreadsheet that refers to specific cells of data. However, I also want to be able to sort my data but keep the formula refering to the same information. IE, say I have a collumn of ten numbers. I would like to be able to have a formula take the average of the first 5 listed. If I then did a sort function of these ten numbers that changes the order of the numbers, I want my formula to have the same result because it is still referencing the correct information. ...

Referencing Data in a Pivot Table
I have created a pivot table which shows me total money spent in various accounting categories. I want to reference the sums this creates to subtract them from a total budget to give me a remaining total. Unfortunately, as the pivot table is updated, cell references change. This means that the total I used as say total spent on fuel may become total spent on salary. Can anyone help? You can use the GETPIVOTDATA function to extract data. Look in Excel's help for information and examples for your version of Excel. Brian wrote: > I have created a pivot table which shows me total mon...

Status on one sheet w/ data?
How do I setup a stats (sum, count or whatever) on the same page as the data and yet able to sort the data at will? If I add a count, for example, at the bottom of the data, and sort, the "count data" becomes part of the sorting!! Is there a stats that can be setup like a page footer thing? Sorry, I am not good at excel in this regard. Thanks, Perry When you sort, select only the data and not the cells with the statistics ? -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email <perryche@yahoo.com> wrote in message news:1186399300.3...

importing *.csv data into two columns
I have a data file that is delimited as comma separated values: escentially one long row of comma separated values. I need to import this long string into two columns: read two comma separated fields and start entering data into a new row and repeat. There are no empty cells as column 1 always has a matching value in column 2 for that particular row. I would appreciate any help. Thank you. Brian brian wrote: > I have a data file that is delimited as comma separated > values: ... I need to import this long string into two > columns: ... This looks like a job for Excel's ...

I can't see field From:
Im have a user A that should send emails with "send From:" user B. But user A don't see field From on address field and on View/From to, and his can't add it. How could i make it visible? Wojtek I am using Outlook 2003 with Word as my e-mail editor and when I open a new message I can go to the drop down arrow next to Options on the menu bar and select From. I hope this information is helpful. Robert Findlay Partner Technical Lead -- BizApps Microsoft Technical Support for Platforms and Business Applications ...

Code to Shade a Field if Criteria Met
Can I do this on my subform without using Code on an event? I am not quite ready to do the code on my own yet. This is my statement in english If the ThpyTypeID_fk is equal to 88, 81,70,73,74,43 (these are my AutoNumberIDs of the therapy types that could be entered)......Make the backcolor to the EquipID_fk field light yelllow The tricky part to this is both of these are combo boxes. For the ThpyTypeID_fk, My column count is 2 and the bound column is 1 which is the ID field in my tblTherapyType. For the EquipID_fk field, the bound column is 3 which is the EquipID in my Equipment T...

Contacts, creating or relabeling a new phone field
Hi, I'm using Outlook 2007 and was wondering if it is possible to create or relabel a new phone field such as Skype. I have 19 possibilities - Business, Home, Mobile, Other, and so on, but would like to be able to, say, alter 'Other' to become Skype. Possible? Thanks, DH ...