AutoFilter list of values

When I activate the auto filter, a drop down lists the unique values within 
the column. How could I get this list of unique values?
0
RJH (1)
4/19/2005 2:56:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
661 Views

Similar Articles

[PageSpeed] 15

Hi RJH,

As far as I know this can only be done with advanced filter.
Select Data-Filter-Advanced Filter and check the Unique records only
box.

Cheers,
JF.

0
4/19/2005 3:36:01 PM
Another way to extract the list of unique values

Suppose the data is in col A, A1 down

Put in C1: =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

Put in B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select B1:C1 and copy down till the last row of data in col A
Col B will return the list of unique values in col A
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"R.J.H." <RJH@discussions.microsoft.com> wrote in message
news:2CFB96BF-9F5C-4D76-A978-C5ED0D125FD5@microsoft.com...
> When I activate the auto filter, a drop down lists the unique values
within
> the column. How could I get this list of unique values?


0
demechanik (4694)
4/19/2005 3:49:03 PM
Reply:

Similar Artilces:

Create a bar chart from a list?
I have a table, "A1:B10". Col "A" list of names, Col"B" values. How do I create a bar chart that plots the names which only have values? -- Bob "Bob" wrote: > I have a table, "A1:B10". Col "A" list of names, Col"B" values. > How do I create a bar chart that plots the names which only have values? > -- > Bob Have you tried selecting just the cells that have names and values and then going to Insert>chart? You can select multiple cells by holding the CTRl button and the mouse to select. I am not sure ho...

envelope list from excel
I have a list of names and addresses in an excel work sheet. How can I generate a label list for a mailing from it? Louis Louis Labels from within Excel alone is a trial and error operation. Best to use Word for the mailmerge operation. For help on Word mail merge using Excel as the data source. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm And a training tutorial for creating envelopes and labels. http://office.microsoft.com/training/training.aspx?AssetID...

Autofilter
Is it possible to autofilter a single column within a project? I am using Project 2007. Thanks Larry Larry, I am not sure what you mean by Autofilter a single column. Can you give more detail as to what you are trying to do? "External Update" wrote: > Is it possible to autofilter a single column within a project? I am using > Project 2007. > Thanks > > Larry Hello Larry, Sure. Turn on Autofilters and use the drop-down filter only on the single field. The Autofilter icons will appear at each field heading, but you need not use them...

Outlook 2003 Safe/Blocked senders list
Hi, I am working on a custom made spam filtering and I would like to access the Outlook 2003 Safe/Blocked senders list from my application (made in Delphi). Is there a way to do it or it is not opened? Thanks: Peter ...

How do I put the name of a data point (not the value) next to the.
I have created an XY chart. For each data point (x,y) there is a name. I want the name to appear on the chart next to the point itself, not along one of the sides of the chart. Any suggestions? Have you looked at Ron Bovey's XY Chart Labeler? http://www.appspro.com/Utilities/ChartLabeler.htm "Colts&PacersFan" <Colts&PacersFan@discussions.microsoft.com> wrote in message news:C964F875-A40B-4482-B4D0-6F03FEF172B8@microsoft.com... > I have created an XY chart. > For each data point (x,y) there is a name. > I want the name to appear on the chart next to the...

Paste damages formula in autofilter #3
That doesn't seem to work as the paste sends a #REF into the offset o paste so the formula becomes =IF(OFFSET(#REF!,0,-1)>0,1,0) on the righ which returns the #REF error. Think solution would be easier to solve with a macro executin everytime an update occurs maybe?. There's got be a way to do it cause I have seen a worksheet where i works and I don't believe it is likely it was done with macros. (sidenote, database is a central one used to modify other worksheet using macros. Each of these worksheets uses different sets of filters The data pane on the right records subtotal...

how do I add numbers in list of columns
entered numbers in columns of excel and want to total them going horizontal and vertical to get grand total for each column. Can you explain how to do this? For e.g. You enter 1-10 in A1-A10 You enter 11-20 in B1-B10 So, in A11, you type SUM(A1:A10) to get Column Grand total and copy it to B11 to get Grand column total for B And, you type SUM(A1:B1) in C1, to get Row Grand Total and copy it to C2 to C10 to get all Row Grand total Hope this helps. "Amadauss" wrote: > entered numbers in columns of excel and want to total them going horizontal > and vertical to get gr...

