Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Is VBA a valid option? –  nixda Aug 22 '13 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 '13 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 '13 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 '13 at 19:43
    
Couldn't you just sort all of them by date and then remove duplicates? –  Voitcus Aug 23 '13 at 13:16
show 4 more comments

4 Answers

up vote 2 down vote accepted

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."

share|improve this answer
2  
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 '13 at 0:40
add comment

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. ;-)

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 4:01
add comment

@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

share|improve this answer
add comment

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.)

share|improve this answer
    
I prefer to avoid third-party tools, but thanks. –  Iszi Mar 1 '13 at 19:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.