Creating a unique list from a list of duplicates

Hey Guys,

I have a problem in Excel that I really need help with. I have two sheets, the first sheet has a column with duplicates (all my columns have headers). In my second sheet I would to have the list, generated from the column in the first sheet, but a unique list. I have tried different formulas, and nothing works! I am posting it here under Macros and VBAs, because perhaps a Macro would be a better solution, unfortunately I have little experience with Excel macro's, though I have some experience with VBA in Access. A solution in the form of Macro or a Formula would both be helpful!

Please help!

Many thanks!

S

not sure understanding question properly
you just want a copy of a sheet but remove duplicate rows based from a column ?

will you upload a sample file.......in which there are some problem data and manual solution data.......


Regards
CA Mahaveer Somani

Basically the first sheet, lets call it 'Pre - General Training' has the following data:

Name of Event
Training the trainers
Training the trainers
Policy Update Training
Policy Update Training
Policy Update Training
Policy Update Training
Training Course on MIS
Training Course on MIS
Training Session on Cloud Computing
Training Session on Cloud Computing
Training Session on Cloud Computing
Training Session on Cloud Computing
Training Session on Cloud Computing

I basically want a column in the second sheet, lets call it 'Need for further training' (second sheet is in the same excel file), to contain a unique list from the above column, for example:

Name of Event
Training the trainers
Policy Update Training
Training Course on MIS
Training Session on Cloud Computing

Hopefully this explains it, thanks in advance guys!

1) copy "Name of Event" column
2) Paste on the other sheet
3) go to [Data] - [RemoveDuplicate]

Should do.

Originally Posted by jindon

1) copy "Name of Event" column
2) Paste on the other sheet
3) go to [Data] - [RemoveDuplicate]

Should do.

This will not work as the first sheet will be updated regularly and thus I need the second sheet to be dynamic and not static.

Then make it as an event driven code...

To a sheet module

Please Login or Register  to view this content.

Thanks guys! JapanDave, I'm very very fresh to excel macros, where exactly do I input this code. (Sorry about such a silly question). In addition to this the code is referring to the sheet 'Need for Further Training', but I can see its not referring to the first sheet 'Pre - General Training', why is that? Should it refer to 'Pre - General Training' , if that's where the duplicates are?

Thanks for your solution as well john, but is there a way I can avoid using a button, or is it the best way to go abouts this problem?

As you select "Need for further training" sheet, it updates

Thanks again John, I'm trying to run the macro but it keeps giving me the runtime error '9' (Sheets("Pre - General Training") = <Subscript Out of Range>

My thanks to all three of you. Im trying the codes now, but I think because of my lack of experience in Excel Macros, I maybe making some basic mistakes. I'm currently trying John's code, but it keeps coming up with errors.

This is my code right now (sheet one is actually called 'Pre - Training Assessme', its not a typo and the name can't be changed):

Sub DuplicateDelete()
'delete duplicates and copy to sheet2
Application.ScreenUpdating = 0
With Sheets("Pre - General Training Assessme")
Dim X As Long
Dim LastRow As Long

LastRow = Range("a65536").End(xlUp).Row
For X = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("a2:a" & X), Range("a" & X).Text) > 1 Then
Range("a" & X).EntireRow.Delete
End If
Next X
End With
Sheets("Pre - General Training Assessme").Range(Range("a2"), Range("a2").End(xlDown)).Copy Sheets("Need for Further Training").Range("A2") 'change Sheet1 or Sheet2 for your needs
Application.ScreenUpdating = 1
End Sub

There is an error 1004 (Application or Object Defined Error) showing up on the line:
Sheets("Pre - General Training Assessme").Range(Range("a2"), Range("a2").End(xlDown)).Copy Sheets("Need for Further Training").Range("A2") 'change Sheet1 or Sheet2 for your needs

Ok, let me try

@Dave, I just checked the code and yes Jindon made the changes you are suggesting. Are there any disadvantages of using this code as the solution? (I ask to increase my understanding of this code, )
@John, The code is not generating an error anymore, however its also not removing duplicates, its just as if its copying and pasting the entire column :S.

ssu

I haven't.

That 1 is for xlYes for header protocol.
As your data in original column has header, I made it 1.
If you don't want header at the destination, need a bit change.

@Jindon, ohh yeah, my bad, you didn't change it. But tbh, i need the header at the destination and its working like a charm.
@Dave. I'm going to try your code now and get back to you on it. The current amount of records are around 500, and data entry has just started, so one can estimate that the sheet will end up being quite large.

@Dave: If I want to run your code automatically and not as a button, what changes would I have to make to it?

@Dave, my bad, sorry about that! Right, so i have tried your new code and its telling me that variable 'a' is not defined.

Thanks dave! This works much better! The only problem is that is there are any deletions to the duplicate column in the first sheet, the code doesn't update the second sheet. However, fr my needs, this would not be a problem, as there would be any deletions to the first sheet, only additions. Thanks everyone for your help :D

Hey Dave, just saw your message. Your code looks good. What changes would I have to make to it if both my duplicate column and the column to paste the unique data are the second columns, not the first?

Re: your message.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

You started off asking for a macro OR a formula, but most of your replies have been macros (except for Marcol's). Here's a link to a site which describes how to do this using formulae:

http://www.get-digital-help.com/2009...om-one-column/

Hope this helps.

Pete