Listing different Values 1

[scottie1]

Hi there

I've got a cloumn with data in it that has duplicate data in it. What I'm trying to do is cut the original data from the duplicates and put them in a list on another spreadsheet.
ie cell A1:A10 = 1234 then from cell A11:A:20 = 98765 which means i have column A with 20 rows filled in but I only want a column with the two different numbers in it.
ie A1 = 1234 and A2 = 98765 and so on

Help

 

[Ayias]

I dont know of any way in excel to do this, you can do this by writing some vba code within excel. All you would need to do is write a loop to go through the cells and then check for duplicate data, if it finds different data it can then paste this into a column, you can then delete the original column. Hope this helps, if you need more help then I would advise reading the VBA help, this is very useful.

 

[TonyJollans]

Hi scottie1,

You can do this quite easily without code.

Insert a row at the top for column headings (Click in A1 and Select Insert > Rows from the Menu)
Insert a column at the left to work in (Click in A1 and Select Insert > Columns from the Menu)

In Cell A2 enter [blue]=B2=B1[/blue]
Copy down as far as your data goes

Select Columns A and B
Select Data > Filter > AutoFilter from the Menu

From the dropdown in cell A1, Select FALSE
Select Column B
Select Edit > Go To from the Menu
Click on Special...
Select Visible Cells only and Press OK
Select Edit > Copy from the Menu

Go to your new Worksheet
Select Edit > Paste from the Menu

Go back to your original worksheet and remove the AutoFilter (Select Data > Filter > AutoFilter from the Menu again)
Delete the Row and Column that you added at the beginning

.. and Bob's your Uncle!


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[scottie1]

Tony

You are a diamond that was sweet as a nut so simple when you know how.

Cheers Adam

 

[xlbo]

If you can bear to have teh unique list on the same sheet, this is even easier with Advanced Filter. To get the data onto another sheet, you have to use code unfortunately, due to a bug in the Advanced Filter function which M$ has STILL not fixed

You could also use a pivottable


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi Geoff,

Ken (Wright) recently made me aware that you CAN Filter to another sheet. What you must do is run the dialog from the target sheet, and point to a database range on a different sheet rather than the other way round - he found it on a web page (Debra Dalgleish's I think)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

Tony - beautiful. Bug must be in the target rather than the database. I'd give you a star if you hadn't told me it was one of Ken's ;-)

In which case, why not use that instead of the autofilter ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

No reason at all, Geoff - just not thinking

scottie1,

You might want to take a look at the Advanced Filter (as it's even easier that way)..

Go to your target worksheet
Select Data > Filter > Advanced Filter
Select Copy to another location
Click on the 'Collapse Dialog' button beside the List range textbox and go select your data on the other sheet.
(Leave the Criteria Range blank)
Click on the 'Collapse Dialog' button beside the Copy to textbox and select your target (column A?)
Select Unique Records Only
Press OK




Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.