+ Reply to Thread
Results 1 to 5 of 5

Short list of values from long list of duplicates

  1. #1
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    Short list of values from long list of duplicates

    Okay, after searching and trying - I think I'm going to need INDEX and maybe MATCH but those are not commands I use in my day-to-day, so I'm stumped on how to make them work.

    I've got a column of dates, (not sequential, several days skip). I want to build a "short list" of all the date values in the column - so that even though 2/5/2012 shows up 12 times, I want it to appear on a list once. I can copy the cells down as much as I need to - until I identify all the possible dates from the column.
    Ultimately, the next column to the right would have a countif statement involving my result and the column, to give me an occurance count for each date. The 'short list' can occur at the bottom of the date column, or in another column, no matter.

    file attached.


    thanks a million for any and all assistance!
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Short list of values from long list of duplicates

    Take a look at advanced filter and then apply your countif

    http://www.contextures.com/xladvfilter01.html#FilterUR
    HTH
    Regards, Jeff

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Short list of values from long list of duplicates

    put this formula in B2, copy and copy down. you then have a couple of different options...

    =IF(A2=A1,"",A2)

    if you want to keep all your data, use a filter, based on column B, to only show non-blanks
    if you ont want to keep the duplicates, copy column C, paste as values, sort column B abd delete all blank rows.

    let me know how you make out
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Short list of values from long list of duplicates

    See the attached.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: Short list of values from long list of duplicates

    thanks all!

    I didn't describe the whole issue very well - I needed to keep the column intact - each date has lots of specific error data attached to it and aside from the error count (date occurence) for statistical use, I need to keep it "whole" to investigate each error. I toyed with the auto-filter option but for reporting that would be a bit manual - select the date, count the occurrences, enter that error count into the statistical system, then go back and work the errors. I thought it would be nice to just 'see' the counts and move on to the bulk of the work.

    I appreciate all the responses, Haseeb's solution will work for me. THANKS!

+ 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