Create value list from a range of values

I've got a range of cells containing state abbrev. that I want to lis
in another range excluding all duplicated states abbrev's.

Is there a way to list the non-duplicated values automatically with 
function?

I've tried using the excel Data Filter menu but it seems that needs t
be performed manually. I need a function that automatically calculate
when there is a change or addition to my list of states.

Thanks for your help..

--
mallets12
-----------------------------------------------------------------------
mallets123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2509
View this thread: http://www.excelforum.com/showthread.php?threadid=50622

0
1/30/2006 12:18:24 AM
excel 39879 articles. 2 followers. Follow

1 Replies
501 Views

Similar Articles

[PageSpeed] 58

One way, using non-array formulas

Assume source data is in A1 down

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW())),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))

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

Select B1:C1, fill down to say, C100,
to cover the max expected extent of source data

Col B will auto-return the list of unique items within col A,
with all unique items neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mallets123" <mallets123.22f8na_1138580401.1397@excelforum-nospam.com> wrote
in message news:mallets123.22f8na_1138580401.1397@excelforum-nospam.com...
>
> I've got a range of cells containing state abbrev. that I want to list
> in another range excluding all duplicated states abbrev's.
>
> Is there a way to list the non-duplicated values automatically with a
> function?
>
> I've tried using the excel Data Filter menu but it seems that needs to
> be performed manually. I need a function that automatically calculates
> when there is a change or addition to my list of states.
>
> Thanks for your help...
>
>
> --
> mallets123
> ------------------------------------------------------------------------
> mallets123's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25090
> View this thread: http://www.excelforum.com/showthread.php?threadid=506224
>


0
demechanik (4694)
1/30/2006 2:57:28 AM
Reply:

Similar Artilces:

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

search a matrix for values
OK, this is a bit of a tricky problem.... i have a 7*7 matrix in excel, which is full of numbers... i want to search in only half the matrix, where half is from cell1-1, to cell 7-1 (i.e. top left to bottom right). what i want to search is for numbers a certain value, say X. what i then want to do is tablulate these numbers in one column, and then tablue late their position as a reference to the matrix (i.e. their matrix position). how on earth can i do this? Phil I have assumed that the matrix is in A1:G7 In A10 I entered =INDEX($A$1:$H$7,ROW(A1),ROW(A1)) and copied down to A16 In...

CRM 3.0/4.0: Views to show record that is created X Days Ago
I have a simple requirement to create a View with condition that the record is created x days ago, for example the record that is created 3 days ago. The only operator that is available for datetime (e.g. createdon) is only Last X Days, which if i show Last 3 Days, will show the records that were created today, yesterday, and 2 days ago. Is there any operator or any way to show the record that were created 3 days ago? I try to put condition "createdon Last 3 Days" and "NOT createdon Last 2 days", but there is no "NOT" operator in CRM. I try to insert new attribut...

print report according to value field
I am trying to print a report depending on a the field "OrderQty". i did created the code with the print command and the criteria and it looks like this: DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip DoCmd.PrintOut , , , , [OrderQty] however, it give me an error saying, that "|" field is not found. i looked through the code and tested the code, by putting 1 or 2 in place of OrderQty and it prints fine, that means to me that it's not the code and i looked to through the Query that the source of that R...

spreadsheet does not print as large as created
I have created a spreadsheet in Excel 2002. Simple layout with number, name, address,blank, blank,number. I created with large font size (28 or 32). I want the columns large enough for me to write in blanks and make notes. When the document is printed (hp laser 5p) the size of the font is not increased. Jim Morgan Memphis Tn Probably because that printer (driver) will not support that font and/or size. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Jim Morgan" <jmorgan7@midsouth.rr.com> wrote in message news:06ce01c34dfe$29c53d90$a401280a@phx.gb...

