MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel VBA


Posted by Opeyemi Ola on March 05, 2002 1:54 PM

I have a long list of names with a lot of repetion in it and I need to compile a list of 'unique' names. Does anyone know how to do this in Excel?


Posted by Nemo on March 06, 2002 11:03 AM

use Advanced Filter from menu DATA->Filters
(see Excel Help for details)
1. first row of list must be header
2. on advanced filter form :
-check enother location
-clear criteria range
-put cursor to destination location adress box
-show first(left-top) cel of destination location (it must locate on sheet with filtered list)
-check unique box
and GO !

Nemo
P.S. I have polish version and do'nt remember english menu names.

Posted by Nemo on March 06, 2002 12:36 PM

Previous procedure make unique list from one-column list.

If you have multi column list
eg.
(column)(A) (B) (C)
(1 row)Name Last_name Address
(2 row)Adam Smith Minesota
(3 row)Ian Smith Wayoming
(4 row)Adam Smith Minesota

1.sort this list on column Last_name and Name
2.add new column named "UNIQUE" (first free column on right of list)
3. set cells format in this column to GENERAL (it necesary to evaluate macro)
4. put in first cell column UNIQUE formula:
=and(A2=A1;B2=B1)
5. fill column this formula to the last row of list

;trick: (in this example)
select cells D2
move with vertical scrollbar of sheet to last row of list
press SHIFT and hold and select cell in column D in last row of list (this select desirable range in column D)
press CTRL+ D (fill selected range with contents of first cell in selection)
;end trick

(column)(A) (B) (C) (new D)
Name Last_name Address UNIQUE result of formula
Adam Smith Minesota =and(A2=A1;B2=B1) FALSE
Adam Smith Minesota =and(A3=A2;B3=B2) TRUE
Ian Smith Minesota =and(A4=A3;B4=B3) FALSE

6.change formulas in column UNIQUE to values
(copy column D, and PASTE SPECIAL checked option VALUES )
7. sort list on column UNIQUE

Nemo

Posted by noname on March 20, 2002 11:57 AM

Yes.
Try sorting the data tru a new sheet:
To do this, select the range contai ning all the data and use the menu:
Data | Filter | Advanced Filter
You may experiment whit this a litle to find the solution that suits your needs.