Excel: How to combine two sheets into one

Forums › IT Pro and developers › Excel: How to combine two sheets into one

tonyhughes

Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 5

Mod Emeritus

Trusted

Lifetime subscriber

Topic # 26546 25-Sep-2008 15:04

I have Googled this, but Google is littered with too much crud on the subject of joining/merging worksheets.

Lets say I have three worksheets (Sheet1 and Sheet2 and Sheet3).
Sheets 1 and 2 are manually updated with data.

I want sheet 3 to automatically gather the data into itself. It doesnt have to do any error or dupe checking.

Does anyone know how? I seem to be able to do some complex things in Excel, but basic stuff breaks my brain.

I have tried the 'Consolidate function, and selected the ranges on both sheets, but just get no output at all in Sheet3 (which I am initiating the consolidation from).

Sheet1
   a       b    c
1 bob    napier
2 fred    hastings
3 sally   gore

Sheet2
   a    b    c
1 mike auckland
2 joe     hamiltron
3 ian     opotiki

Sheet3
   a       b c
1 bob    napier
2 fred    hastings
3 sally   gore
4 mike auckland
5 joe     hamiltron
6 ian     opotiki

(obviously the above is a tiny amount of false data)

AndrewTD

291 posts

Ultimate Geek

Trusted

Reply # 167076 25-Sep-2008 15:21

Hi,

I just typed a big long reply in here suggesting you use the = function to do this, and enumerated examples.
Then I clicked off the page and lost it!

Instead of me typing it all in again - how about you call me on 027 5528 005, and I can talk you through it.
It's fairly straightforward.

kind regards Andrew TD

mdf

1998 posts

Uber Geek
+1 received by user: 590

Trusted

Subscriber

Reply # 167085 25-Sep-2008 15:51

Agree with the previous response.

If the data on sheet 1 and 2 is fixed in terms of number of items (i.e. there is no row 4 to be added later in sheet 1), just use the '=' function. i.e. [thiscell] =Sheet1!A1. Type "=" in the formula bar then just navigate to the sheet and cell you want. Excel should do the rest itself. You can then just autofill this out for the rest of your range.

If you need something a bit more complicated, I have used the 'lookup' function for this sort of thing. I understand that this may have changed for Excel 2007 (but is still provided for "backwards compatibility") and there is probably a new (better? worse?) way of doing it, but I am not all that familiar with Office 07 so not really sure.

Cheers

tonyhughes

Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 5

Mod Emeritus

Trusted

Lifetime subscriber

Reply # 167086 25-Sep-2008 15:53

mdf: Agree with the previous response.

If the data on sheet 1 and 2 is fixed in terms of number of items (i.e. there is no row 4 to be added later in sheet 1), just use the '=' function. i.e. [thiscell] =Sheet1!A1. Type "=" in the formula bar then just navigate to the sheet and cell you want. Excel should do the rest itself. You can then just autofill this out for the rest of your range.

If you need something a bit more complicated, I have used the 'lookup' function for this sort of thing. I understand that this may have changed for Excel 2007 (but is still provided for "backwards compatibility") and there is probably a new (better? worse?) way of doing it, but I am not all that familiar with Office 07 so not really sure.

Cheers

A large amount of rows exists in each, and will be updated by (l)users from time to time.

AndrewTD

291 posts

Ultimate Geek

Trusted

Reply # 167094 25-Sep-2008 16:11

Having lots of cells referenced using the "=" function is no problem, and if they are in contiguous blocks, then using the autofil or simple cell range extension makes it very easy to extend the referenced range after just typing in one ow two entries.

I do this sort of thing all the time - I have a sheet for each product type, and various entries for units and revene per month per product. On each sheet I have totals cells.

I then have a "Totals" sheet at the front of the workbook, and refer (using the "=" mechanism) to both the product and month titles, and the asscoiated product revenues / units totals.

So I get high level totals on the Totals sheet, and more detailed totals on each product type sheet.

It takes very little time/effort to make up the Totals sheet - whose individual cell contents are almost all references to cells in the other sheets.

Each Row or columns of cells in the Totals sheet is done simply by filling in the left/top most cell, and then using the cursor, extending (autofilling) the cell across/down.

kind regards Andrew TD

mdf

1998 posts

Uber Geek
+1 received by user: 590

Trusted

Subscriber

Reply # 167108 25-Sep-2008 16:46

Agreed again.

I have had a look at the "consolidate" option, and I do not think this will do what you are looking for. This is effectively a shortcut function that lets you select some way of manipulating data - e.g. summing, averaging etc. data across worksheets. So it will turn [manycells] into [onecell].

As I understand it, you want to preserve the source data, just redisplay it in a different format.

I still think the "=" function will be the simplest way of doing this; it should not be affected by the raw amount of data.
The other (more complicated) way is the lookup function, where you can get excel to lookup a data reference and display the info in either the next (vector from memory) or last (array) column.

You could also try a pivot report (menu -> data -> pivottable and/or pivotchart on versions I'm familiar with). This purports to be a wizard, but can be a bit fiddly to get operating right. You would need to play around until you got the info in the state you wanted it.

If there's any more info about what you want/what problems you have with '=', let us know as there is usually some form of workaround.

alasta

4142 posts

Uber Geek
+1 received by user: 755

Trusted

Subscriber

Reply # 167110 25-Sep-2008 16:52

If the individual lists have a variable number of rows and your users are not particularly Excel-savvy then you might want to think about writing a simple macro to sequentially examine each row in the individual worksheets and add them to the consolidated worksheet.

This is pretty straightforward to do if you have basic programming skills.

TinyTim

902 posts

Ultimate Geek
+1 received by user: 49

Trusted

Reply # 167321 26-Sep-2008 12:31

Tony have you got a solution yet?

Here's an option you can use if the data in the first two sheets is contiguous:

=IF(ROW()<=COUNTA(Sheet1!$A:$A), Sheet1!A1, OFFSET(Sheet2!A1, -COUNTA(Sheet1!$A:$A)+1, 0))

(This formula goes in the top left cell of sheet 3. Assumes data starts in A1 of the first two sheets. It copies the row headings from the first sheet, and the '+1' means it's not copied from the 2nd sheet. Copy this down for each row required and across for each column. You could make it more sophisticated to check for cells beyond both tables.)

Here's an option using VBA - not very sophisticated, assumes all data is in contiguous blocks (starting in A1). Also copies row headers from 2nd sheet so can be improved.

Sheets("sheet1").Range("A1").CurrentRegion.Copy Sheets("sheet3").Range("a1")
Sheets("sheet3").Range("A1").CurrentRegion.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Sheets("sheet2").Range("A1").CurrentRegion.Copy
Sheets("sheet3").Paste

tonyhughes

Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 5

Mod Emeritus

Trusted

Lifetime subscriber

Reply # 167349 26-Sep-2008 13:56

Thanks for replies.

I will tackle this on Monday. Marketing tasks have got the better of me today, then I'm off to see a man about a dog this afternoon.