Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Combine Two Spreadsheets With Different Column Headings

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do I combine two spreadsheets of contacts and make one spreadsheet
conform to the structure of the other. Both sheets have the same data type,
but I don't want to have to cut and paste and move columns around in the
sheet or delete unwanted columns in the sheet.

Is there a way that I can just bring them together and have the one format
itself to the other?


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Delete Empty Columns
- This macro will delete columns which are completely empty. This means that if there is no data within the entire column

Similar Topics







I am trying to do a simple Vlookup between two spreadsheets. One spread sheet is a CSV data extract and the other is an XLS file. In both spreadsheet I have to combine 4 cells togeather into Column D to create the lookups between the spreadsheets they both have the same placement and same visible data for these for columns.

Column E (EX: "1601/0017/0000")
Column F is any of the following (PK,K,1,2,3,4,5,6,7,8)
Column G is any of the following (PK,K,1,2,3,4,5,6,7,8)
Column H (EX: "S0001")

The problem I am having is that when I combine these cells on both spreadsheet and then try and Vlookup between then the only way I can get it to return anything is by writing over the combined data in Column D of the csv file, then it works but only by me manually typing over it.

Here is the formula I am using to combine the 4 cells on both spreadsheets


Code:

 
=TRIM(E2)&Text(F2,"00")&Text(G2,"00")&Text(H2,"00000")


After I write the formula I copy and paste values only in both spreadsheet.

Not only does it not make both columns identical but If column F or G are "K" it shows a space with it on the csv ("K ") or (" K") and just "K" on the other spreadsheet

I just need two identical columns in both spreadsheets I don't care about format or how. Can someone please help me make two identical columns in these two spreadsheets with the columns I have shown combined togeather?


I am familiar with and use a macro to combine data from closed workbooks, but I have the opposite situation now (XL07). From a database, 91 Excel spreadsheets are opened. Call them Temp Sheets. Each has a unique name like Output001, Output002, etc. I'd like to combine these into one spreadsheet without saving them. The order is not important, just want to copy/paste columns A:G from each of the 91 spreadsheets and stack all the data in Column A:G on a separate sheet. Can this be done?

Thanks in advance for any and all suggestions.

jim


I have 50 spreadsheets for US states, with cities,zipcodes,population,and other data.

Every column header for each spreadsheet is the same across all the fields/ cells.

I would like to combine all these spreadsheets into one, do all the editing and spreadsheet work on all the data at the same time. I have enough ram memory to support the spreadsheet size.

Each city has 20+ data fields associated with it.

Is there an easy way to combine all these sheets? I started working with each spreadsheet for each state, but it is very tedious having to repeat all the same work on each spreadsheet, additionally loading & saving each file,etc.

I'd like to just combine all the sheets into one big sheet do all the calculations,etc. and save the sheet for any additional changes. Then I can just sort the large sheet and separate the respective sheets based on my sort and search criteria.

I recall doing this kind of thing by copy paste in the past, but I thought there maybe a better way.

So... I'd appreciate any suggestions

Thanks


Is there a way to combine spreadsheets? i.e I have a about 20 different spreadsheets that I need to have on one single spreadsheet but under different tabs. I can't use a standard Macro as the spreadsheet names are different every time - although the same format. Currently it is a bit a a lengthy copy and paste job!




Hi to all Excel Expert here,

I just want to find out if there are macro that can do this.

