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



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 Tutorials

Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Combine Values from Multiple Cells into One Cell in Excel
There are two easy ways to combine values from multiple cells in Excel. In order to do this, we need to do what is ...
MOD Function in Excel
The MOD function is very simple but it can be used to do wonderful things in Excel. It returns the remainder after ...
Combine Data from Multiple Worksheets in Excel
The easiest way to combine and consolidate data in Excel. Simple method to combine data from multiple worksheets i ...

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!


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 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

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!




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, 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 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?

Many thanks.


Hi

I have a problem

I have 2 spreadsheets which I need to combine

both have various codes in column A and values in columns C TO F

some codes appear in 1 spreadsheet and not in the other

how do I combine these 2 into a third one which holds all the codes from both sheets and adds the values for the correct codes in cols C to F on sheet 3

at present I have tried vlookup but cannot work out how to get rid of na error when code appears on one sheet but not on the other however it does work if code appears on both sheets.


Thanks
ricky


Hi all,

I have a fairly complex spreadsheet (5k+ rows and 25+ columns) where I am trying to combine data from several different columns into a simpler format in order to populate a summary report I have to submit each month.

I've come up with a HUGE formula full of IF statments to try and combine the columns if certain criteria are met. I've gotten the single and double line entry formulas working correctly but when I try to combine data from several different rows, the formula becomes too much to manage.

I'm looking for suggestions on how to combine the data more efficiently. I'm open to any method that would work... VBA, adding more columns, formulas... anything.

I've created a very simplified example file with only the columns that affect the formulas I'm trying to figure out.

In the attached example, I need to combine data from orders from different companies. I need to combine model and serial numbers affected by the purchases based on the models ordered and the serial numbers that incorporated the changes from the production line (ie the specific serial numbers that incorporate your change are x, y, z).

The sheet "Example 1" will not be seen by anyone else in the company so I can manipulate the data any way I need to. The sheet "Summary Report" is the format in which I need to display the data and the format is pretty much set in stone.

Thank you in advance for your thoughts and please let me know if you have any questions...


Hello,

I have 10 workbooks (by month) which each contain the same financial data points. I am trying to combine 5 columns from each of these 10 sheets into 2 columns on a sheet named Combined.

The sheet names are Jan08, Feb08 ... through Sep08. The columns which I want to combine are B, G, AD, AE and AO.

Ideally these combined columns would paste into the Combined workbook in range A:E.

Is this something that can be done with a macro?

Thanks in advance for any assistance.


Hi,

I want to gather all sales from Jan 08-YTD. This information had to be downloaded from our sales software into two different excel spreadsheets. I have manually merged the information from both spreadsheets but am left with hundreds of rows of duplicate item names (column A below) but different data (columns B-D below). Does anyone know how I would move the data from B2-C2 to E1-F1 and then delete the duplicate name in A2?


A B C D E F
1 Apple 14 15 15
2 Apple 7 3
3 Orange 33 22 55

4 Orange 2 4


Im trying out MrExcels Easy-XL and for the life of me, Im having a heck of a time getting my head wrapped around a simple function like MERGE

I have a list of products that somehow got copied a few times and various work was done on each of the 4 copies! Now what I need to do is combine them based on the (product number) and simply append all the data.

Some sheets might have for example part of the dept or sub catagories done, whereas another sheet might have the Mfgrs all updated, another might have the Vendors we buy them from updated ..

and what I have to do is just put them all together.

Given the 4 "MERGE OPTIONS" available (to output rows with the types of matches) ... for some reason Im just getting totally goofed!

1 Rows in sheet 1 that match a row in sheet 2
2. Rows in sheet 1 that DONT match a row in sheet2, (columns combine from sheet 2 will be blank)
3 Rows in sheet 2 that match a row in sheet 1
4. Rows in sheet 2 that DONT match a row in sheet1, (columns combine from sheet 1 will be blank)

I just need the idiotproof version / way of doing this ...
I was just hoping to Merge!

Sorry .. I know in a week, Ill look back at this and be totally ashamed of myself!

Thanks


Hi all,

I have a fairly complex spreadsheet (5k+ rows and 25+ columns) where I am trying to combine data from several different columns into a simpler format in order to populate a summary report I have to submit each month.

I've come up with a HUGE formula full of IF statments to try and combine the columns if certain criteria are met. I've gotten the single and double line entry formulas working correctly but when I try to combine data from several different rows, the formula becomes too much to manage.

I'm looking for suggestions on how to combine the data more efficiently. I'm open to any method that would work... VBA, adding more columns, formulas... anything.

I've created a very simplified example file with only the columns that affect the formulas I'm trying to figure out.

In the attached example, I need to combine data from orders from different companies. I need to combine model and serial numbers affected by the purchases based on the models ordered and the serial numbers that incorporated the changes from the production line (ie being able to tell the customer "the specific serial numbers that incorporate your change are x, y, z&quot.

The sheet "Example 1" will not be seen by anyone else in the company so I can manipulate the data any way I need to. The sheet "Summary Report" is the format in which I need to display the data and the format is pretty much set in stone.

Thank you in advance for your thoughts and please let me know if you have any questions...

I have 11 spreadsheets where I need to combine them into one sheet. Basically, I want each individual spreadsheet to be a tab on a consolidated spreadsheet. Is there an easy way to do this, other than manually copying?


Can anybody please help?

I have two seperate data files one is our companies product and price information the other is our competitors. They both Share the same first column of barcode, that is the only data that matchs. I want to bring both spreadsheets into one matching the barcode and retain both sets of information. Our spreadsheet has 8057 lines and thiers has 18798 so we wont match all but need the unmatched on the same spreadsheet too. Any help would be most welcome.

Both spreadsheets use 17 columns of info. Only the first column barcode has the same info on both spreadsheets but the barcodes are in different orders on both spreadsheets.


I have a table with 24 columns across. Some columns have headings, some do not and the columns are not in the same order in each of the spreadsheets im looking at.

That being said i know the text that will be in row 3 of the columns that i actually want.
They are "Leader", "Promo" ,"Talker", "Description", "Normal", and "Difference".

I need a macro that will delete all other columns given that i dont know where these columns are.

eg. "leader" could be A on spreadsheet 1 but in column D on spreadsheet 2.

Any ideas would be much appreciated.

ta

Kyle


I have a problem that I'm hoping someone can help me with. I have a spreadsheet that contains several identical tables where I compile data on a weekly basis. I've set up a table on a master sheet where I combine the data from each sheet in order to compile a combined total. Each week I add another sheet. The problem is I have 2 seperate groups of data on each sheet. So I have to enter the data set twice. For example: I combine the number of minutes on the left of the table and the number of occurances on the right of the table with the description in the middle. So I consolidate the minutes portion for each sheet then delete them and consolidate the occurances portion from each sheet. Well I'm up to 16 weeks (38 more to go) and it is becoming quite cumbersome to select a range, pick 16 sheets, delete them, then select a different range, pick 16 sheets and then delete. I do this on 6 seperate spreadsheets. I looked at an add-on but it didn't help.
Thanks Gatorlass