Create rule to run script
I have a vb script that when run takes the attachment on an e-mail and places it in a folder. Is there any way to setup a rule in outlook that when an e-mail is received from a certain person or address, to automatically run this script to place the attachment in the specified folder? Thanks in advance what version? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outl...

Creating a Combination Bar Chart with 2 Lines
I am trying to create a bar chart with 2 line series. The Y-axis for the vertical bars would be on the left and the Y-axis for the 2 line series would be on the right (3 series were defined). The horizontal axis for all series are calendar months (Jan, Feb, etc.) Every time I setup the chart my chart only displays a single line, ignoring the other line series. The vertical bars come out fine. Any thoughts on what I am doing wrong? Thanks Bill Falzone Don't use the built in types. Make the chart using all columns. Select a series that you want to change (click on it right in th...

how do i create a local client rule for outlook 2003
hello has anyone ever been able to create a local client rule for outlook 2003? exchange 2003? I can create the rule to send it to a PST (i dont know whether this can be done) then when i run the rule it says there is an error with the rule and it turns red. Does anyone have the steps for how to get this to work? Thanks A does the pst exist and is it in your profile? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: htt...

Setting up own block list (e2k3)
hello all, tried to create my own block list for exchange 2003 but i got an error. using telnet on port 25 ##### 220 xion.hybrid.intra Microsoft ESMTP MAIL Service, Version: 6.0.3790.0 ready ... helo 250 xion.hybrid.intra Hello [192.168.0.2] mail from:spam@spam.com 250 2.1.0 spam@spam.com....Sender OK rcpt to:support 421 4.1.0 Dropping connection due to an error on this server Connection to host lost. ##### everything runs fine when i disable my connection filtering rule. someone an idea ? tia ...

Algorithm to combine identical items in a list
I have a rather large worksheet that contains a list of all items in a warehouse inventory. I would like to be able to combine duplicate items as new inventory is added. Each item has (among other fields) an item ID, a color and a condition. I would like to combine only the lines where all of these fields are identical, summing the quantities in each record, and recalculating the cost based on the cost field in each record. Currently, I do this by starting at the top of the list and comparing each record to all of the records below it to the end of the list. This is rather time-cons...

can I add a link in Publisher to display, "join our email list"?
I am creating a fairly simply website and need to add a button for visitors to join our email list. Is this possible in Publisher? Please post your question in the web group and we will try to help you there: news://msnews.microsoft.com/microsoft.public.publisher.webdesign or http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.publisher.webdesign&lang=en&cr=US Always include the version of Publisher you are using, the URL of your site and as much detail as possible. DavidF "mfreecox" <mfreecox@discussions.microsoft.com> wr...

create a database
Steve can you email me I really need a database created as well and will pay you the fee. Sheryl On Sun, 21 Mar 2010 10:26:01 -0700, Sheryl <Sheryl@discussions.microsoft.com> wrote: >Steve can you email me I really need a database created as well and will pay >you the fee. Sheryl Luckily for you, he can't, because he doesn't know your email address. Don't post it here, or even worse spammers than Steve will pick it up. I would recommend that you find someone (perhaps locally) who can help you, rather than paying someone who has habitually and constantl...

Any idea why this is giving me a #Value! Error?
Function FrstLtrs(MyStr As String) As String Dim temp Dim i As Long TmpStr = Split(Trim(MyStr)) 'MsgBox "String" + TmpStr For i = 0 To UBound(TmpStr) If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not (UCase(TmpStr) = "THE") And _ Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then If Asc(Left(TmpStr(i), 1)) >= 65 And _ Asc(Left(TmpStr(i), 1)) <= 90 Then FrstLtrs = FrstLtrs & Left(TmpStr(i), 1) End If End If Next End Function Find the mo...

