+ Reply to Thread
Results 1 to 6 of 6

Merging data from separate sheets.

  1. #1
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Merging data from separate sheets.

    We have a couple of sheets with data.
    Each with about 100 rows of data, containing various information, spread out over 57 columns.
    I have been updating this sheet with information but now the powers that be are starting to send updated versions of the document out.

    These newer versions may contain new entries or some removed. My issues is merging this data together.
    Currently, I have to open both spreadsheets and manually go down each row and see what is new and what has been removed. It is tedious to say the least.

    Is there a trick, a way, to merge this information in such as a way as:

    If the newer spreadsheet has extra entries that don't appear on the original, add the row.
    If the newer spreadsheet is missing a row from the original, delete that row from the original.
    All this can be based off the first column if required since that contains the name and never will be modified, it is a good achor point.

    I would rather avoid using VBA, but if required I can, I would run it from another spreadsheet as I don't want to make our master sheet a macro enabled workbook, for various reasons.

    Can anything be done to help?

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Merging data from separate sheets.

    Take a look at the MATCH() function. If there is some key such as a part number or customer ID you can use the following:

    =ISNUMBER(MATCH(Cell A,RangeB,0)) will be true if the value in cell A is in range B. It will be false otherwise. You can also do the reverse: check Range A for values in B.

    So if A is the old report and B is the new report - Values in A not found in B have been deleted. Values in B not found in A have been added.

    I have a question. If you are going to delete stuff from A and add stuff from B so as to make A look like B, why not use B to begin with?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    12-18-2016
    Location
    London
    MS-Off Ver
    Microsoft 2013
    Posts
    145

    Re: Merging data from separate sheets.

    =INDEX(Sheet1!E:E,MATCH(A2,Sheet1!A:A,0))
    or
    =VLOOKUP(A2,Sheet1!A:E,5,FALSE)

  4. #4
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Merging data from separate sheets.

    Quote Originally Posted by dflak View Post
    I have a question. If you are going to delete stuff from A and add stuff from B so as to make A look like B, why not use B to begin with?
    Good question, I should of addressed this in my initial post.

    The sheet we use has data entered already. But some cells are populated by myself over time. If I just replaced the entire sheet, I would lose those values.
    To be fair, I blame the powers that be. Rather than send just updates, they send out the entire sheet again and expect me to work out what is new, what is missing.

    So imagine someone gave you a sheet full of data. You gradually fill in blank cells with info. Then they give you the same sheet again and say somewhere in that sheet are new rows of data and some no longer needed.
    Beleive me, just replacing my sheet with theirs is preferred.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Merging data from separate sheets.

    Actually, you are trying to make Excel behave like a database which for a simple application like this doesn't require a lot of coding. If you are willing to attach sample workbooks with non-sensitive data (make sure the column headers are the same or at least the column header that contains the key element), then I'm willing to take a shot at what you suggested as an automated process: have a third workbook open the other two and do the updates.

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Merging data from separate sheets.

    Here is the file with dummy data. Fill in the source and destination path names and file names. The source file is the one your boss sends you. The destination file is the one you maintain. I included my test files in case you want to play with it.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro needed to Concatenate data in 2 separate tables of 2 separate sheets
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-08-2015, 02:02 PM
  2. Merging Data from Separate Tables
    By william. in forum Access Tables & Databases
    Replies: 2
    Last Post: 11-02-2012, 05:07 PM
  3. Merging Data from Separate Tables
    By william. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2012, 01:34 PM
  4. Merging: displaying which sheet data comes from when merging sheets
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-12-2012, 12:20 PM
  5. Merging Data from 2 Separate Spreadsheets
    By MrBlund in forum Excel General
    Replies: 1
    Last Post: 06-15-2012, 04:05 AM
  6. Excel 2007 : Merging Separate Sheets !!HELP!!
    By Rooney83 in forum Excel General
    Replies: 0
    Last Post: 10-29-2010, 10:54 AM
  7. Replies: 3
    Last Post: 08-12-2010, 03:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1