Attached are the example of the spreadsheets (it's actually longer than this, just want to make short to make things easier)

I would like you guys to assist me to combine 1-June Ind MLE Report and 1-June SM MLE Report spreadsheet into Sheet1,
And 1-July Ind MLE Report and 1-July SM MLE Report spreadsheet into Sheet2.

I would have more spreadsheet in future but at this point of time, let's stick with 2 spreadsheets.

All the 4 spreadsheet are in the same format.

Now if there's advance code that can allow me to just combine based on the selected header / field, instead of combine all the columns header, that will be much appreciated, as there are number of columns which I don't need, and will have to manually delete.
That's manageable assuming using macro to copy selected column/header/field is impossible.


Appreciate the feedback.

Thanks in advance b

Regards
cks1026

I have a list of 1200 serial #'s of assets belonging to my company and I need assistance in combining three very large spreadsheets into one complete accurate spreadsheet. The three sheets include data about model, location, department, addresses, account numbers. etc., and were made by three different people, (sources). The problem is, some spreadsheets have missing or out of date information, different formats, different column headings, # of columns etc. Also they are all sorted differently, but most of the information is identical for each serial #.

I need to develop one master sheet that contains the updated information for each serial number and was wondering what the easiest way to take all three sheets and combine them without updating each one seperately.


Ok, I am very very new so bear with me on this one. I have received several spreadsheets and would like to take the info from all of them and combine it into just one spreadsheet. Reason?: each spread sheet is the same format, but with different information. I want to be able to compare the data from same fields across diff spreadsheets and then if that data is the same to enter it onto the "master" spreadsheet. IF the data is not the same I would like it left blank so that I can see that I need to track down the right information IE: correct address.

Is there a certain process I should do? IE: combine the spread sheets and then convert that one master spreadsheet over to Access DB? My goal is to have one DB or spreadsheet that can be used. I would like to be able to querry the db maybe for all contracts expiering in the next qtr.... help any one??? or just ideas?


Ok, I am very very new so bear with me on this one. I have received several spreadsheets and would like to take the info from all of them and combine it into just one spreadsheet. Reason?: each spread sheet is the same format, but with different information. I want to be able to compare the data from same fields across diff spreadsheets and then if that data is the same to enter it onto the "master" spreadsheet. IF the data is not the same I would like it left blank so that I can see that I need to track down the right information IE: correct address.

Is there a certain process I should do? IE: combine the spread sheets and then convert that one master spreadsheet over to Access DB? My goal is to have one DB or spreadsheet that can be used. I would like to be able to querry the db maybe for all contracts expiering in the next qtr.... help any one??? or just ideas?


Is it possible to combine multiple spreadsheets into one. I have approximately 6 spreadsheets that have 11 columns in each spreadsheet. Some of the data is the same in each spreadsheets but other data is different. What I would like to do is maintain the data that is the same in each one, addiing only data that is different.

Thanks in advance,

Greg




I'm looking to combine several spreadsheets (separate files) into one. All of the columns are the same, but the rows contain different information. Think names and addresses. All the columns will be the same, but each row would have a different person for my christmas card list.

I am simply looking to append the data from each spreadsheet onto one master spreadsheet. I could open each file, copy all the rows, and paste onto the bottom of one of the spreadsheets and repeat, but there has to be a better way. All of the merge/combine questions I see answered have more complexity (wanting to add only certain columns, etc). Mine need is pretty simple, but apparently too difficult for me!

Any help would be appreciated!



Hi All,

I currently have is two spreadsheets (two different depts.) with, in some cases the same clients on each, each client have a dedicated ID made up of initials, DOB and Postcode (A/B-10/10/72-CV4/4RT).

What I'm trying to do is combine both spreadsheets dataset onto one, based on the ID and all the ones who aren't on both are preferably left off the new spread sheet.

Many thanks

Clash



Hi, new to the forum, so my apologies if this isn't in the exact right place. I work at a nonprofit and I'm trying to combine 2 separate prospective donor lists into 1. However, I don't want to simply add the 2 lists together. Rather, I'd like to create a new list out of contacts that are in BOTH of the original spreadsheets. So if a contact is in Spreadsheet A as well as Spreadsheet B, I want that person in my new list (call it Spreadsheet C). Is there a way to automatically create a Spreadsheet C that has only the contacts that are in A & B simultaneously?

Right now, I have color-coded Spreadsheets A & B and combined them, and I've re-sorted by contact name and am manually marking the names that are in both colors. I'm sure there has to be a more efficient way to do this, as there are over 10,000 total contacts for me to sort through.

Let me know if that isn't clear enough. I appreciate any help I can get.

Hi there this is my first post. I am trying to merge two spreadsheets one which just has predefined column headings and the other which is data received from clients in their format. I need to merge this to a new .csv file.

This would ideally be VB as I need others to run this without me being around.

Example sheets would be:

Open workbook1 from client

Col1, Col2, Col3, Col4,

I will then need to combine this with workbook2, this contains the headings, which would not be open at the time but will always be stored in a specific directory.

Then take the headings from Workbook2 and ensure Col1 from Workbook1 will go to Col3 of workbook2 and so on. Repeating this for many rows.

If you need further information please ask.

thanks for your help




Hi everyone

I have two spreadsheets (complete with macros) that I need to combine into one sheet. How do I do this??

I tried copying all the sheets across to a new book but everything wanted to reference back to the originals, also wouldn't let me save it so I could clean it up later...

There must be an easy way.......

Many thanks

Dave

Here is my query

Sheet A has got 30 columns
Sheet B has got same headings than Sheet A but in a different order.
Is it possible to combine both sheets without having to manipulate the order of the columns?
The code below combine 2 sheets that have got the same headings in the same order. Is the re away to modify it without adding lines of extra code to rearrange the columns?
[Code]
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub
[\code]


Hi,
sorry if this is a newbie question.
I have 2 spreadsheets each having one column of names and several columns of data for each name. Many of the names are the same, but the columns do not match exactly. I am trying to merge the two spreadsheets so that there is one column with all the names from both spreadsheets with the corresponding data for the names. However, I cant figure out how to make excel recognize the identical names and put all the data from both spreadsheets on the same row with that name.

Ex:
spreadsheet 1:
Steve 343 45 23
Paul 32 3 23
Frank 223 23 12

spreadsheet 2:
Tom 33 76 6
Steve 23 34 3
Paul 1 334 45

I would like to combine the data so that all the names are shown, but Steve and Paul are only shown once. There would be six columns of data, so I dont need that combined.

Does anyone know how to do this?
I appreciate your help.


Hi gurus,

I have a few spreadsheets I am using daily. One spreadsheet per customer (it contains up to 20 sheets, 1 sheet per product). All I need to do is to combine all those spreadsheets into one. But in that case it is very easy to get lost as you are handling huge amount of data. So, I made one spreadsheet (one sheet per customer with all products in it). I want to have a button for each product which would unhide the relevant to the product columns. I have made a very visual dummy example. It will tell you more.
Any help will be much apreciated.

Thanks a ton guys!


Hi all - I have a weekly task to combine 16 spreadsheets sent to me into one document.
Each workbook is identical containing the same format just different figures (weekly report)
Is there a quick way to combine these spreadsheets, so that rather than 16 .xls files they are one file with 16 tabs?
The current method of copy and paste works fine - but would be great if there is a process to speed this rather mundane weekly task!




HI!

As you can see in the attached file have 2 spreadsheets with similar values in the two of the tree columns. I would like to brink the value of the first spreadsheet to the second one if column a and b is the same. I only know how to use vlookup and bring the value if only one column is the same. now I have two columns and I don't know which fuction to combine.

Thanks in advance!



Hello,

Every week I take a large spreadsheet (~90 columns) and break that into smaller spreadsheets (~15 columns) manually. The smaller spreadsheets are subsets of the larger sheet but columns are in a specific order and sometimes not like the sequence of the large sheet. I typically just go in and manually copy all the columns into a new spreadsheet with all the accompanying row data, then delete the columns I don't need. But this takes forever!

Is there a way to pull only the required columns and data into my reoccurring smaller sheets?

For example:
The large spreadsheet has columns:
ABCDEFG

The smaller sheet has columns in this order:
ACF
or even
FBG

I created templates for each smaller reoccurring spreadsheet (just with the columns and no data) so I know which columns to bring over. I tried using a VLOOKUP formula but I have to know the col_index_num and that changes for each smaller sheet.

I am sure many of you have been in this same instance whereby you want to pare down a huge file into a smaller one but manually deleting or copy/pasting only those columns is tedious.

Many thanks!

Hi guys I need some assistance. I have two spreadsheets which I need to combine to provide the data in one table. The two spreadsheets must be combined based on the columns "EMIS NO" which are in both sheets and must contain all the information as provided in the various columns of the sheets. In the attachment it can be seen that the two sheets do not have the same number of rows as some of the information is not available. Can somebody help with a way of doing this? Is there a formula consisting of "IF" , "THEN" statements that can be used. I have tried a number of the functions but could not achieve the desired results.


Hi, I am still trying to get familiar with VBA. I have two spreadsheets with the same format and would like to have some VBA codes to automatically combine the two spreadsheet into one. Can some one point me to the right directon?

Thanks a lot!


I have a list of 1200 serial #'s of assets belonging to my company and I need assistance in combining four very large spreadsheets into one complete accurate spreadsheet. The three sheets include data about model, location, department, addresses, account numbers. etc., and were made by three different people, (sources). The problem is, some spreadsheets have missing or out of date information, different formats, different column headings, # of columns etc. Also they are all sorted differently, but most of the information is similar for each serial #. Example: There is a location associated with each serial# but some of the addresses don't match on each sheet. The sheet that is the most accurate and up to date is missing columns i need from the others such as account# or order#. How can i match the serial # with information from each sheet?

I need to develop one master sheet that contains the updated information for each serial number and was wondering what the easiest way to take all four sheets and combine them without updating each one seperately.

Thanks for the help!


I have two spreadsheets. They share data in four columns (first name, last name, title, company). One spreadsheet also has an ID number column, the other has a Business Type column.

The rows don't synch up (there are 32,000 rows), and the number of rows in each sheet is not an exact match. I need to combine these spreadsheets into a single document so that each row has all of the relevant data--I need to filter/match on first name, last name, title, and company, and then add the business type and the ID number all into a single spreadsheet.

Can anyone help? I'm totally new to macros, I've worked with formulas somewhat, and I don't have MS Access. Using Excel 2010.

Thanks!


Hi guys, hope you can help!

Essentially I have two spreadsheets, both with independent columns of data. However, both of them share a common column, row b. Row b contains the same type of data in each sheet, but one sheet has 1300 entries and the other 1600.

What I need to do is in a third spreadsheet, compare the two sheets together and where data is missing from sheet 1 that is present in sheet 2 and vice versa, get them to appear in sheet 3.

Does this make sense?!

Many thanks for your assistance.

Brian