Sort By Last Updated Column and Remove Duplicates

I am trying to figure out a macro to sort the rows based on the latest updated (newest to oldest) and remove the duplicates once the file is imported into the 'Current' tab. Once the duplicates are removed (signifying nothing has changed about a particular SR number), there may be some rows with the same SR number as shown in the attachment but one may be more recent than the other with different notes (SR Status Update & SR Resolution Summary columns) by the user. Is there a way to highlight these rows so that the user can see that things have changed for this particular SR number based on their last update?
DSE-Carelog-Report.xlsm
AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
NB: This is a follow-on question from this one
http://www.experts-exchange.com/OS/Apple_Operating_Systems/OS_X/Q_28640366.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?
AckeemKAuthor Commented:
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?
AckeemKAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SimonCommented:
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.
ImportFormats.png
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.
AckeemKAuthor Commented:
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
AckeemKAuthor Commented:
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.
SimonCommented:
Hi AckeemK, I would suggest a database as the repository to import all the csv files into, with a forms-based front end. However, that might be quite a way down the development road. Excel is fine for rapid development and prototyping.

All I've done so far is to update the import routine (writing to an Import tab) to map the three source files to your desired layout. This also includes a function to convert the ISO dates to Excel dates, though this doesn't retain the timezone offset.
Sub Select_File_Or_Files_Mac()
    Dim MyPath As String
    Dim MyScript As String
    Dim MyFiles As String
    Dim MySplit As Variant
    Dim N As Long
    Dim Fname As String
    Dim mybook As Workbook
    Dim filePath As Variant
    Dim dataRange As Range 'Range in the import sheet to get data from (excludes header row)
    Dim destRange As Range 'Range in the destination sheet to write to (end of usedrange.rows +1)
    
    'On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.excel.xls"",""public.comma-separated-values-text"",""org.openxmlformats.spreadsheetml.sheet""}" & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0
   
   If MyFiles <> "" Then
        MySplit = Split(MyFiles, ",")
            Set destRange = ThisWorkbook.Sheets("Import").UsedRange
            'Clear content of Current sheet apart from headers
            destRange.Offset(1).Clear

        For Each filePath In MySplit
            Set mybook = Application.Workbooks.Open(filePath, , True, , , , , , , , , , False) 'open read-only. Don't add to MRU
            testval = mybook.Sheets(1).Cells(1, 1).Value 'Check the value of cell A1 to determine the version of the import sheet
            Debug.Print testval
            Set dataRange = mybook.Sheets(1).UsedRange.Offset(1)
            Set dataRange = dataRange.Resize(dataRange.Rows.Count - 1)
            Debug.Print dataRange.Address
            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
            Set destRange = destRange.Offset(destRange.Rows.Count).Resize(dataRange.Rows.Count)
            Debug.Print destRange.Address
            If testval = "id" Then 'customer engineers view (import file type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(4).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(7).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(2).Value 'Status
                'destRange.Columns(6).Value = dataRange.Columns(8).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(3).Value 'Owning group
                destRange.Columns(8).Value = dataRange.Columns(8).Value 'LastUpdated
                destRange.Columns(11).Value = Now()
                destRange.Columns(12).Value = mybook.FullName
            ElseIf testval = "SR Number" Then 'SEARCH-SR-PRODUCT-GROUPS (import file type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(2).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(3).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(4).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(5).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(7).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(8).Value 'Owning group
                destRange.Columns(8).Value = dataRange.Columns(11).Value 'LastUpdated
                For Each c In destRange.Columns(8).Cells
                    c.Value = ISODateToExcel(c.Value)
                Next
                destRange.Columns(11).Value = Now()
                destRange.Columns(12).Value = mybook.FullName
            ElseIf testval = "" Then 'US Bank (import file type)
                destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(10).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(12).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(11).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(8).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(4).Value 'Owning group
                destRange.Columns(8).Value = dataRange.Columns(16).Value 'LastUpdated
                destRange.Columns(11).Value = Now()
                destRange.Columns(12).Value = mybook.FullName
            End If
            mybook.Close
        Next
   End If
End Sub

Function ISODateToExcel(DTString) As Date
    thedate = DateSerial(Left(DTString, 4), Mid(DTString, 6, 2), Mid(DTString, 9, 2))
    thetime = TimeSerial(Mid(DTString, 12, 2), Mid(DTString, 15, 2), Mid(DTString, 18, 2))
    ISODateToExcel = thedate + thetime
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AckeemKAuthor Commented:
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.
AckeemKAuthor Commented:
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.
SimonCommented:
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.
AckeemKAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.