Excel Spreadsheet Data Copying/Combining

I am trying to combine information from 2 spreadsheets into a new spreadsheet. Basically, I am trying to link company information from both spreadsheets to create a new spreadsheet with the combined information (where available). The problem is that not all the companies from "Loc.xls" are listed on "Fin.xls". "Fin.xls" is a more detailed listing of financial data with more companies. "Loc.xls" has addresses of companies (all are listed on the other spreadsheet, but "Fin.xls" has other companies too). Both spreadsheets have an alphabetical listing of companies, but with different information I would like to combine for the companies that are listed on both spreadsheets (the companies listed only on "Fin.xls" will just have empty cells for certain information). I am hoping there is an easier way to combine information rather than by tediously cutting and pasting because I have a rather large list. What would you suggest to simplify this task?

(note: I created a small version of the 2 spreasheets as a bmp file to give a general visual representation)

 

you'll be doing some sort of copy, paste special, link to get info from the 2 sheets into one

try:
highlighting and dragging down all the rows from loc.xls
copy

in new spreadsheet
starting at cell a1
paste special, link

now you have all the loc.xls info linked to the new spreradsheet

do the same with fin.xls but start in the a cell down from the last loc.xls row

now you can delelte the rows you don't want, and columns too
it won't affect the links to what's left

needless to say (but i will anyways ) the layout will need to be the same so the column headers/titles all reflect the info in the column

note: where there's a blank cell / empty info from the source spreadsheet, you'll get a 0 in the linked spreadsheet and may have to back to the source and hit the spacebar in the offending cell to 'empty' it in the linked sheet

your .bmp file didn't show so i may be off the mark a little - or a lot! (try saving it as a .jpg and attaching it to a reply)

 

Sounds like you are asking more from your spreadsheet than it can deliver. It might be time to migrate all that data into a centralized database that everyone can access. You wnat ot be able to keep the data consistent between the locations and 2 copies of a spreadsheet isn't going to cut it. You could import everything from both spreadsheets into Access and sort and filter it down to the unique records for companies and then copy it into a new spreadsheet.

 

If you are sophisticated enough in Excel you can try to work with the Consolidation feature which is under "Data", "Consolidate". It will allow you to specify which is the column or row of labels you intend to use that will be matched between the two files. Attached is the help information supplied by Excel on how to do it:

Guidelines for specifying source areas for a consolidation
When you consolidate data, you specify the source areas of the data either in 3-D formulas or in the Reference box of the Consolidate dialog box (Data menu, Consolidate command). The source areas can be cell ranges on the same worksheet as the consolidation table, on different worksheets in the same workbook, or in different workbooks or Lotus 1-2-3 files.

Before you begin
Name your source areas To make source areas easy to keep track of, name each range and use the names in the Reference box.

Referring to source areas
On the same worksheet When the source areas and destination area are on the same worksheet, use cell or range references or names.

On different worksheets When the sources and destination are on different worksheets, use sheet and cell or range references or names. For example, to include a range named Budget from the Marketing worksheet in a consolidation on the Summary worksheet, enter Marketing!Budget.

In different workbooks When the sources and destination are in different workbooks, use book, sheet, and cell or range references or names. For example, to include a range named Sales from the Eastern Region worksheet in the 1996 workbook in the same folder, enter the following:

'[1996.xls]Eastern Region'!Sales

In different storage locations When the sources and destination are in different workbooks in different locations, use the full path, book, sheet, and cell or range references or names. For example, to include a range named Revenue from the February worksheet in the Sales Department workbook in the Budget Worksheets folder, enter the following:

'[C:\Budget Worksheets\Sales Department.xls]February'!Revenue

Note You can omit the sheet names from the references if your workbook has names that you assigned, rather than labels used as names by Microsoft Excel. For example, '[1996.xls]'!Sales or '[C:\Budget Worksheets\Sales Department.xls]'!Revenue.

Tip To enter a source reference without typing, click in the Reference box, and then select the source area. To select a source area in another workbook, click Browse. To clear the Consolidate dialog box out of the way temporarily while you select the source area, click Collapse Dialog .

Consolidate data by using 3-D references
On the consolidation worksheet, copy or enter the labels you want for the consolidated data.

Click a cell that you want to contain consolidated data.

Type a formula that includes references to the source cells on each worksheet that contains data you want to consolidate.

Repeat steps 2 and 3 for each cell where you want to consolidate data.
Tip To enter a reference in a formula without typing, enter the formula up to the point where you need the reference, and then click the cell on the worksheet. If the cell is on another worksheet, first click the worksheet tab, and then click the cell.

Consolidate data by position
Click the upper-left cell of the destination area for the consolidated data.

On the Data menu, click Consolidate.

In the Function box, click the summary function you want Microsoft Excel to use to consolidate the data.

In the Reference box, enter a source area you want to consolidate.

Click Add.

Repeat steps 4 and 5 for each source area to consolidate.

To update the consolidation table automatically when the source data changes, select the Create links to source data check box.
To create links, the source and destination areas must be on different worksheets. Once you create links, you cannot add new source areas or change the source areas that are included in the consolidation.

Note When you consolidate by position, Microsoft Excel does not copy the category labels in the source areas to the destination area. If you want labels for the destination worksheet, copy them or enter them manually

Consolidate data by category
Click the upper-left cell of the destination area for the consolidated data.

On the Data menu, click Consolidate.

In the Function box, click the summary function you want Microsoft Excel to use to consolidate the data.

In the Reference box, enter a source area you want to consolidate. Make sure to include the data labels in the selection.

Click Add.

Repeat steps 4 and 5 for each source area you want to consolidate.

Under Use labels in, select the check boxes that indicate where the labels are located in the source area: either the top row, the left column, or both.

To update the consolidation table automatically when the source data changes, select the Create links to source data check box.
To create links, the source and destination areas must be on different worksheets. Once you create links, you cannot add new source areas or change the source areas that are included in the consolidation.

Note Labels in a source area that do not match any labels in the other source areas result in separate rows or columns when you consolidate data.

 

Make the columns in spreadsheet 1 and in spreadsheet 2 be the same. You may need to insert blank columns into either or both to accomplish this. Basically, make the column headings match for both spreadsheets. You MUST have column headings on spreadsheet 1 on all columns, even if currently blank.

Now, copy all the cells from spreadsheet 2 to the bottom of the cells in spreadsheet 1.

After pasting, KEEP THE CELLS SELECTED, don't click off of the selection. Now, format those cells to have red text or something that will offset them for you.

Now, you should be able to sort by the company names or other relative column.

Insert a blank column to the right of the company name column.

Type the following formula in and copy it down (assumes your company names being in cell A2, and the new blank column you inserted is B)

In B2, type =A2

In B3, type =if(a3=a2,"dupe","")
(copy that formula if you want)

Copy B3 down all rows.

Now select B2 and all cells below it.

Hit Copy.

Hit Edit-Paste Special, Values, OK

Now, sort by column B in DESCENDING order and all the "dupes" will come to the top. Select all these "dupe" rows and delete them.

You should now have one sheet with all the companies listed, and you'll still be able to tell what source they came from.

Hope this helps. (This is keeping it fairly simple without having to know everything about Excel.)

There is also some fancy fixes, like:

download from Aaron Blood:

delete_if.zip file from http://geocities.com/aaronblood/pages/vba.html

or this one from Chip Pearson:

http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

Let us know how you work it out, okay?