7

I have several spreadsheets with information I need to consolidate, each with different (and sometimes overlapping) portions of the final data set I'm hoping to create. Typically, combining spreadsheets with similar data is easy to do by dumping all the data into one sheet and using Remove Duplicates. However, I want to make sure that the line items which are left represent the latest version of the duplicates.

I will be importing the following data from each spreadsheet:

  • A unique identifier field, (UID) which identifies the subject of the line item and will be used to spot duplicates between the input sheets.
  • A status field, (STATUS) which will have information about the item called out in the unique identifier field.
  • A date field, (DATE) which will contain the date the imported data was originally written.

What I need is for my output to contain only one line item for each UID, and have data from the STATUS field which matches the most recent DATE for that UID from the input sheets.

What is the easiest way to do this in Excel?

9
  • Is VBA a valid option?
    – nixda
    Aug 22, 2013 at 16:37
  • @nixda Not preferable, but I wouldn't be uninterested either. Primary criteria is that it should be natively supported in a bare-bones Windows 7 + Excel 2010 build, and the results should be 100% reliable (or as close to 100% as possible). Only reason I'd rather not do VBA is pretty much because I don't really understand it enough to write it myself (and therefore also not enough to really comprehend what a script is meant to do). So, if that's your solution, please make sure your script is thoroughly annotated for the non-initiated.
    – Iszi
    Aug 22, 2013 at 18:23
  • I have an idea how it would be doable with a minimum f VBA. Can I have a sample Excel workbook from you?
    – nixda
    Aug 22, 2013 at 18:38
  • Can't work one up right now, but the above post pretty much spells out the essentials. The UID will be of format roughly similar to ###.###.###.###-#####, the STATUS will be free text entry, and the DATE will be formatted as YYYY/MM/DD.
    – Iszi
    Aug 22, 2013 at 19:43
  • Couldn't you just sort all of them by date and then remove duplicates?
    – Voitcus
    Aug 23, 2013 at 13:16

6 Answers 6

8

I don’t know whether this is guaranteed to work, but it seems to work for me (in very small-scale tests in Excel 2007): take the combined data sheet, and sort it in reverse order by DATE, so the newest rows are above the older ones.  Then Remove Duplicates.

This site confirms this behavior: "When Excel scans the table, it removes any subsequent record that has the same Product ID as an earlier record, even if the rest of the data is different."

1
  • 3
    I took the liberty of adding a reference. I also confirmed this with a small-scale test on my own in Excel 2010.
    – Dane
    Aug 26, 2013 at 0:40
3
+200

Here is a several-step solution, assuming you can do some of this manually, and don't need a single completely automated solution: (and if you do, I'm sure you can take it from here...)

  1. Excel is not a database.
  2. Dump all the data into a single sheet. (For the sake of example, I am assuming that you have UID in column A, DATE in column B, and the STATUS in C).
  3. In a second sheet, perform a Remove Duplicates on the UID column only. (e.g. copy filtered uniques only, or copy the whole column then perform a standard Remove Duplicates).
  4. In the DATE column, add the following Array* formula:

    {=MAX(IF(DataSheet!A:A=A1, DataSheet!B:B))}

    This basically selects the latest date for each UID. (This is for the first row of course, make sure to fill all the rest of the rows with A1, A2, ... )

  5. In the STATUS column, add the following Array formula:

    {=INDEX(IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,DataSheet!C:C)),MATCH(TRUE,IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,TRUE)),0))}

(Again note the first row, fill the rest).

This one is more complex, let's break it down:

IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,DataSheet!C:C))

This array formula simply performs the equivalent of an SQL WHERE clause with two conditions: for all rows that match both the UID (A column) and DATE (B column), return the row's value in the C column (STATUS).

MATCH(TRUE,IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,TRUE)),0)

The first formula should have been good enough, but since we don't have a way to pull out only the non-null (or non-FALSE) value, and Excel does not have a COALESCE formula, we need to resort to a little indirection.
The MATCH formula searches the array returned by the IF (same conditions as above, but simply returns TRUE if it is a match), for the first TRUE value. The 3 parameter, 0, demands an exact match.
This formula simply returns the index of the first - and only - row that is a match for the previous conditions (matching UID and DATE (which was the maximum date that matches the UID)).

{=INDEX(IF(see above), MATCH(see above))}

