Display repeating items in another column only once?

Roo

New Member
Joined
Mar 17, 2002
Messages
3
I have flight log speadsheet containing a couple of thousand records. One column contains the three letter aircraft registration. I want to dispay an alphabetical list of the individual registrations on another sheet. There are about 30 unique aircraft regos that repeat frequently through out the column.

Essentially I need a way to find and display every item from one column in another column but only once. Preferably in alphabetical order.

Can someone please tell me which formula I should use to achieve this.

Source Result

EBW EBQ
EBQ EBS
EBS EBW
EBS EBY
EBY ECC
EBW
EBW
ECC
EBY
EBW
ECC
EBY
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Do you require a formula? Or, will a couple of commands suffice?
This message was edited by Mark W. on 2002-03-18 15:47
 
Upvote 0
I would prefer a formula so that it would be more automated. But now that I think of it a couple of command would probably suffice as I could no doubt make a macro to run them.
 
Upvote 0
Okay, suppose A1:A13 contains...

{"aircraft regos "
;"EBW"
;"EBQ"
;"EBS"
;"EBS"
;"EBY"
;"EBW"
;"EBW"
;"ECC"
;"EBY"
;"EBW"
;"ECC"
;"EBY"}

1. Select A1:A13 and choose the Data | Filter | Advanced AutoFilter... menu command.
2. Click on Copy to another location.
3. Enter B1 in the Copy to field.
4. Check Unique records only and press [ OK ].
5. Select column B:B and Sort the values to get...

{"aircraft regos "
;"EBQ"
;"EBS"
;"EBW"
;"EBY"
;"ECC"}
 
Upvote 0
Hi Roo and Mark

I use this formula in B1 to display unique entries in column A. (needs sorting afterwards)
=IF(COUNTIF($A$1:$A$33,A1)=1,A1,IF(AND(COUNTIF($A$1:$A$33,A1)>1,COUNTIF($A$1:A1,A1)=1),A1,""))

Extend the A33 to as many rows as necessary
regards
Derek
This message was edited by Derek on 2002-03-18 16:53
 
Upvote 0
This information was helpful, but I need to be able to filter and copy this list to another file - (or even another worksheet within the workbook would work) -- I have not been able to do this, beyond the same worksheet that you're filtering from.... can you help? thanks. Linda
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top