re-produce/loate auto filter list

[Guest]

I have a data set with 10 fields, from the data set, I need to produce 5
separate list from 5 fileds in the data set which eliminiates all the
duplicates, are there any way where I can re-produce the list of variables in
the auto filter drop-down to another location? Or can I re-locate the "auto
filter buttom" to another worksheet?

 

[hansyt]

Suppose you have your values in A2 to A14.
Give your columns a header in A1 and B1 e.g. "d" and "m".
Highlight A1 to A14 and type a name in the name box e.g. "tab".
In B2 enter the following formula and copy into B3 to B14:

=IF(ISNA(VLOOKUP(A2;$A3:$A$15;1;FALSE));1;0)

Depending on your config replace the ";" with ",".

That marks all entries with a "1" except the duplicates which are
marked "0". Save this workbook as e.g. "file1.xls".

Create a new worksheet and select data-->get external data-->new
database query

From the next window select "Excel files" and select and double click

the just created file "file1.xls".
In the next window you will see the name "tab" from your "file1.xls".
Click "tab" and then the right arrow to move the two fields "d" and "m"
into the right box.
Click next.
Click next.
Select "View data or edit Query" and click finish.
Highlight the first entry in column "m" and select criteria-->Add
criteria
In the next box enter 1 in field "value" and click "Add".
Close the box
Select File-->Return to MS Excel
Select "properties" and click "ok"
select "refresh on file open" and any other options you may find
appropriate
click "ok"
click "ok"

On your worksheet you will find all unique entries of the workbook
"file1.xls".

This also applies for other columns that you want to extract unique
items from.

A bit complicated but it should work.

Hans

 

[Debra Dalgleish]

You can use an Advanced Filter to extract the unique values from a
column. There are instructions in Excel's Help, and here:

http://www.contextures.com/xladvfilter01.html