Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
re-produce/loate auto filter list
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
re-produce/loate auto filter list
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
re-produce/loate auto filter list
You can use an Advanced Filter to extract the unique values from a
column. There are instructions in Excel's Help, and he http://www.contextures.com/xladvfilter01.html Sin wrote: 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? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get a reverse filter list? | Excel Discussion (Misc queries) | |||
Using Auto Filter | Excel Worksheet Functions | |||
Using advanced filter to search for criteria in a list | Excel Discussion (Misc queries) | |||
Auto Filter Multiple Selections | Excel Discussion (Misc queries) | |||
Auto Filter not working properly | Excel Discussion (Misc queries) |