Sort By Last Updated Column and Remove Duplicates

NB: This is a follow-on question from this one
https://www.experts-exchange.com/questions/28640366/Macro-to-open-file-into-an-existing-worksheet-and-sort-by-column-MAC-2011.html

AckeemK: In the previous question, it seemed that at least some imports would clear the existing content of the 'Current' tab while others wouldn't. Is this still the desired behaviour? Or do you want to append the import rows to the bottom and then sort by 'Last Updated' - if you clear rather than append you will lose visibility of cases that are no longer present in the latest import.

I'd suggest storing the previous data on a 'Previous Import' tab and using vlookup to compare current to previous (on the two columns you mention). They could then be conditionally formatted based on the comparison result. Does this sound like what you want to achieve.

Also note that the datetime format appeared to vary in your previous question. Do you need all dates converted to values that Excel can sort on?

Yes, if I am able to have the report store previous data on 'Previous Import' tab using vlookup, along with conditionally formatting implemented, that can achieve the end goal. Lastly, each time a new file is imported, would it be possible to have a macro run every time that moves the data within the 'Current' tab to the 'Previous Import' tab and perform the vlookup/conditional formatting you mentioned previously occur?

Also, if all dates are converted, I believe that will allow the sort to run more smoothly. I've attached two samples of raw outputs (files that are imported) from two different platforms to provide a better understanding. If you check the 'Menu' tab, you'll see a button that says 'Import .csv file(s)' which takes you through the processes currently in place.
customer-engineers-view-2015-03-23-1751.
SEARCH-SR-PRODUCT-GROUPS--5-.csv
DSE-Carelog-Report.xlsm

OK, I can see that you've modified the current sheet layout since the last question.

You now have 3 different import formats, but none of them are  mapped correctly to the 'Current' sheet layout.

I need to ask you some more questions:
1. Do you need all the formats to be mapped to the same layout on the current tab?
2. Do cases (SR numbers) get reported on from more than one source?
3. For all this effort, wouldn't you rather store the history for each SR number? I'm starting to think that you'd be better off with either separate tables for imports from each of the 3 sources, so that you retain all of the columns from that particular source and can drill down into the history of each SR. Your Excel dashboard would be a richer tool then.

Maybe I haven't understood your business requirements. If you could comment on the questions above it would help. At the moment I struggle to think that Excel is the best tool for the storage of the information, though possibly the best for dashboard/presentation.

Ultimately, I am looking to build a user-friendly as well as interactive standardized reporting tool which is able to capture high level details of particular SR's the support engineer is working currently on or has closed in the past. Being that the engineer can be pulling this data from possibly three different platforms depending on the customer, the fields that are common across all three platforms are SR/Number, Problem/Summary/, Severity, Status, Product, Owner/Assignee, Organization and Last Updated. Being able to pull in these fields from any imported file would be a great starting point. Attached is one of the current report's they have in place for tracking SR's with a customer to get a idea. I want the user (engineer) to be able to input status updates and any other notes as seen in the attachment and have them saved. I want to later create different charts and pivot tables based on both Open and Closed SRs, Severity, and several others as you can see on the 'Menu' tab to provide trending as well as predictive analysis. I am also looking to include a API from one or more of these platforms to provide more efficiency and features in later versions of this reporting tool. If there is another tool that can capture this information real-time and more abundantly or that can go hand-in-hand with Excel to accomplish the end-goal, that would be great to know. I want to release a plain version of this tool soon so I wanted to get something out there to show a prototype.
03.20.2015-Pivotal-SR-Report-v4.xlsx

Several other key aspects to note are compatibility with both Windows and Mac OS, ability to build customer presentations (as seen in the 'Menu' tab) and be able to view information at the business unit and product level. If you know of a better possible tool which can be modified and updated to accomplish these particular requirements, that would be most appreciated.

Thank you for the help, it is greatly appreciated. I am looking forward to what I can do for future releases but this is a great start.

I am a little confused with this part of the macro. Am I uncommenting the first test where we perform the 'id/SR number' writing out the rest of the code only for the ones which title may vary as shown below?

Set destRange = ThisWorkbook.Sheets("Import").UsedRange
            'Clear content of Current sheet apart from headers
            If testval = "SR Number" Or testval = "ID" Then
                destRange.Offset(1).Clear
                Set destRange = destRange.Resize(1, 1)
            End If
            If testval = "Subject" Or testval = "Problem Summary" Then
                destRange.Offset(2).Clear
                Set destRange = destRange.Resize(2, 2)
            End If
                 If testval = "Owner" Or testval = "Assignee" Then
                destRange.Offset(4).Clear
                Set destRange = destRange.Resize(4, 4)
            End If
     If testval = "Product Group" Or testval = "Organization" Then
                destRange.Offset(7).Clear
                Set destRange = destRange.Resize(7, 7)
            End If
 
Also you mentioned implementing a vlookup as well as conditional formatting to compare the current tab with what is imported and highlight changes made to a particular SR. Also, I noticed that once the fields in all tabs are cleared (starting with only headers, brand new report) it doesn't seem to import multiple files below each file that was imported.

I commented out those lines because the the conditional  (starting at line 52 of my listing above) determines which columns each column in the import file gets written to.

I didn't continue with the conditional formatting because step 1 is to get data in consistently. It's not yet clear to me what will end up on the 'Current' sheet and how best to generate that from the import and possibly the repository of previous imports. It seemed unwise to go further without a fleshed-out example of what data should be on each sheet - ie. I did do some comparison formulae when working on it, but there was not much in common between the import and what was already on the 'Current' sheet.

Hi Simon,

I know you helped me out earlier with macros to build out this reporting tool. I had a question. I added a column into the report and now I am receiving a error when I try to do a import about a mismatch. It is pertaining to the function but I am not sure how to fix that. I've attached the tool as well as the file I am trying to import. Can you take a quick look here? I appreciate it a lot.
DSE-Carelog-Report-V1.xlsm
EMC-IT-2015-05-05.xlsx