Merge 2 Spreadsheets?

Nov 23, 2009

I want to do....is...a macro or something....to be like "Hey...were going to match up column information based on the first column, there exists both the same data in both sheets....as you can see in A3 and A2, now, I want to be able to add the information that is in book1, into the "DBL approved", so like...the 2nd row, I want columns B-G to be filled via book 2 because its saying,

"okay, A3 in DBL has the data 00-120-01, but there exists no data next columns over...but oh look, A2 in Book1 does, let's fill in that missing information!" so now it'll fill in the bullet points, brand, segment, etc."

View 4 Replies


ADVERTISEMENT

Merge Two Spreadsheets (files) Based On ID

Sep 30, 2011

I'm a long time excel user and I've come up with an issue, it can surely be solved in any other larger database language but excel is just my everyday friend and I was wondering if it's possible to do such a thing.

I have a list of "people" in my 1st ssheet and they have

ID | ProductName| Category | Year |

and I have this data on the 2nd ssheet

ID | Price | Description

There is 600.000 records in the primary sheet but only 22.000 in the secondary because the data doesn't repeat (one description can fit to 10.000 products, no need to write it down that much times).

But now I need to compile one big/large file, so I need to have one file that contains all:

ID | ProductName| Category | Year | + | Price | Description

In MS SQL I would use the "Where" function and compare the ID here and there, but is there a possible way to do it in excel?

View 6 Replies View Related

Merge Couple Of Spreadsheets With Common Column / Field

Sep 15, 2013

I have a couple of spreadsheets. all of them have a same or common column called "ID".

The order of the ID columns of the spreadsheets are not in the same order however.

I'd like to see if there is a way that I can merge the spreadsheets to one using the shared ID.

To explain more:
Spreadsheet1 columns are: "ID", "URL", "Location"
Spreadsheet2 columns are: "ID", "Picture"
Spreadsheet3 columns are: "ID", "Address"

As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.

View 12 Replies View Related

Turn The F1 Key Off In Spreadsheets

Dec 5, 2008

I'm going to turn the F1 key off in my spreadsheets so that I do not hit it by accident so manytimes, but how do I bring up help without it?

View 5 Replies View Related

Combine Several Spreadsheets Into One

Jun 24, 2009

I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?

View 6 Replies View Related

Joining Two Spreadsheets

Nov 13, 2009

I have two spreadsheets, A & B. A can be thought of the master spreadsheet and has one record for every employee giving personal information, including a personel number. B has 0, 1, or 1+ records for every employee in A. Each of these records has a personel number. I want to copy the value of a specific field from B to A.

For example:

A:
personel number1
personel number2
personel number3

B:
personel number1, 'sally'
personel number1, 'john'
personel number3, 'pete'

merge A & B:
personel number1, 'sally', 'john'
personel number2
personel number3, 'pete'

View 5 Replies View Related

Linking Two Spreadsheets

Aug 5, 2008

I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.

On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.

I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.

The workbooks are attached below.

View 12 Replies View Related

Data In Different Spreadsheets

May 21, 2007

how do i access data from different spreadsheet tabs in a same excel file..e.g: if tab 1(student) and tab2(teacher), how do i access tab2 let say cell C4

View 14 Replies View Related

Comparison Of Two Spreadsheets

Mar 6, 2009

I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".

For example:
Spreadsheet 1:
Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.

If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F

View 15 Replies View Related

VLOOKUP Across 2 Spreadsheets?

Nov 1, 2011

I have two spreadsheets (lets call them Spreadsheet A and Spreadsheet B). Spreadsheet A is a list of all my customers and Spreadsheet B is a list of potential leads which may or may not include some which are already customers. Is there a formula I could include in an extra column to highlight which ones are customers already so that I can ensure I don't call them.

If it makes a difference the two spreadsheets are stored on seperate locations on my computer.

View 7 Replies View Related

Changing 503 Spreadsheets At Once

Jul 10, 2008

I have been handed 503 spreadsheets containing system data for multiple business units in my company. Each spreadsheet is identical in structure (same column headings) but just varies in the number of rows of data

Ive now been asked to add (4) additional columns to each of the 503 spreadsheets. The (4) new columns are to be called:

Class
OpCat Template ID
Agent
Last Occurrence

And added in columns L, M, N, and O.

Is there a way to do this automatically instead of having to manually enter this data 503x?

View 9 Replies View Related

Get Data From Several Spreadsheets

Feb 5, 2009

I have got a few spreadsheets which save information for each departments, about 8. They contain information on bookings & I am being asked to get information on certain date ranges, mainly monthly reports. (They are being filled in automatically from another excel file.)

