Removing Duplicates through using if statements

[excelnovice936]

Hello:

I have a list of Dates, however many of them are repeated. I want to have a list of just each individual date in a seperate column.

Below is my column A, it has mulpiple dates:

12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
12-AUG-13
13-AUG-13
13-AUG-13
14-AUG-13
14-AUG-13
14-AUG-13
14-AUG-13
15-AUG-13
15-AUG-13
15-AUG-13
15-AUG-13
16-AUG-13
16-AUG-13
16-AUG-13
16-AUG-13
16-AUG-13
17-AUG-13
17-AUG-13
17-AUG-13
17-AUG-13
18-AUG-13
18-AUG-13
18-AUG-13

What equation should I use to have another column with this format:

12-AUG-13
13-AUG-13
14-AUG-13
15-AUG-13
16-AUG-13
17-AUG-13
18-AUG-13


Thanks for the help.

[liz5818]

my suggestion would be create a pivot table that will show the dates. I'm assuming you just want to know what the individual dates are. using a pivot table could allow you to know how many times that date was listed as well.

[Sam Capricci]

There are many options, you could use the filter > advanced > copy to another location and check unique records only, you could use the remove duplicates function, or the group function or the subtotal function.

[excelnovice936]

hey guys, the thing is that I am trying to automate this because this is for a project that is going to be used in the future. and the initial informatino will look like the first set of data I posted. But i need to figure out how to make it into the second set of dates using if statements or any excel formula so this project can be implemented without other users of this tool having to do anything. thanks

[AlKey]

Assuming data starts in A2

Enter in B2

=INDEX($A$2:$A$32, MATCH(0, COUNTIF($B$1:B7, $A$2:$A$32), 0))

This is an array formula must entered with Ctrl-Shift+Enter

[excelnovice936]

@alkey:
That did not seem to work.

I was thinking something along the lines of:

=if(A2=A1,if (A3=A2),if(a3=a4)....

im not sure if that is the right thought process though

[AlKey]

You probably did not enter the formula right. Please see attached fileUniques.xlsx

[excelnovice936]

okay. i see that. it looks great. butfor example if you add the date 8/19/2013 right after to the list in A? I am looking for something that will automatically add new dates to column B as column A is changed.

[AlKey]

You can drag formula farther down and when new dates added the formula will pick it up. Just extend the range to accommodate grows.

[excelnovice936]

alright, I tried to do that I added additional dates to column A, i added 2 "8/19/2013" and 1 "8/20/2013" and expanded the range to look like : =INDEX($A$2:$A$35, MATCH(0, COUNTIF($B$1:B9, $A$2:$A$35), 0)) and it didnt do anything?

sorry for all the questions and thanks alot for your help

[AlKey]

The range in the formula ($A$2:$A$35) needs to be of the same range as column A. After you adjusted the range in the formula, make sure that you entered it with Ctrl+Shift+Enter. If still have trouble with arrays please let me know and will provide you with an alternative.

[Sam Capricci]

BTW, I tried AlKey's original formula as written and it didn't work but as included in their attachment it does work. It should be ok once you do the control shift enter to simply drag down, w/o hitting control shift enter each time, it worked that way for me. Congrats Alkey, nice formula, I'll keep that one.

[AlKey]

Thank you Sambo kid

[excelnovice936]

Thanks a bunch guys, I got it working. truly really helpful

[AlKey]

You're Welcome and please mark thread as "Solved" if your issue has been resolved.

[excelnovice936]

where do i mark it as solved?

[AlKey]

Select Thread Tools-> Mark thread as Solved