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

1. ## 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.

JD

2. ## 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

I thought you just required to list the unique data... How do i delete this post?

3. ## 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

4. ## 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...

5. ## 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. ## 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. ## 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

8. ## 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))))

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

Works beautifully much appreciated for you time and knowledge.

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

#### 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