Can I have a file which I could call "Master file" which has a macro in that looks up and copies all the relevant rows from each departments spreadsheet into the new master file. I dont think this would be too difficult (still beyond my capabilities though) My intial thoughts were something like (in half code half english).

Create a form where you specify your date range and a submit button e.g. txtstartdate txtenddate

Lookup 'G:FolderGeneral[General.xls] IF in between txtstartdate and txtenddate then paste

ActiveWorkbook.Sheets("master sheet").Activate
Range("B2").Select
Do

View 9 Replies View Related

Linking To Other Spreadsheets

Jan 4, 2010

Is it possible for me to link to other spreasheet data, and have the links update (retrieve the data) without those files being opened?

View 9 Replies View Related

Shared Spreadsheets

May 20, 2006

I have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.

View 4 Replies View Related

Countif With 2 Spreadsheets

Jun 28, 2006

Im using this code. My data is in column A from Cell A2-A65000, and the data I want to compare against is in a different sheet col K cells k2-k65000. I need it to compare column A on "Sales Reps" against Col K "Data Sheets" and count how many times whats in cell A2-A65000 occurs on in Cells k2-k65000. So if 689 was in cell A2 and was listed in cell k3, k4, k5, it should return the value 3 in Cell B2. My formula is not working. Getting an error.

For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ce.Offset(0, 1) = WorksheetFunction.CountIf(Sheets("Data Sheet")(Range("k:k"), ce.Value))
Next ce

View 8 Replies View Related

Comparing Two Different Spreadsheets

Jul 29, 2006

I have three different spreadsheet, Spreadsheet A with the name of all the clients of the company. Spreadsheet B with the name of all the clients in Toronto and Spreadsheet C with the name of all the clients in Montreal. Now i have to create a fourth spreadsheet to lookup the clients who are not in the Spreadsheet A (basically trying to find new clients). How do i search for the new clients in all the three spreadsheet?

View 2 Replies View Related

Linking Spreadsheets

Jan 14, 2007

I have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:

I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.

when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.

View 3 Replies View Related

Cross-referencing Two Spreadsheets

Jun 20, 2014

Basically I order thrice a week from a certain supplier to fill the shelves of my shop.

They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-

order ref
date
unique product code
description
qty

9262
03/07/2013
622642
Fabric Dye
4

And an invoices spreadsheet with the following columns:-

invoice ref
date
unique product code
description
qty

122551
04/07/2013
622642
Fabric Dye
4

Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.

I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-

1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times.
2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.

View 5 Replies View Related

How To Compare Data Between Two Spreadsheets

Jul 23, 2011

I'm doing an audit where I need to compare data between two spreadsheets that are housed on the same worksheet. The data I'm trying to compare is the ssn#. I'm expecting duplicates, I'm trying to find ssn#'s that do not match up with another. In other words, if one spreadsheet has 92ssn#s, the other has 79, I want to be able to identify those that do not have a matching ssn# from one spreadsheet to the other.

Both spreadsheets have the following columns:

Column A Column B Column C
SSN Last Name First Name

The tab on the bottom of sheet 1 is titled "CX Data", the tab on the bottom of sheet two is titled, "BCBS of AL Data"

in creating a VLOOKUP formula that will meet my needs?

View 7 Replies View Related

Switching Between Multiple Spreadsheets

Oct 23, 2007

I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.

Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.

View 2 Replies View Related

Merging Content From Two Spreadsheets

Oct 24, 2008

What I am trying to do is take the data (all columns) in Spreadsheet 2 and put that data in Spreadsheet 1 with all the companies matching up, how do I do this, is this possible?

I tried using VLOOKUP but I don't know if I was doing it right and I don't even know if it's the right function to use.

In sheet 1, column A I have a list of about 30,000 companies
In sheet 2, column A I have a list of about 10,000 (pulled from sheet 1), I have other info associated with these companies in columns B and C.

Basically I want to take column A, B and C from sheet 2 and match them up with the same companies in sheet 1. So there will be 10,000 matches and 20,000 N/A's. This is how I set up the VLOOKUP but nothing happened.

View 8 Replies View Related

Comparing Information In Two Spreadsheets

Jul 31, 2009

I have two spreadsheets. Spreadsheet A has a column of numbers representing employee IDs (thousands of them). But I need to also have the First and Last Name for these employees to cross-reference. Spreadsheet B has the employee ID, Last Name, First Name. Spreadsheet A is large and grouped in a way that I cannot just sort by the employee ID to match up with Spreadsheet B. So basically what I am trying to do:

