Help in sorting duplicate data

ordinateur

New Member
Joined
Feb 13, 2009
Messages
34
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:)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use Data|Filter|Advanced Filter to copy a list of unique items to another column.
 

ordinateur

New Member
Joined
Feb 13, 2009
Messages
34
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:)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 13, 2009
Messages
34
Andrew/Colin

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


Regards:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,316
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top