Solved

Hide Duplicate Records based on TimeStamp

Posted on 2013-06-28
5
529 Views
Last Modified: 2013-06-29
Greetings

I have a data stream in to Excel Spreadsheets (1301, 1302, 1303, etc.) within a single Workbook. I have VBA routines to Delete Duplicate Records but they do not consider the TimeStamps. I need to "Hide" Records and not "Delete"

ColA               ColB               ColC               Cold
1/02/2013      CustA             data               data
1/13/2013      CustA             data               data
1/13/2013      CustB             data               data
1/08/2013      CustA             data               data
1/21/2013      CustB             data               data
1/13/2013      CustC             data               data
1/18/2013      CustB             data               data
1/13/2013      CustD             data               data

I need to "Hide" Records with earlier TimeStamps (ColA) by ColB (Customer)' I wish only to View a Customers Later Record based on TimeStamp (ColA). I must retail all data for a historical record.

ColA               ColB               ColC               Cold
1/13/2013      CustA             data               data
1/21/2013      CustB             data               data
1/13/2013      CustC             data               data
1/13/2013      CustD             data               data

This should run on Active Worksheet

Regards,

Dave
0
Comment
Question by:srlittle
[X]
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
  • 3
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
Martin Liss earned 300 total points
ID: 39286055
Here;s a macro.

Sub HideOld()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngCompareRow As Long
Dim strCust As String
Dim lngHighRow As Long
lngLastRow = Range("A65536").End(xlUp).Row

For lngRow = 2 To lngLastRow
    If Rows(lngRow & ":" & lngRow).EntireRow.Hidden = False Then
        strCust = Cells(lngRow, 2).Value
        lngHighRow = lngRow
        For lngCompareRow = lngRow + 1 To lngLastRow
            If Rows(lngCompareRow & ":" & lngCompareRow).EntireRow.Hidden = False Then
                If Cells(lngCompareRow, 2).Value = strCust Then
                    If DateValue(Cells(lngCompareRow, 1)) >= DateValue(Cells(lngHighRow, 1)) Then
                        Rows(lngHighRow & ":" & lngHighRow).EntireRow.Hidden = True
                        lngHighRow = lngCompareRow
                    Else
                        Rows(lngCompareRow & ":" & lngCompareRow).EntireRow.Hidden = True
                    End If
                End If
            End If
        Next
    End If
Next
    

End Sub

Open in new window

0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39286075
Note that the above assumes that your data starts in row 2.
0
 

Author Comment

by:srlittle
ID: 39286746
Took a while to walk thru your solution. Very nice. I learned a few things. Your code Hides Old Duplicates as expected, however the EntireRow.Hidden property does not allow me to Unhide the Hidden Rows from the Files/Unhide Button (It is grayed Out). However the Right Click "Unhide" works. I need to do this to view Historical Data. Your solution works and I accept with gratitude.

Regards,
Dave
0
 

Author Closing Comment

by:srlittle
ID: 39286758
My coding expertise is more centerd on data manipulation. I always enjoy learning more about the power of Workbook/Worksheet Properties and functions.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39286854
I'm not familiar with Files/Unhide but here's a companion macro you can use to unhide the rows. Do you know how to assign a shortcut key to the macro?

Sub ShowAll()

Dim lngLastRow As Long
lngLastRow = Range("A65536").End(xlUp).Row

Rows("1:" & lngLastRow).EntireRow.Hidden = False

End Sub

Open in new window

In any case I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

751 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