+ Reply to Thread
Results 1 to 9 of 9

How do you create a unique list from multiple same named entries?

  1. #1
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    How do you create a unique list from multiple same named entries?

    I am trying to create a unique list from a column of information, the source data contains names, dates and times to used to create a list of attendees ad non-attendees on a course. I have attached a workbook to show the data and the result required. I would also like the data to be sortable in its new unique form if this is possible.

    Thanks in advance.

    JD
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    478

    Re: How do you create a unique list from multiple same named entries?

    are u looking for something like this?

    unique-list-sorted-alphabetically.xls

    Sorry, My bad
    I thought you just required to list the unique data... How do i delete this post?
    Last edited by akhileshgs; 06-05-2013 at 03:47 AM.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: How do you create a unique list from multiple same named entries?

    OK this is how i did it

    Custom sort list by NAME>DATE>TIME
    Then
    Remove Duplicates by Name

    then
    Re-sort list by Date>time

    * remove duplicates - removes every duplicate except the first one which is why you sort by Name>date>time first

    sorry just found out 2003 doesnt have remove duplicates function
    attached is work around for that result

    http://support.microsoft.com/kb/262277
    Last edited by humdingaling; 06-05-2013 at 03:53 AM. Reason: reasoning
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How do you create a unique list from multiple same named entries?

    Using a helper and hidden column to "add" ALL the conditions. Formula.

    =IF(COUNTIF($G$2:$G$4;B2);SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2));"")

    Then in A2 of second sheet use this ARRAY formula. Copy down and across.

    =IFERROR(INDEX(Sheet1!A$2:A$28;SMALL(IF(Sheet1!$E$2:$E$28=1;ROW(Sheet1!A$2:A$28)-1);ROW(Sheet1!A2)));"")

    I also added a button for sorting as you want because using only the formula, data are sorted only by dates and not by time...
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: How do you create a unique list from multiple same named entries?

    It works well in it's own spread sheet but it just lists everything when I try and incorporate it into my own , is there something I am missing? the list of the 3 appointment types is in another worksheet in my own spreadsheet would that affect it, even though I am pointing it in the right direction, the helper column has the 1 instances in the right places but when it comes to display it on the matched criteria it just reproduces the full list of appointments regardless of appointment type.

    Hope you can help its driving me nuts.

    I also need a formulas that will work in workbooks from 2005 to 2010 if possible.

    JD

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How do you create a unique list from multiple same named entries?

    As you said works fine in the example sheet that you provided. Pls upload your modified sample workbook to test it.

  7. #7
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: How do you create a unique list from multiple same named entries?

    Hi please see attached sheet, the final product column would not calculate properly as I use Microsoft Excel 2010 at work and 2002 at home, so the formula has not worked and is just displayed as =n/A, is there a way to make it work and be fully compatible between versions, yours hopefully.

    JD
    Attached Files Attached Files

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How do you create a unique list from multiple same named entries?

    The problem is that IFERROR, does not works in editions<2007. So we replaced it with IF(ISERROR..

    =IF(ISERROR(INDEX(Sheet1!A$2:A$1000;SMALL(IF(Sheet1!$F$2:$F$1000=1;ROW(Sheet1!A$2:A$1000)-1);ROW(Sheet1!A1))));"";INDEX(Sheet1!A$2:A$1000;SMALL(IF(Sheet1!$F$2:$F$1000=1;ROW(Sheet1!A$2:A$1000)-1);ROW(Sheet1!A1))))
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: How do you create a unique list from multiple same named entries?

    Works beautifully much appreciated for you time and knowledge.

+ 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