Help in sorting duplicate data

[ordinateur]

Dear member

Can anyone let me know whether there is a way to sort out all duplicate data in a column.

I have loads of cost centres in a column and i want to sort them out without the duplicates in a new column.


regards

 

[Colin Legg]

Hi,

Have a look at using the advanced filter.

Data | Filter | Advanced Filter
Copy to another location

Change the List Range and Copy to fields.
Tick the unique records only checkbox.

 

[Andrew Poulsom]

You can use Data|Filter|Advanced Filter to copy a list of unique items to another column.

 

[ordinateur]

Hi Andrew and Colin

Thanks for the reply, i tried it but i did not quite work well as i had few cost centres missing in my new list and one (just one though) was duplicated. May be i had done it wrong, i did the following;

1) Clk copy to another location
2) Clk on List range and select the colum with the cost centres.
3) criteria ??? i did not do anything as i don't know what it does.
4)Then i clk copy to and selected a new colum

and last ticked the unique records only.

Am i doing it right?

regards

 

[Jon von der Heyden]

Sounds like you're doing it right. There's a tutorial here: http://www.contextures.com/xladvfilter01.html#FilterUR

I don't know why it would miss cost centre's but I suspect the duplicated one may have a leading or trailing space.

 

[ordinateur]

Thanks for the link Jon , i will have a look at it later.

Regards

 

[Andrew Poulsom]

If there are some missing you didn't select all the records before filtering. If there seem to be duplicates they are not really the same. For example one might have a trailing space, or one is a number and the other is text that looks like a number.

 

[Colin Legg]

Also, advanced filter considers the 1st item to be a column header, so make sure it is not a cost centre code - if necessary give the data a column header such as "Cost Centres".

 

[ordinateur]

Andrew/Colin

Many thanks for your tips, it works perfect now.Really appreciate it.


Regards