Removing Duplicates - while keeping original data series

[tomdriscoll]

Hello,

I would like to use information from one column in a different section of the worksheet. However, this column contains duplicates I do not want. I've found out how to remove these duplicates by using the Data ribbon, but this filters your selection where it is located, whereas I want to keep the original set of data the same in it's original location.

Does that make sense? I've attached a sample worksheet.



Thanks,

Tom

[Tony Valko]

Try this array formula** entered in D3:

=IFERROR(INDEX(A$3:A$1000,MATCH(0,COUNTIF(D$2:D2,A$3:A$1000),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

There must be a cell above the first formula cell and this cell must not contain an entry that is also in the extraction range.

You might also be interested in this method:

http://www.excelforum.com/excel-prog...pty-cells.html

[Sam Capricci]

you can always use the advance filter function where you copy to another location unique records only.

EDIT: now that i've read your attachment notes this wouldn't work if you want it to be automatic.

[RudiS]

Try this sample.
Just change one of the values in the list and the filtered uniques get updated.

[tomdriscoll]

Hi everyone - thanks for your responses so far.

Tony - I'd prefer to use a formula over a macro if possible - but the formula you've suggested is behaving a little oddly for me. The data in column D keeps updating/changing from row 8 downwards. D8 keeps switching to the value of D7, and all the cells below change also, and then back again to what I think is the correct value for D8, 55.964[....] . I've attached this new file so you can see what I mean - it's quite bizarre!

Sambo - thanks, but yes I am looking for something automatic of possible.

Rudi - that macro seems to do the trick. How do I change that code to put the output list in a different sheet, say sheet3?

Thanks again,

Tom

[Tony Valko]

I'm unable to open this new file. Excel 2007 just freezes up and I have to kill it through Task Manager.

Here's your original file with the formula implemented:

example worksheet(1).xlsx

In the original file there are 240 unique entries.

[RudiS]

Here is the new macro to shift the unique data to sheet 3.

[azumi]

Hi everyone - thanks for your responses so far.

Tony - I'd prefer to use a formula over a macro if possible - but the formula you've suggested is behaving a little oddly for me. The data in column D keeps updating/changing from row 8 downwards. D8 keeps switching to the value of D7, and all the cells below change also, and then back again to what I think is the correct value for D8, 55.964[....] . I've attached this new file so you can see what I mean - it's quite bizarre!

Sambo - thanks, but yes I am looking for something automatic of possible.

Rudi - that macro seems to do the trick. How do I change that code to put the output list in a different sheet, say sheet3?

Thanks again,

Tom

when you put formula in D3 you must change this part "D$3:D3" with "D$2:D2"

Cheers
Azumi

[tomdriscoll]

Thanks guys, got this working in my workbook now!

[Tony Valko]

Good deal. Thanks for the feedback!