Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Return a unique row from a subset that has duplicates in a specified column, based on the latest date of another spcecified column

Posted on 2011-09-10
5
233 Views
Last Modified: 2012-08-14
I'm working in Excel 2007.  I have a table in which a particular column will have duplicates; I need to report one of the rows with the duplicates but the row that remains must be determined by a date in another column.  

See the attachment.  The first row should remain because there is no duplicate in the EPD_IDENTIFIER column.  The next two rows have a duplicate in the EPD_IDENTIFIER column but I need only the row with the latest time stamp in the EPD_STATUS_CHANGE_TS column to remain.  All the rows with a red arrow should remain, the other deleted.

example.pptx
0
Comment
Question by:wkraft
  • 3
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36516668
wkraft,

It will be much, much more helpful if you provide a sample Excel file.  Sending a PowerPoint file for an Excel problem won't do us much good :)

Patrick
0
 

Author Comment

by:wkraft
ID: 36516683
Thanks for pointing that out, Patrick.  Makes a lot of sense.  

I've attached the data in an Excel spreadsheet to this comment.
--example-again.xlsx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36516988
Please try uploading the file again, it does not seem to have been uploaded correctly.
0
 

Accepted Solution

by:
wkraft earned 0 total points
ID: 36516995
With the page filtered newest to oldest by EPD_STATUS_CHANGE_TS, then A to Z by EPD_IDENTIFIER, run macro:


Public Sub CorrectList()

Dim lastRow As Integer, EPD As String, nextEPD As String, count As Integer, canc As Integer

lastRow = Cells(Rows.count, "A").End(xlUp).Row

For i = 2 To lastRow
   
    EPD = Cells(i, "C")
    count = 1
   
    If EPD = "" Then
   
        Exit For
       
    End If
   
    can = 0
   
    If Cells(i, "D") = "CANCELLED" Then
        can = 1
    End If
   
    For t = i + 1 To lastRow
            nextEPD = Cells(t, "C")
        If EPD = nextEPD Then
             count = count + 1
        Else: Exit For
       
        End If
    Next
   

   
    Select Case can
       
        Case 0
               
                If count <> 1 Then
                    Rows(i + 1 & ":" & i + count - 1).Select
                    Selection.Delete Shift:=xlUp
                End If
               
        Case 1
                               
                    Rows(i & ":" & i + count - 1).Select
                    Selection.Delete Shift:=xlUp
                    i = i - 1
    End Select
   
Next

End Sub
0
 

Author Closing Comment

by:wkraft
ID: 36541419
Coworker was able to help build a macro.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question