# of unique dates per unique list entry

**MrNovice** · 10-22-2009, 10:37 AM

Hi,

I have an Excel sheet containing a list of product repairs.
The list can be of warying length - depending on the product in question, and period monitored.

Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.

The repair list contains 1 entry per spare part used, so the same serial number may occur several times.

Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).

The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.

Sample list:
Serial........Repair date
20774205 2008-09-04
20775508 2008-12-17
20775521 2008-12-31
20775521 2009-01-22
20775521 2009-01-22
20775521 2009-01-22
20775521 2009-02-13
20775521 2009-07-24
20775536 2009-05-20
20775553 2009-07-16
20777012 2008-11-13
20777049 2009-03-27
20777049 2009-03-27
20777049 2009-04-03
20777068 2008-08-22
20777068 2008-08-22
20777068 2008-11-17

For instance - serial number 20775521 has been repaired on 4 unique dates.

I am a complete novice with regards to using macros/programming in Excel, so please add information about how to implement such a solution.

Can anyone help?

**DonkeyOte** · 10-22-2009, 11:07 AM

IMO, you should use a Pivot Table to do the final analysis.
To aid that process add a 3rd column to weight the date based on frequency of occurence...

eg assume your table is A:B with row being headers... and given use of XL2007

set header
C1: Unique

obtain apportionment (of 1)
C2: =1/COUNTIFS($A$2:$A$1000;$A2;$B$2:$B$1000;$B2)
copied down for all rows

Then create a Pivot Table off your data in Cols A:C, set Serial as Row Label and Unique as Data Field (SUM)

**MickG** · 10-22-2009, 11:14 AM

Hi, This is a Double Click Event procedure.
Right Click your sheet Tab, Select "View Code". (VB Window Appears)
Paste the code Into The Window, Close VB Window
The code assumes Your Data is in column "A & B and starts Row (2).
To Run code, Double Click Cell "A1", code runs and Column "D" should be Filled with Results.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range, Dn As Range, n As Long
If Target.Address(0, 0) = "A1" Then
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count)
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        If Not .Exists(Dn.Value) Then
           n = n + 1
           .Add Dn.Value, n
           ray(n) = Dn.Value & ":- " & Dn.Next.Value
        Else
            ray(.Item(Dn.Value)) = ray(.Item(Dn.Value)) & " , " & Dn.Next.Value
        End If
    Next Dn
End With

With Range("D1")
.Value = "Product - (RepairDate)"
.Offset(1).Resize(n) = Application.Transpose(ray)
.Columns.AutoFit
End With
End If
End Sub

Regards Mick

**MrNovice** · 10-23-2009, 04:05 AM

Hi,

Thank's for the fast replies!

@DonkeyOte:

Nice idea - hadn't thought about assigning decimal values in inverse proportion to the number of occurrances - makes it quite simple to analyse results and make a graphical representation with pivot.
It does, however, require an extra amount of operations.

@MickG:
Running your program gives me a single column (D) listing all unique serial numbers - so far so good!
The cells also contain every repair date related to the unique serial number - example:

20775521:- 31-12-2008 , 22-01-2009 , 22-01-2009 , 22-01-2009 , 13-02-2009 , 24-07-2009

As you can see, the same date occurs several times for that serial number.
The optimal solution for me would be to have the number (eg. "4" in the example above) of unique dates related to every unique serial number listed in a separate column (E in this case), next to their related serial number - like this:

20775508 1
20775521 4
20775536 1
20775553 1
20777012 1
20777049 2
20777068 2

Is that possible?

I really appreciate your help guys - thanks a million!

**DonkeyOte** · 10-23-2009, 04:21 AM

@DonkeyOte:

Nice idea - hadn't thought about assigning decimal values in inverse proportion to the number of occurrances - makes it quite simple to analyse results and make a graphical representation with pivot.
It does, however, require an extra amount of operations.

All of which could be automated of course... FWIW I'd always opt for that which offers most flexibility long term.

**MickG** · 10-23-2009, 08:42 AM

Hi, Try this , Post Code as before, Results in column "E",

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range, Dn As Range, n As Long, Twn As String, Sunq
Dim num As String, Q, U As Long
If Target.Address(0, 0) = "A1" Then
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count)
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        Twn = Dn & "," & Dn.Next
        If Not .Exists(Twn) Then
           n = n + 1
           .Add Twn, Nothing
         End If
    Next Dn
Sunq = .keys
n = 0
End With
 
  With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
         For U = 0 To UBound(Sunq)
           If Not .Exists(Split(Sunq(U), ",")(0)) Then
                n = n + 1
                num = Split(Sunq(U), ",")(0)
                .Add num, Array(n, 1)
                ray(n) = num & " " & 1
           Else
                Q = .Item(num)
                Q(1) = Q(1) + 1
                ray(Q(0)) = num & " " & Q(1)
                .Item(num) = Q
           End If
      Next U
End With

With Range("E1")
.Value = "Product - (RepairDate)"
.Offset(1).Resize(n) = Application.Transpose(ray)
.Columns.AutoFit
End With
End If
End Sub

Regards Mick

**MrNovice** · 10-27-2009, 04:29 AM

Hi,

I have now had the time to test your latest suggestion Mick - it does exactly what I intended.

One final question: I would like to split the results into 2 columns (in order to facilitate easy graphical representation - or is there a way to display the data "as is" - i.e. both datasets are shown in the same column (E)) - does this require a change in the program, or can it be done straight off?

Bo