Price List #3
We have customers with the same prices, except some items. How can I copy an existing pricelist and change only the items with a different price to assign the new pricelist to the customer? -- Toni Go to Price List Utilities On Sep 25, 5:14=A0pm, Toni <T...@discussions.microsoft.com> wrote: > We have customers with the same prices, except some items. How can I copy= an > existing pricelist and change only the items with a different price to as= sign > the new pricelist to the customer? > -- > Toni This was my first step, but Price List Utilities let you copy only i...

shortcut on autofilter function
i have many columns of data using the autofilter. is there a shortcut that either returns all the fields to the all function or a shortcut that returns all the data to the page thanks One line of code will do this (maybe put in your Personal.xls): Sub Foo() Activesheet.ShowAllData End Sub Then assign it to a New (Macro) Icon in your toolbar. It will always be available on any sheet that has an autofilter range assigned. HTH "derwood" <darren.irvine@gmail.com> wrote in message news:1132654133.294598.246590@g43g2000cwa.googlegroups.com... >i have many columns of data u...

distribution list #44
I have a user who created a whole bunch of email distribution list which now need to be moved to a separate "contact" folder. How can I choose all the contacts in a distribution list and move them to a contact list as individuals and not a distribution list. Thanks, Raul Rego NJPIES PS using exchange 2003 and Outlook 2007 you'll need to go through the whole list and pick and choose then move them yourself. Now... if you just need the addresses into individual contacts you can save the DL as a text file (or use Send, in internet format to create a text file) and impor...

AutoFilter and Frequency Function
I am trying to see if I can create a frequency function that will change as I autofilter different criteria. I have a file with over 15,000 rows returning data on sales opportunities including key fields I want to filter on: organization, product line, geography and status (Won, Lost, Open). In each case I want to see the change in distribution of "days in funnel" whether I am looking at all the data or just one geography or just one product line. It appears that the Frequency function does not recognize the filtered out data and continues to return values for the entire data set....

How do I enter lists into names?
Hello everybody Currently I am using a named range as a list for the data validation function. To make my sheet more idiot-proof, I'd like to enter the items from the named range directly into the name definition (via Insert-Name-Define or VBA). Is this possible? What syntax do I need to use? Greetings from Switzerland Urs 1st - Insert a tab and label it "Departments", that you can hid later put the data that you want in a column, like this: A1= Creative A2= Marketing A3= HR Just keep going down Columns "A" until you hav...

problems with password list
I'm having difficulty with Outlook 2002 and saving email account passwords. Even when I prompt the application to save my passwords, I always have to input the email address again when I open Outlook later. What's going on? For Outlook 2000 or 2002 on Win98, NT, or 2000: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q290684 For Outlook 2000 on Windows 2000: (CW) http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q275465 (IMO) http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q259416 For all Outlook versions on Windows XP (just ignore the parts that refer ...

how to match between two list using letters
Hi, I have two sheets as follows: sheet1 A--------------B 34145 D173abcd 333215 TI242EFG 344233 E0124ABCD Sheet2 A-----------B------------C D-173 abcd ? B-241 efgh ? TI-242 XYZ ? E-0124 STQ ? What I want to do is taking the value from Colum A in sheet1 and put it in column C in sheet 2 using Sheet1 Column B as an indicator. Please note that the Column A in sheet2 has the same data of Column B in sheet1 but there are extra letters and characters. In C1 of sheet2 and copy dow...

Calculating two values that are in different records
Hi, I have a query called SalesBudget3 that has fields which include: CustomerName, Type and then all of the months of the year (January, February). The type field is either Actual or Budget. My query will return two records per client. The first would have their actual sales for 2008 and the second would have their budgeted sales. In my report, I have the field names in the page header (Type, January etc) and CustomerName in the CustomerName header. The Details sections has their monthly sales figures. In the CustomerName footer, I want to calculate percent of Actual to Budget pe...

My Reports List
In Great Plains 10, I've added 12 reports to the My Reports List on my home page. I can only see 10 of them, but I have a "More..." option at the bottom of the list. When I point the cursor at that "More..." option, the text turns blue and is underlined. When I click on it, though, nothing happens. How do I see the rest of the reports in my list? Thanks in advance! Mary Fetsch On Jul 29, 10:20=A0am, Mary Fetsch <MaryFet...@discussions.microsoft.com> wrote: > In Great Plains 10, I've added 12 reports to the My Reports List on my ho= me ...

list function
I have 10 student names put in from cell a1 to a10 in sheet1, and their age put in from b1 to b10. I created a list of student names by selecting cell a1 to a10. I go to sheet2, cell a1: I created a drop drop list of student name. I want cell b1 in sheet2 to auto pop up the age of whoever I choose from the drop down list in cell a1:sheet2. The age is according to the age entered in sheet1. What is the exact formulas for me to this in cell b1:sheet2 or is this possible at all? =IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0)) -- Regards, Peo Sjoblom &q...