Look in Spreadsheet B for the employee ID found in Spreadsheet A. If found insert the information in the Last Name and First Name cell for that row copied into Spreadsheet A

Example:Spreadsheet A
Employee ID
123456
412378
<blank cell>
512345
685217
654553

Spreadsheet B
123456 Alberts Rick
654553 Johnson Mark
412378 Smith John

So I want for Spreadsheet A to compare with Spreadsheet B. If 123456 is found in Spreadsheet B, then put Alberts and Rick in the corresponding cells next to 123456 in Spreadsheet A.

View 3 Replies View Related

VLookup Multiple Spreadsheets

Sep 28, 2009

I am trying to find a vlookup formulae for multiple spreadsheets in excel.
I have the below data that i need to lookup, test and produce an output.

Main Spreadsheet:

Column B...........................Column C
A 0000564235.................... <Desc> <---- Desired output is Coumn C from searching all part numbers in each spreadsheet.

Spreadsheet 1:

Column B (Part No.) ..........Column C (Desc)
A 0000564235................................Hose

Spreadsheet 2:

Column B (Part No.) .......Column C (Desc)
A 02315645646 ............................Clamp

Spreadsheet 3:

Column B (Part No.) .......Column C (Desc)............

View 11 Replies View Related

Filter Data In Spreadsheets

Nov 1, 2009

I have some data that looks like this:
5グ

5\グ
5
5㒅
6e[ủ
6
6h
6
6

and in another sheet, corresponding data like this.
5
6
7
...
53
54
V55

I want to be able to filter out the rows from the first sheet when a symbol (kanji) from the right hand side is included at a number higher than that of the second sheet.

For example, appears as a number 6 in the first sheet but doesn't appear until 54 in the second sheet. Therefore I would like to delete the row with in the first sheet.

Is there a simple way to do this? If not, can anyone suggest a way to program this? I was thinking of writing a little VBA code that reads the the characters and then compares them to every number above and if it doesn't find it, deleting that row but I have 2 problems.
1. How can I tell VBA to actually delete a row (not just the contents preferably)
2. These are asian characters which I'm not sure are well supported in strings which I would need for comparison?

View 9 Replies View Related

How To Match Data From Two Spreadsheets

Jun 6, 2010

I have two spreadsheets with data. Sheet one has about 2,000 employee IDs on it and sheet two has about 300 employee IDs on it. I need to write a formula in sheet one to find which employee IDs from sheet one are also listed in sheet two.

Is there a filter I should use to do this or is there a formula? I tried using the formula

=ISNUMBER(FIND(A3,(Sheet2!$A2:$A$200)))

but that didn't seem to work.

View 6 Replies View Related

Making New Spreadsheets From Tabs

Feb 19, 2013

Is there an easy way to make a new spreadsheet for each tab of a current spreadsheet?

View 3 Replies View Related

Spreadsheets Populated By Scanners

Jun 3, 2013

I am trialing a WASP scanner that populates a excel worksheet. I am reading Item number (P12345) quantity (Q123) etc.

Currently all readings are in column A

A1 is p12345
A2 is q123
A3 is p123456
A4 is q123

I need to have all item numbers in one column and all qtys in another column and any other data I read in separate columns

the set up or the extraction from column into columns for item, qty, etc

View 2 Replies View Related

Merging Template Changes Into Spreadsheets

Jul 14, 2013

I have a template and several spreadsheets based on this template, and when changes are made to the template they need to be manually merged into each spreadsheet, a very painstaking, time consuming, and error prone process. Changes are mostly added rows, data tables, and macro (coding) changes. Surely there must be a better way of doing this. There must be some way of automating at least part of this process, or so I would think. I know that changes to a workbook can be tracked, but I don't shows changes to code, and I don't know if it is true for templates as well (but I suspect that it is); at least this would show me what changes to the template had been made, but maybe not changes to the code, but this is only a small part of the problem.

View 1 Replies View Related

How To Combine 3 Spreadsheets Using A Unique Id

May 14, 2013

I have three files that I need to combine using a unique Id:

File A: This File include the following colums. Stock_Code, Vendor, Price... etc

File B: This File includes: StockCode: ManufacturerID, ProductDetails, Image;...... etc

File C: This file includes: StockCode, ServingSizeText,... etc

I want to combine all 3 files in a database utilizing stock_code as the unique identifier .. how do I do that?

I have tried coping them and put them in one file but then I am not utilizing stock_code.

how I can do this I have looked at a forum by [URL]...

View 6 Replies View Related

Combine Multiple Spreadsheets?

May 7, 2014

I was curious if there was a way to combine multiple spreadsheets that have multiple tabs all onto 1 spreadsheet?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved