Showing only max time for product

[timely]

I am trying to get it so that if I have multiple entries for products I just want the last time for each of the products.

An example of the data is:

Date Customer Date/Time
29-Sep-04 1594301 9/29/2004 23:00
29-Sep-04 1100501 9/29/2004 23:00
29-Sep-04 1594201 9/29/2004 23:00
29-Sep-04 919101 9/29/2004 23:00
29-Sep-04 919101 9/29/2004 23:00
29-Sep-04 1289301 9/29/2004 19:00
29-Sep-04 893601 9/29/2004 23:00
29-Sep-04 1289301 9/29/2004 19:00
29-Sep-04 894001 9/29/2004 23:00
29-Sep-04 418601 9/29/2004 22:17
29-Sep-04 1177701 9/29/2004 23:00
29-Sep-04 161301 9/29/2004 19:50
29-Sep-04 969001 9/29/2004 23:00
29-Sep-04 280601 9/29/2004 16:20
29-Sep-04 1194801 9/29/2004 23:00
29-Sep-04 1594101 9/29/2004 20:06
29-Sep-04 1594201 9/29/2004 20:06
29-Sep-04 1594201 9/29/2004 23:00
29-Sep-04 1594101 9/29/2004 20:06
29-Sep-04 1594501 9/29/2004 23:00


I just need each product to show up once with the last date/time being shown. I know their should be a max function to help with this but not sure how to use it in this case

Thanks

 

[timely]

Oh, bye the way this is in excel.

 

[xlbo]

Pivot table would sort this out no probs

Customer / Product as ROW field

MAX of Date / Time as VALUE field

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[timely]

Yes, good for pivot tables, but is there a way to do that with vba and place in a report. Pivot tables take tomuch room on some of the older systems.

 

[xlbo]

Well give me some more info then - do you want a list of products / customers on a seperate sheet with the max time or do you want to replace the info in situ (deleting the non max records) - you have given no indication of what it is you actually want to do.

Your sarcastic reply is hardly condusive to you getting any help on this - especially as you could create the pivot table in code and then copy/paste values if you are worried about the size of the file

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[timely]

I was not trying to be sarcastic, sorry if it sounded that way.

I was just wanting to put the data and times on another sheet to then compare to other data that I have collected. I was not wanting to have to cut and past due to I am trying to make it so that people can run it by just pushing a button. I thought that maybe the VBA would take less space then creating a pivot table and then moving the data.

Thank You

 

[xlbo]

ok - no probs.
I would still suggest that the pivot table is your best / easiest option

You can record yourself creating the pivottable on a blank sheet, setting the VALUE part of the layout to be MAX of Date / Time.

Once the pivottable has been created it can be copied, then pasted back as values only to keep the workbook size down.

I recorded the method myself and to show you how it could be done, I have cleaned up the code slightly and commented it - should give you something to go at anyway:

[COLOR=green]'Add pivotcache[/color]
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R21C3").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable4", DefaultVersion:=xlPivotTableVersion10
    [COLOR=green]'Call pivottable wizard[/color]
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    [COLOR=green]'Turn off any row / column totals[/color]
    With ActiveSheet.PivotTables(1)
        .ColumnGrand = False
        .HasAutoFormat = False
        .RowGrand = False
    End With
    [COLOR=green]'Get rid of subtotals[/color]
    ActiveSheet.PivotTables(1).PivotFields("Customer").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    [COLOR=green]'Add customer field in[/color]
    ActiveSheet.PivotTables(1).AddFields RowFields:="Customer"
    [COLOR=green]'Add Date / Time field in and change function to MAX[/color]
    With ActiveSheet.PivotTables(1).PivotFields("Date / Time")
        .Orientation = xlDataField
        .Caption = "Max of Date / Time"
        .Function = xlMax
    End With
    [COLOR=green]'Format the date field[/color]
    With ActiveSheet.PivotTables(1).PivotFields("Max of Date / Time")
        .NumberFormat = "dd/mm/yyyy hh:mm"
    End With
    [COLOR=green]'Select pivot table so it can be copied[/color]
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select
    Selection.Copy
    [COLOR=green]'Paste as values to get rid of the pivottable functionality[/color]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[timely]

Thanks...

I will give it a go.