+ Reply to Thread
Results 1 to 9 of 9

# of unique dates per unique list entry

  1. #1
    Registered User
    Join Date
    10-22-2009
    Location
    Holstebro, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    5

    # of unique dates per unique list entry

    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?
    Last edited by MrNovice; 10-27-2009 at 07:03 AM. Reason: Question solved

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,528

    Re: # of unique dates per unique list entry

    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)

  3. #3
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: # of unique dates per unique list entry

    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.
    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 10-22-2009 at 11:17 AM.

  4. #4
    Registered User
    Join Date
    10-22-2009
    Location
    Holstebro, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: # of unique dates per unique list entry

    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!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,528

    Re: # of unique dates per unique list entry

    @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.
    Last edited by DonkeyOte; 10-23-2009 at 04:46 AM. Reason: typo

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: # of unique dates per unique list entry

    Hi, Try this , Post Code as before, Results in column "E",
    Please Login or Register  to view this content.
    Regards Mick

  7. #7
    Registered User
    Join Date
    10-22-2009
    Location
    Holstebro, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: # of unique dates per unique list entry

    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

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: # of unique dates per unique list entry

    Hi, Try this, Results in columns "E & F".
    Please Login or Register  to view this content.
    Regards Mick

  9. #9
    Registered User
    Join Date
    10-22-2009
    Location
    Holstebro, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: # of unique dates per unique list entry

    Spot on - thank you very much!

    Now I just need to convince my manager to let me take courses in this stuff - I cannot interpret very much of the code, you've written...

    I'll mark this subject as solved.

    Bo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1