Creating list of names from existing without duplicating data?

Turbothorup · Jul 12th 2004

I had a previous post where no one replied.....after having read it another time I will now try to rephrase my question.

I have three lists of names, some of the names are in more than one list, others only in one of them. I want to create a new list consisting of the 3 existing lists but without duplicates... So for instance:

1. list:
BMW
AUDI
Mercedes

2. list:
AUDI
Renault
Peugeot
Nissan

3rd list:
Mercedes
Volvo

and 4th list should look like this:

BMW
AUDI
Mercedes
Renault
Peugeot
Nissan
Volvo

All the names without duplicates........and remember it is only in an excel spreadsheet without any VBA or pivottable......did it by "consolidating", but in that case I need a measure to consolidate....and I do not have that

BeginnerBob · Jul 12th 2004

Are these list is diiferent spreadsheets or in different columns e.g column A = List 1, column B = List 2 etc?

Foxxy1 · Jul 12th 2004

Filter

What you nned to do is use the Filter found in the Data Menu pulldown, but instead of using Autofilter, use Advanced filter. Once inside that window decide wheter you want to copy it, where the lists are, but be sure to check the "unique records only" box. That will give you a list of all the items one time each.

Turbothorup · Jul 13th 2004

thank you very much.......and btw. they are in the same spreadsheet, different worksheets......
I will go with the filter.....hope it works out, thanks

Turbothorup · Jul 13th 2004

It does not work.......When I mark the list I want to filter and choose "copy to another location" and then check the "unique records only" it just copy the cells........maybe I am doing it wrong.....I leave "criteria range" empty? Anyway......this function should be able to filter thre lists in three different sheets - all in the same workbook.....can't see how i do it with this function?

Turbothorup · Jul 13th 2004

I think I have been through all the functions.....can't believe that there should not be a function for something as simple?

Jong · Jul 13th 2004

I would think that there would be a function to acheive this. If you cannot find one, then you can code your own.

Lets say this range is a list of unsorted values:

Worksheets("One").Range("A1:A20")

The resulting list will be put in Worksheets("Two").Range("A1:A20")

Code

Dim ResultCounter as Integer
Dim Flag as Boolean


ResultCounter = 1


for each Acell in Worksheets("One").Range("A1:A20")
     Flag = false
     
     for each Bcell in Worksheets("Two").Range("A1:A" & ResultCounter)
          if ACell.Value = BCell.Value then
             Flag = true
             exit for
          end if     
     next BCell
     
     if Flag = false then
          Worksheets("Two").Range("A" & ResultCounter) = ACell.Value
          ResultCounter = ResultCounter + 1
     end if
     
next Acell

Display More

I just wrote the above code from memory, it is untested. This should give you a pretty good idea as to what you will need to do what is needed to do. The above code will only work if your list is all located in the same place. It will sort out the duplicates.

Hope it helps.

Turbothorup · Jul 13th 2004

Thank you very much........I had just hoped there would be an ecel function......it is for one of my colleagues and he is not so much into excel, so I would prefer if I could do it with an existing function? But maybe it is not possible.......this would just surprise me since some of the other functions do the same..........the problem is that they do something more in addition which I do not need.....

Foxxy1 · Jul 13th 2004

Do you have an example

If you give me an example I'll see if I can figure out what is wrong with the Advanced Filter, if you'd like.

Turbothorup · Jul 13th 2004

have sent you a trialsheet, foxxy1........thanks

Creating list of names from existing without duplicating data?

Participate now!

Share