how to trend a value every 5 minutes
I have a valve linked to excell and would like to chart it to a line graph every 5 minutes Andrew, 1) Format cell A1 to Time (13:30) in the menu. 2) Format cell B1 to Custom (hh:mm;@) in the menu. 3) In cell A2, type"=SUM(A1+$B$1) This will add the start time to whatever value you have in cell B2, right now you want 5 minutes. 4) Copy and Paste A2 down the column until you have enough data. 5) If you decide to change the interval, all you ahve to do is change the time interval in cell B2. "andrew" wrote: > I have a valve linked to excell and would like to ch...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

change control source or value of text box
I have a report with GroupHeader0 and GroupHeader1. The data source of the report is a query. In the GroupHeader0 I have the Purchase Order Header info which includes the Purchase Order due date. In the GroupHeader1 I have the Purchase Order line info which includes the line due date. What I need is if the line due date is Null or blank to default to the PO header due date. How can I assign the header due date to the line due date. Can I change the control source of the text box in VB programming. -- MNJoe Put the following expression in the first blank field in your query: ...

creating a function for dividing two columns
I am trying to keep track of softball stats. I trying to make a column of batting averages. I have at bats in column b, hits in column c with the results in column d. I cant get the function right so it will display the correct result. use this =SUM(C1/B1) in cell D1. Format D1 as a number with 3 decimal places. Steve Hubbard "cflan" <cflan@discussions.microsoft.com> wrote in message news:212BF952-55B9-45DF-BC8C-E8B670FA6010@microsoft.com... > I am trying to keep track of softball stats. I trying to make a column of > batting averages. I have at bats in column b,...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

PO not listed for Invoicing
We have a Drop-ship PO with status Released and Revision 0. We can view it under All Purchase Order Transaction window. But when we try to invoice it from eith View->Purchasing Lists window or POP window, it's not listed under the Vendor name. Any help is appreciated. Any chance already assigned to an Invoice Batch? -- Mick "ThongD" wrote: > We have a Drop-ship PO with status Released and Revision 0. We can view it > under All Purchase Order Transaction window. But when we try to invoice it > from eith View->Purchasing Lists window or POP window, it&...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

what causes the recently used file list option to be unavailable .
Tools / Options / Recently used file list is greyed out - How do I correct this ? John You don't say which version so try searching the knowledge base http://support.microsoft.com/default.aspx With a search string of MRU Disabled in Excel -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "JohnPrice" <JohnPrice@discussions.microsoft.com> wrote in message news:ECA43906-EC33-4B92-8580-39923E449B9C@microsoft.com... > Tools / Options / Recently used file list is greyed out - How do I correct > this ? Hi! So...

Different background color depending on x-axis value
Hi! Is there any way to have the background in a chart have differen colors depending on x-axis values? Like x-axis value 1-10 => blue background x-axis value 10-20 => red background Thank -- RealRave ----------------------------------------------------------------------- RealRaven's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3705 View this thread: http://www.excelforum.com/showthread.php?threadid=56778 Here are a few of the ways you can get custom background fills for your charts" http://PeltierTech.com/Excel/Charts/ChartIndex.html#Backgro...

SPAM LISTING
Is there somewhere, on the internet, a list of SPAM sender that I can use to put filtering on my Exchange 2003 server? I have started to enter theme manualy but it's a long process!!!! Thanks! Jean-Seb I am using NoSPAMProxy. It has a cool monitor where I can blacklist something with one click. "John Smith" <john.smith@aol.com> schrieb im Newsbeitrag news:j5Whc.33230$CO3.1123537@news20.bellglobal.com... > Is there somewhere, on the internet, a list of SPAM sender that I can use to > put filtering on my Exchange 2003 server? I have started to enter theme > m...

maling list
how do i create a mailing list, or transfer one from yahoo contacts "omtradelaw" <omtradelaw@discussions.microsoft.com> wrote in message news:1EA2BADE-2D73-498E-AC3A-A3A05DADDA1D@microsoft.com... > how do i create a mailing list, Contacts - New - Distribution List > or transfer one from yahoo contacts Get the Yahoo Intellisynch. ...