Display repeating items in another column only once?

Roo · Mar 18th, 2002, 04:44 PM

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

Mark W. · Mar 18th, 2002, 04:47 PM

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 ]

Roo · Mar 18th, 2002, 05:23 PM

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.

Mark W. · Mar 18th, 2002, 05:39 PM

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"}

Roo · Mar 18th, 2002, 05:46 PM

Thanks thats excellent and will get me by nicely.

Derek · Mar 18th, 2002, 05:48 PM

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 ]

Linda_SF · Nov 28th, 2007, 08:55 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 04:44 PM	#1
Roo New Member Join Date: Mar 2002 Posts: 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

Mar 18th, 2002, 04:47 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Mar 18th, 2002, 05:23 PM	#3
Roo New Member Join Date: Mar 2002 Posts: 3	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.

Mar 18th, 2002, 05:39 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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"}

Mar 18th, 2002, 05:46 PM	#5
Roo New Member Join Date: Mar 2002 Posts: 3	Thanks thats excellent and will get me by nicely.

Mar 18th, 2002, 05:48 PM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]

Nov 28th, 2007, 08:55 AM	#7
Linda_SF New Member Join Date: Nov 2007 Posts: 1	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