force unique values in a range of cells?
Is there some way to force unique values in a range of cells? Thanks, Keith See http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Ron de Bruin http://www.rondebruin.nl "keithb" <k31thb@yahoo.com> wrote in message news:udteP9EnFHA.3380@TK2MSFTNGP12.phx.gbl... > Is there some way to force unique values in a range of cells? > > Thanks, > > Keith > ...

Lookup and Reference Lists
Here's the situation. In Sheet1 I have a list of about 1200 items in following format: T0000 ---- AAA 00000 BBB 11111 CCC 22222 T0001 ---- AAA 11111 DDD 33333 and so on. In Sheet2, as follows: T0000 T0500 T0700 AAA BBB CCC DDD T0003 T0100 T0900 T1000 T0350 AAA BBB CCC DDD There are about 300 of these blocks in Sheet2. And my task is to fill out the blank cells under each id numbers in Sheet2 with their corresponding values (if any) in Sheet1. I am fairly new to Excel and don't have a clue as to how to go about this...

List Box Help Please
I have a subform on a main form. I have two list boxes in the sub form. I want List box 2 to update when I choose from list box 1. This is what I currently have. Private.Sub 1st_ListBox_AfrterUpdate() Me.1st_ListBox = "" Me.1st_ListBox.Requery Me.Requery End Sub Private Sub 2nd_ListBox_AfterUpdat() Me.Requery End Sub I would also like my list boxes to have a default of Null. I would appreciate any help as this is not working. Thank you in advance. Please provide a bit more description of what you want to accomplish. "I want Listbox 2 to update" do...

Folder File list disappears.......
I have what might be a unique situation, or perhaps one I've just not seen reported in these forums before. I've just re-installed WinXP on my main notebook. Given the number of apps that had to be reinstalled, and the various specialized drivers that were needed, I'm not certain exactly when this problem began, but nonetheless..... Not always, but often, when I double click to open a folder, the folder opens, I can see the file list, but then the folder screen clears. If I leave it as such (and often there seems to be no other option) the files refresh after a ...

How do I print out a list of the tabs in an Excel workbook?
I have a large workbook that tracks computers for a company I work for, one tab per computer. I would like to print out jsut a list of the tab names as sort of an index file and check list for a physical inventory. Is this possible? thanks This macro will list all the sheet names in order in Column A of the active sheet, starting in A2. Column A must be empty for this to work. HTH Otto Sub ListShtNames() Dim ws as Worksheet For Each ws In ActiveWorkbook.Worksheets Range("A" & Rows.Count).End(xlUp).Offset(1).Value = ws.Name Next ws End Sub "Britom...

autofilter
hello and good new year i'm on w2k and office 2003 on excel when i do two cols A B 1 =a+1 2 =a+1 etc..... i go to for example 500 and after i make auto filter on A and B if i use autofilter i see Filter mode but if after i do only 10 line and not 500 i have 1 of 500 records found and not 1 of 10. i have two problem : with 500 line i have not the count of record found and if i delete some line i must desable autofilter for have the good count of line thanks for your help Debra Dalgleish as some work arounds at: http://www.contextures.com/xlautofilter02.ht...

Entourage talk list down temporarily
For those that also subscribe to the Entourage talk list..... The list will be down for up to two weeks. Received word from the list mom: > we suffered a major hardware failure (CPU + hard drive) Once new computer arrives and is set up, the list should resume as normal. In case you're wondering what's the difference between this newsgroup and the talk list.... The talk list is via email where the newsgroup is available through your newsreader or through Google's web interface. Users tend to stay with the talk list for long periods of time rather than visit for just help wit...

Income in Payee List?
Why does my income (paychecks, child support received, etc.) show up in my Payee list. Are they supposed to? Thanks -- Lori In microsoft.public.money, Lori wrote: >Why does my income (paychecks, child support received, etc.) show up in my >Payee list. Are they supposed to? Thanks It's up to you. If you want to rename or move those "payees" to something more meaningful to you, there are buttons to make those choices. Just select the odd payee, and choose Rename, Move or Delete. Move or Delete will let you combine payees. I'm not sure I understand what you mean by...