Combine Two Spreadsheets With Different Column Headings

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?



Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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 ...
Guide to Combine and Consolidate Data in Excel
Guide to combining and consolidating data in Excel. This includes consolidating data from multiple cells, multiple ...
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 ...

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







Hi peeps

I want to combine data from several worksheets into one worksheet.

For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).

I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.

I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?

Any help will be beautiful.

Cheers



I have the following code. In the "Master" worksheet I want to add columns headings from cells A1 to Z1
how can i do this through vba?

Sub Do_it()
Dim ws As Worksheet, rs As Worksheet
Set ws = Worksheets("Master")
Set rs = Worksheets("Point Estimate Complete")
GoSub Combine
Set rs = Worksheets("Estimates Validated")
GoSub Combine
Set rs = Worksheets("Estimate In Progress")
GoSub Combine
Set rs = Worksheets("Other Status")
GoSub Combine
Exit Sub
Combine:
lr = rs.Range("A65536").End(xlUp).Row - 3
lr2 = ws.Range("A65536").End(xlUp).Row + 1

rs.Range("A3:Z" & lr).Copy

ws.Range("A" & lr2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

lr3 = ws.Range("A65536").End(xlUp).Row

ws.Range(ws.Cells(lr2, "AA"), ws.Cells(lr3, "AA")) = rs.Name 'd

Return

End Sub




Hi Guys

I currently have multiple excel workbooks (11 workbooks), with multiple spreadsheets (approximately 12 sheets per). Each workbook is derived from a common template. I would like to combine these workbooks into a master document, i.e. one workbook with 12 sheets, with the corresponding information from each exel spreadsheet from each workbook, found on the coressponding sheet in the master document. Each document also has a prompt which asks to be updated when opening it. it is not necessary to update the document. Can anyone please assist me. Thanks.



Hi,
I'm looking for a way to combine (not concatenate) the data from 5 columns in a single column. In other words, if I had 5 rows of data in column A, 3 rows of data in column B, 11 rows of data in column C, 8 rows of data in column D and 9 rows of data in column E, I want column F to have the 36 values (5+3+11+8+9 = 36) from the other columns.

The data has significantly more rows than I've used in my example and cutting and pasting is getting a little tedious every time I need to combine the data.

The data starts in row 3 for each column and any given row has no more than 258 values.
There are no intermingling blank cells in a given column; the blanks are at the end of each column.
The data is text, not numeric

(Back story: The data in columns A-E is generated using array formulas that pulls data from other worksheets. I need to combine those columns in to one, then remove duplicate values and sort the resulting combined column. The removing duplicates and sorting part I can handle; it's the combining part I'm having problems with. I was about to drop into VBA to solve this but since when it comes to writing formulas I'm a noob, I thought that I check here first. I saw this thread but no one solved it.)

Any help would greatly appreciated!
Thanks!
Paul

Hi,

I have 3 columns, one is Text, one is Date and the last is Time. I managed to combine the Date and Time cells and have them formatted properly but I can't figure out how to add my Text. So I am here for help. Attached is my original spreadsheet. I would like have all three cells combined into one with maybe about 4 or 5 spaces between each value. Then I would like to use the Paste Special - Values to remove the formulas so I can delete the 3 columns and just have 1 column.

Thank you for your help.


Greetings,
I am familiar with Excel but I am a newbie to macros. I am using Excel 2003 and I am working on a couple of spreadsheets that contains over 50,000 rows. My problem is this: I need to look at values in column "A" of spreadsheet 1 and compare them to the values in column "A" of spreadsheet 2. If the values match for a certain row, then I need to copy the value on column B of spreadsheet 1 and paste it to column B of spreadsheet two. That is, i need help in accomplishing the following (Just an example): Look at values column A, spreadsheet 1 Compare to values in Column A, spreadsheet 2 if (lets say) row 4 in column A, sheet 1 matches row 10, column A, sheet 2 then... ...copy values in row 4, column B, sheet 1 paste value to row 10, column B, sheet 2
I hope this does not sound too confusing... i know it is to me. Can someone perhaps help with this? I would truly appreciate it.

PS.- i could even paste all the values from one sheet to another so that I can have all the values in one sheet if this would make it easier. That way i would need to compare values in one column to values in another column in the same sheet.

April12


I have two excel spreadsheets which for audits are logged by two separate departments. If the work is being logged correctly the spreadsheets have the same information on both, but we need a way of checking both documents and highlighting anything that is on one spreadsheet and not the other.

The spreadsheets have columns for Name, reference number, and agent


Hello and thank you in advance.
I have a spreadsheet form that is used to track parts. Users copy/paste pictures into their spreadsheet. These spreadsheets are then consolidated into a master sheet. I have written VBA to consolidate the data, but cannot figure out how to copy the pictures from the user sheets and paste them into the master sheet.
I would also like to be able to set the properties of the picture to "Move and size with cells".
I have tried a simple copy/paste of the cells that have the pictures, but when I run the macro, pictures do not get pasted.
Thanks,
Dave


This might be a piece of cake for some but it has me stumped.

I want to combine 'like shuffling a deck of cards' two date + time columns that overlap. The second column of dates+time of day must be placed into the first date+time column where it fits in chronological order. Example sheet attached which is very short compared to the many thousands of rows in the real world sheet. There is a corresponding data value associated with each date+time. The date+times are not necessarily in any consecutive order.

I wish to do this with formulas and not VBA if possible. Any ideas please.

Greg


Greetings,
I am familiar with Excel but I am a newbie to macros. I am using Excel 2003 and I am working on a couple of spreadsheets that contains over 50,000 rows. My problem is this: I need to look at values in column "A" of spreadsheet 1 and compare them to the values in column "A" of spreadsheet 2. If the values match for a certain row, then I need to copy the value on column B of spreadsheet 1 and paste it to column B of spreadsheet two. That is, i need help in accomplishing the following (Just an example):
Look at values column A, spreadsheet 1
Compare to values in Column A, spreadsheet 2
if (lets say) row 4 in column A, sheet 1 matches row 10, column A, sheet 2 then...
...copy values in row 4, column B, sheet 1
paste value to row 10, column B, sheet 2


I hope this does not sound too confusing... i know it is to me. Can someone perhaps help with this? I would truly appreciate it.

PS.- i could even paste all the values from one sheet to another so that I can have all the values in one sheet if this would make it easier. That way i would need to compare values in one column to values in another column in the same sheet.

April12


Hello People,

I have a workbook with loads of worksheets in. There are lists of keywords in cols. A-I. I need to combine these words to make all possible strings of words in certain combinations.

EG: in col: J the title is: C-A-E, And in this column I need all the combinations of words in columns C, A and E.

Theit are other columns with different combinations. can I get excel to look at the column name and then make the selections of col.s to combine?? I would like to make a macro to do it really.

The problem is all the worksheets are different, Any help appreciated. I am currently using DigDB, and it is ok, but still takes too long fr me!!


Hi,

I have multiple Excel files with the same structure (same sheets and column headers). Each file has multiple worksheets. My master file has same structure, too, but it has no information in it.

I need to gather data from all files and combine it in the master file, duplicates are ok. I know that Access is right for that job, but I am not supposed to use it...Can someone please help me with the VB code to merge all data in one master file?

Thank you!!


I currently many columns of data that I want in one single column and am trying to automate the process with a macro.

I don't want to combine/consolidate the data. I want to copy the data from Col B and place it under the last cell of data in Col A, then copy the data from Col C and place it under the last cell of data Col A (which now also has the original Col B data underneath the original Col A data), then from Col D to Col A, etc... until all of the data is in Col A.

The length of each column will be changing on a daily basis, i.e. each column will have a different # of rows.

Does anyone know of way to make this happen?

Any help is greatly appreciated!

Thanks,

Greg


This is completely bizarre, and not sure why. I am trying to copy data from one spreadsheet to another containing dates in dates format.

However, once copied to the new sheet, the year is exactly four years earlier than the original. eg. 29-Nov-09 (original sheet) -> 29-Nov-05 (new sheet). I have tried many things, copy/paste value, copy/paste all, open new spreadsheets many time, and saving files in various xls formats. Still no luck.

Then I tried entering the number 1, into the original spreadsheet and format as date. Surprisingly, the date is 2-jan-1904, but in a normal spreadsheet, number 1 would be 1-jan-1900. Very weird.

Anyone know what the problem is? And how can I copy the dates correctly over to a new sheets? Thanks.


To all,

Hoping someone can help me.

I have a series of spreadsheets in excel that I use for calculations. Once I impute my data and have the formulae calculate my data, I copy these spreadsheets into MS Publisher, format them and make my reports. This has never caused me a problem until now.

Today I was working along as usual but when I pasted one of my spreadsheets into Publisher, one particular cell (appearing about 30 times in one spreadsheet) had changed sizes. I checked the size of the cells (column) and verified that nothing had changed, I checked all the formatting and all was good, I checked my settings in Publisher and nothing out of the ordinary.

Then I tried to paste more sections of the same spreadsheet (Actually 1 spreadsheet with different calculation tables down through the worksheet). And some of the pastes pasted fine while others within the same sheet didn't (larger sized cell). I pasted to the same Publisher file as I always have and some sections of the worksheet pasted like always while others had the larger cells (on the same Publisher file).

I tend to think that it is a problem with the clipboard. Has anyone else ever run into this, and can anyone advise.

Thank you very much in advance,

Vaughan Martin


Hi Guys,

I think I've got a fairly simple request.

Basically I want to move columns G & H to the last column (in this case " O & P") and then delete the columns G&H, so they don't leave any blank cells open, and the rest of the columns moves 2 columns to the left.

- Select & Cut columns G & H
- Paste after last column
- Delete Columns G & H to remove the empty columns

Thanks all, much appreciated


Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?


Hey I am fairly new to programming in excel. I need to create a macro that will go to multiple other spreadsheets (files) and gather certain data (from cells I27, j27, k27) and bring it back to my current spread sheet (and paste to cells c65, d65,e65). For example every day the same spreadsheet is filled out and saved by the date (11-18-10). I need to be able to create a one spreadsheet to copy data from certain cells in each of those sheets and copy them into a yearly spreadsheet with each row as a date and paste into cells c, d, e of that date row. I can use either a button or a GUI. I was trying just to use a button that would go by row and update per date.


Does anyone know how to do this? I have lots of columns that need to be stacked into a single column (I do not want to combine or concantenate)
eg put all data in column B, column C, column D, column F and column E and stack them one under each other into a single column in column A. I have many many columns each with approx 500 rows of cells.All columns are in the one sheet.
Any ideas appreciated.



How can I mirror a spreadsheet so that it will look like another without switching back and forth between the 4 sheets pressing = and then clicking the same cell in the other spreadsheet?

What I have is 4 spreadsheets that are worked on by different people at the same time. What I want to create is 1 workbook with 4 tabs that just show the information being put in the 4 spreadsheets. What would really be cool is if there was a way to see the information being put into one of the spreadsheets in real time from the workbook with the 4 tabs!


I am working on a spreadsheet, where the developer had changed the cell format for every cell on most of the spreadsheets. For example, instead of a white background, they changed the entire sheet to have a green background. On one of the sheets, the data on the sheet is only in the range A1:N59. I am trying to set the cell formatting for the columns starting in O and the rows starting in 60 back to the default. I have copied and pasted a cell with the defaut format into those columns and rows. However, when I save the file after doing that, the size of the file actually gets much bigger. It goes from 16 MB to 52 MB. Does anyone know why this is happening?


hey, new to the forum. in need of help. i have data in two spreadsheets. one is in order with column A being numbered 1-1000 give or take a few and column B is a list of vendor part numbers and column C is empty. 2nd spreadsheet is the same list of vendor part numbers in random order in column B, column A is empty and column C is our internal part number which is different than the vendor part#. i need to sync. up the 3 columns in 1 sheet. the problem is that the 2nd sheet is missing about 100 part#s so i can not simply sort them by part# and copy and paste column C over to the first sheet. any ideas would be great. thanks. i own an automotive accessory company so anyone who can help me out with this issue i could hook you up with some stuff for your ride. rims, tires, floormats, ect. you can email me at billy@bonesenterprises.com. thanks again


The database that I'm working on divides patient names into three separate columns: lastName, firstName, midInit. My department has several old spreadsheets that have data my boss suddenly wants included in my database. The trouble is, these old spreadsheets have the patient's last name, first name, and middle initial entered into the same column. There are at least 200 hundred names in these spreadsheets. I absolutely do not want to go back and separate the names into separate columns by hand.

Is there any way to get Excel to take the names entered into one cell and split them into three cells?

I've attached a spreadsheet example that shows what I need to do.


My co-worker have multiple Excel files (1 worksheet per file); all worksheets have the same column headers and structure; she needs to combine all these worksheets from the multiple files into one worksheet with all the data stacked one below the other.

Please help if you know of an easy way to do this.

If would be great if you could provide a macro code and instruction on how to use the macro.


Hi. I am having an issue with 2 linked spreadsheets. I need to have the same data in both sheets...I linked them so i only have to enter data in one of them to save time. Occasionally i need to insert a new row in the first sheet to keep similar items together. Is it possible to have that new row (and the data i enter in it) automatically created in the second spreadsheet? At this time I have to create a new row and then link the cell in the second sheet manually every time. Is there a quicker way? Thanks in advance...