Formula to create an ordered list and remove duplicates without using macros

[Skiingbeancounter]

Hello again,

It's probably easy or down to syntax when using table references rather than a names area but I can't find the error.

I have a workbook where other users enter some information at the front in a very user friendly format, the data is then reformatted to fit a a different template/format so we can enter it using a pre-written import which we can't change (well we could for a fee). The users who enter the data find the fixed format at the back confusing hence the need for an entry page.

For multiple reasons macros are not an option.

In a different workbook I have been using the following formula successfully where I had a list of product names to create a different list without duplicates and ordered in alphabetical order. The formula is in B2 and dragged down so $B$1:$B1 becomes $B$1:$B2 when in B3 etc. In the origina spreadsheet, then formula returns "" in B2 but the rest is a nice ordered list of text with no dups.

{=INDEX(List,MATCH(MIN(IF(ISBLANK(List)+COUNTIF($B$1:$B1,List),"",IF(ISNUMBER(List),COUNTIF(List,"<"&List),COUNTIF(List,"<"&List)+SUM(IF(ISNUMBER(List),1,0))+1))),IF(ISBLANK(List)+COUNTIF($B$1:$B1,List),"",IF(ISNUMBER(List),COUNTIF(List,"<"&List),COUNTIF(List,"<"&List)+SUM(IF(ISNUMBER(List),1,0))+1)),0))}

In the new workbook the information which was in the equivalent of List in the formula is now in a table in the front page and the data I need de-duplicated and ordered (to be fair I don't mind if it's not ordered, just de-duplicated) is in date format. So I modified the formula slightly and made it point to the "date" column of Table 2 instead of the original "List". So far so good.

{=INDEX(Table2[DATE],MATCH(MIN(IF(ISBLANK(Table2[DATE])+COUNTIF(B$67:$B67,Table2[DATE]),"",IF(ISNUMBER(Table2[DATE]),COUNTIF(Table2[DATE],"<"&Table2[DATE]),COUNTIF(Table2[DATE],"<"&Table2[DATE])+SUM(IF(ISNUMBER(Table2[DATE]),1,0))+1))),IF(ISBLANK(Table2[DATE])+COUNTIF(B$67:$B67,List),"",IF(ISNUMBER(Table2[DATE]),COUNTIF(Table2[DATE],"<"&Table2[DATE]),COUNTIF(Table2[DATE],"<"&Table2[DATE])+SUM(IF(ISNUMBER(Table2[DATE]),1,0))+1)),0))}

The problem is that once it has ran out of dates instead of putting N/A or "" in the cell it puts the first date in over and over, so if I have 20/12/2012, 24/12/2012, 28/12/2012, 24/12/2012 and 28/12/2012 in Table2[DATE] I would expect
B67 20/12/2012
B68 24/12/2012
B69 28/12/2012
B70 #N/A
B71 #N/A

Instead I get
B67 20/12/2012
B68 24/12/2012
B69 28/12/2012
B70 20/12/2012
B71 20/12/2012

Any ideas?

[Moo the Dog]

Could having the reference to 'List' be messing things up for you? (See red text in formula below):

Please Login or Register  to view this content.

-Moo

[Skiingbeancounter]

Thank you Moo, a good example of the hazards of copy pasting . I had been staring at it too long to notice.

[Moo the Dog]

You're welcome.