Now it is simple enough, to take the index of the matching row from the MATCH, and pull out the corresponding STATUS value from the IF array. This returns a single value, your new STATUS, which is guaranteed (if you've done all these steps correctly) to be from the latest date for each UID.

6 Excel is not a database.


* FOOTNOTE: if you are not familiar with Array formulas (though I think you are), see this: basically you enter the original formula that should result in an array of values (without the squiggly {}), then press CTRL+SHIFT+ENTER. Excel adds the squiggly {} for you, and calculates all the values as an array.

* FOOTNOTE #2: Seriously, EXCEL IS NOT A DATABASE. ;-)

5
  • NOTE: On second thought, I think - but not thoroughly tested yet - that you can simplify this even further: the entire first set of IF() (the whole first parameter to INDEX()) could probably be completely replaced by just a simple DataSheet!C:C, since the indexing based on MATCH would probably work just as well. This is a result of building up the formula from the bottom...
    – AviD
    Aug 22, 2013 at 22:05
  • Btw by using column notation instead of specific range notation (B:B vs. B1:B2500) the performance is seriously degraded, since Excel needs to build, compare and collate arrays for the entire length of nearly infinite rows (okay, not quite infinite, but quite a lot). I used this for clarity of example.
    – AviD
    Aug 22, 2013 at 22:08
  • NOTE 2: Since the nested IFs inside the MATCH only return TRUE (if they both match), you can simplify this even further by replacing the nested IFs with a simpler AND(). So the final formula would actually be the much cleaner: {=INDEX(DataSheet!C:C,MATCH(TRUE,AND(DataSheet!A:A=A1,DataSheet!B:B=B1),0))}
    – AviD
    Aug 25, 2013 at 19:16
  • Tested the solution in the answer and it does work, but seems to be fairly resource intensive. Populating it down just a handful of rows took a rather noticeable moment to process. I'm not sure I even want to imagine running it against the hundreds/thousands of rows my real sheet will have. I guess this might have something to do with the column notation, but I use column notation in a lot of other fairly complex formulas (albeit not involving arrays) without generally having such issues. And yeah, I know it's also probably because Excel is not a database.
    – Iszi
    Aug 26, 2013 at 0:27
  • Awarded bounty here for inventiveness, transparency of method (i.e.: I don't need a third-party reference to tell me this will give reliable results) and for keeping the solution self-contained in one workbook. This does not scale up very well though, as it is very processor-intensive.
    – Iszi
    Aug 28, 2013 at 4:01
1

@AviD is correct, in that Excel isn't a database, but you can import your data into another spreadsheet via a Microsoft Query data source. It's a bit ugly, but will give you access to a SQL statement, which should enable you to get what you want.

  1. In a new spreadsheet, go to the Data tab an in the Get External Data group select From Other Sources... and From Microsoft Query.

enter image description here

  1. Choose Excel Files and select your saved data
  2. If you get an error saying that it can't find any visible tables, just click OK and in the Options dialog box select System Tables from the show list. That should then give you access to the sheets in your worksheet

enter image description here

  1. Add your UID, Status and Date columns to the query

enter image description here

  1. Next... Next... Next and choose View Data or edit query in Microsoft Query and select Finish

enter image description here

  1. Now you get worksheet that looks a bit like an early version of Access.

enter image description here

  1. Click the SQL button and you get access to the query itself, which I think you need to change to something like the below (using a GROUP BY and MAX to get the latest date):

    SELECT Sheet1$.UID, Sheet1$.Status, Max(Sheet1$.Latest) FROM C:\Users\rgibson\Desktop\Book8.xlsx.Sheet1$ Sheet1$ GROUP BY Sheet1$.UID, Sheet1$.Status

    1. You can them close the query and choose where to import the data to:

enter image description here

0

If you're willing to consider a third-party tool, I would recommend BeyondCompare. It's easy to use, it has a very generous trial before buying, fairly low price, and is nice for both comparing and merging many different file types and directories, including Excel. You can copy individual lines from one file to the other.

(I'm a BC user, and have nothing to do with the company.)

0
0

If all else fails combine all the data onto one sheet sort by date (make sure formated the same way) then copy the most current date to new spreadsheet then the next most current date so on. Go to the Data tab remove duplicates. Since excel keeps the 1st record entered into the spread sheet and removes the next occurring duplicate this should work.

0

Pivot the data sheet. Put the Unique ID in the Row and the date field in the value and set the value to be the Max. This will generate a two column table with the UID and the most recent date asociated with that UID. Format the value column in the pivot table to be exactly equal to the value of the date field in the data tab.

Then I copied the pivot data to a new tab - I called it "date resolver" - and I created a named range for the two Columns A and B and I called the named range "date_selector" you can call it whatever you want. I actually set the range to $A:$B so it would be ready if I came back and added additional rows later.

Then I went back to the data table and added two columns. A V lookup that keyed on the UID and returned the max date from the "date_selector" range and and then a simple if statement to keep the rows where the date from the "date_selector" range matched the date in the record.

My unique ID is in column M The date is in column H The VLOOKUP date value is in Column A

VLOOKUP =VLOOKUP(M2,date_resolver,2,FALSE)

IF STATEMENT =IF(A2=H2,"KEEP","DELETE")

In the future all I need to do is refresh pivot, paste pivot results to the date_resolver tab, paste down my formula rows, and delete the delete rows.

(thinking deep inside the box)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .