1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Spreadsheet Data Copying/Combining

Discussion in 'Business Applications' started by howdy123, Dec 27, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. howdy123

    howdy123 Thread Starter

    Joined:
    Dec 27, 2001
    Messages:
    1
    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)
     
  2. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    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)
     
  3. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    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.
     
  4. Whinyguy

    Whinyguy

    Joined:
    Dec 8, 2001
    Messages:
    270
    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.
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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?
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/63132

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice