Pull Unique Values From a List/Table

How can I pull unique values from a table?  I track vendors who might sell
multiple items, but I only need to show the vendor once so I can pull other
information.  There are also may be blanks in the rows if the vendor is
inactive.

Thanks!


0
kfb1 (138)
4/2/2004 6:28:42 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
484 Views

Similar Articles

[PageSpeed] 55

Let me add one more twist to this problem:

The unique values also need to filter out where it may be 'VendorA - West'
and 'VendorA - East' and combine them into one record.  I only need it to
show VendorA and not both records East and West (same company, just
different drop ship location).

Thanks!

"Karl Burrows" <kfb1@spambellsouth.net> wrote in message
news:KG7bc.3156$ts4.3108@bignews3.bellsouth.net...
> How can I pull unique values from a table?  I track vendors who might sell
> multiple items, but I only need to show the vendor once so I can pull
other
> information.  There are also may be blanks in the rows if the vendor is
> inactive.
>
> Thanks!
>
>


0
kfb1 (138)
4/2/2004 7:08:00 AM
I suggest that you add a helper column and deduce the name from it with a
formula. IF you are unsure how to do that give us some rules and we can
help.

Then do an advanced filter on the helper column, copying unique values.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Karl Burrows" <kfb1@spambellsouth.net> wrote in message
news:Td8bc.3784$b_5.749@bignews5.bellsouth.net...
> Let me add one more twist to this problem:
>
> The unique values also need to filter out where it may be 'VendorA - West'
> and 'VendorA - East' and combine them into one record.  I only need it to
> show VendorA and not both records East and West (same company, just
> different drop ship location).
>
> Thanks!
>
> "Karl Burrows" <kfb1@spambellsouth.net> wrote in message
> news:KG7bc.3156$ts4.3108@bignews3.bellsouth.net...
> > How can I pull unique values from a table?  I track vendors who might
sell
> > multiple items, but I only need to show the vendor once so I can pull
> other
> > information.  There are also may be blanks in the rows if the vendor is
> > inactive.
> >
> > Thanks!
> >
> >
>
>


0
bob.phillips1 (6510)
4/2/2004 8:53:44 AM
Hi
In addition to the comments you already receive you may have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

--
Regards
Frank Kabel
Frankfurt, Germany


Karl Burrows wrote:
> How can I pull unique values from a table?  I track vendors who might
> sell multiple items, but I only need to show the vendor once so I can
> pull other information.  There are also may be blanks in the rows if
> the vendor is inactive.
>
> Thanks!

0
frank.kabel (11126)
4/2/2004 9:27:38 AM
Reply:

Similar Artilces:

Select a random value from a column of values
Hi, I have never programmed Excel before, but do use it a fair amount, including use of built-in formulae. I would like to be able to select a random value among a group of values - is this possible? So for example, in a spreadsheet I have in column A a list of names from cell 1 to cell 20. then I want to pick a name from random from that list. How would I do? Any input would be appreciated. -- Paul paulastya@gmail.com PS I do not have visual Studio/VB installed on my machine. someone else will give you a simpler formula, but, if not, the following will work: =INDIRECT("A"&a...

Access 2007. Combo box list template
I am intending to use combo boxes on Forms to support data entry. I am wondering if there are templates already created that i can import, such as Countries, States, Counties, etc.. Also is there any design benefit to using the combo box in the bound or unbound state. Thanks There's a couple of lists here, for US zip codes and Australian postal codes: http://allenbrowne.com/ser-65.html Bound combos are ideal where you need Access to store a value. Unbound combos are ideal for jumping to a record or applying a filter, e.g.: http://allenbrowne.com/ser-03.html -- Allen Browne...

Default numeric value
I am creating a spreadsheet dealing with dollars. There are several formulas that I have created using percentages. One particular formula multiplies a percent in one cell by the $ value in another. If I have no $ value in the cell the formula uses a '1' instead of what I would expect '0'. How can I set up my sheet so that if a cell is blank the default value will be '0'? Would help to see your formula, but: =if(isblank(a1),"",a1*b1) ************ Anne Troy VBA Project Manager www.OfficeArticles.com "thomson" <thomson@discussions.microsof...

filter a list
Hi all, In the "City" column, we type city with capital case in order to identify the donors. Now I need to mass mailing to the donors only. Is there any possibilty that I can filter all cities typed with "cpital case"? Thanks in advance for your help! Regards, L.J Add a new column to the table, with the heading "Donor" In the cell below the heading, enter the formula: =EXACT(D2,UPPER(D2)) where city is in column D Copy the formula down to the last row of data Filter the table for TRUE in the Donor column. L.J. wrote: > In the "City" co...

Unique Lead Id like Case ID
Hi All, I want to attach a unique id with lead just like we have for case, quote, contracts,etc(CAS-10225-xv72s). C Can i know as to which procedure, function or DLL is doing so or do i need to write my own utility. If i need to write my own, how shall i go abot this? Thanks in advance. Regards, Piyush There are multiple ways to achieve this but they all depend on using code to get the next id number. For instance after adding a field to lead to store the id number you could add a workflow rule that calls a .net assembly to find your last id and add one to it. You can also do this us...

Pivot tables
Two questions regarding pivot tables. 1 Is there a way to use auto filter to limit the items included in row fields? That is, instead of checking or unchecking items in the drop down box use filters to say only include accounts greater than 5000 and less than 7000. I got around this by filtering the source of the data, copying it to another sheet and changing the source of the pivot table to the new sheet, but that is cumbersome. 2 Can conditional formatting be used within pivot tables? I have an actual field, a budget field and a calculated variance field. I would like to have variances...

Off Topic: Pulling Hair Out
Want to have some fun? Search microsoft.public.pos on google groups for "hair." You'll get all of the posts about people pulling thier hair out. Hint: There's more than one page. ...

Table of contents #2
I have a multi page spreadsheet at sheet1. Then created a table of contents at sheet2. By using a formula (=name! location) I have the page name in my Table of Contents from the spreadsheet. Each spreadsheet page is numbered by using (page set up, custom footer, &page). How do i get the number into the Table of Contents? hi, don't think that will work. &page just numbers the page starting at 1. so all the pages have 1. what are you trying to do? create a goto funciton? if so you might have better luck using cell addresses(upper left corner) or range names. >-----Orig...

Pull down menu
Hello, I am trying find a way to make a pull down menu in excel. I have a list of foods on one sheet in a workbook where all the nutrition information is stored per food type where the row indicates the food type and the columns indicate the nutrition info ie: Carbs, Protein, Fats, Calories etc. This sheet has roughly 300 or so food items listed in rows. How can I create a pull down menu in another cell on another worksheet as a method to select a food item from the food sheet? Any help is appreciated, Dave --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (...

Users with Email address not in All Users list
I have some users with Email addresses established (no mailbox) and would like to have them appear in the All Users recipient list. How would I do that? ...

2007
Hi! - I was hoping someone could help me please. Here's what I have. A worksheet that has several dates in a row. D1:T1, Of that range, I have conditional formatting set to highlight a date(s) either = to or within the next 7 days. I need to be able to pull the date next closest or equal to today. Can anyone help me out? Any help would be greatly appriecated. Shaun > I was hoping someone could help me please. Here's what I have. A > worksheet that has several dates in a row. D1:T1, Of that range, I > have conditional formatting set to highlight a date(s) either = to or...

dyanmic picklist values
OK so I have set up a dynamic pick list but have run into a minor problem. I would like my second list to default to a unassigned or blank value, no matter what value is chosen on the first list? I have tried to update my values by adding a blank value at the start of my values iStartIndex = 0; iEndIndex = 0; iStartIndex = 35; iEndIndex = 39; This however does nothing, and since I can not set a blank value in the picklist, I am stuck. Any ideas or suggestions??? Denzel, You should add a value to you SubPicklist and call it "Unassigned" then reference that value in the Ca...

Building folder-list in Excel
Hey guys You have kindly helped me before, and I dare to consult you again. I want to make a Excel-list of all subfolders in folder - the path = value in cell A1. If there are no subfolders then a msgbox will appear "Folder dos'nt exist". Proberbly a simple VBA-trick - but I'm not that kind of magician ...so..will you please help me again? Best regards Snoopy Option Explicit Private cnt As Long Private arfiles Private level As Long Sub Folders() Dim i As Long Dim sFolder As String Dim iStart As Long Dim iEnd As Long Dim fOutline As Boolean arfiles = Array() cnt = ...

word cannot be pulled up
Do I need a updated version of word. Cannot bring up any word files now. 99 version. If you are saying that you cannot double-click to open Word documents, see http://word.mvps.org/faqs/apperrors/reregisterword.htm. Note that there is no "Word 99"; you need to look at Help | About Word to find out which version you are actually using. -- Stefan Blom Microsoft Word MVP "rentals" <rentals@discussions.microsoft.com> wrote in message news:12565263-2B58-4C40-A0C3-192A933FF438@microsoft.com... > Do I need a updated version of word. Cannot br...

Display percent and value on stacked column chart
I'm displaying cumulative $ totals from 3 sales groups on a 100% stacked column chart. We've recently exceeded 100% of our total $ goal so the chart isn't able to display the amount above 100%. If I switch to a standard stacked column chart, I can't choose the display percentage option. I downloaded the Rob Bovey's Chart Labeler but I can't get it to display what I want. Perfect world would be displying the dollar value of each sales group in the column segments and showing percent of total goal on the y-axis. I would even be okay with showing the dollar valu...

MS Query - Date/Time
I'm running a querry to bring data into Excel from a Oracle database. The dates in the Oracle database are in the Date/Time format and I need to average values for the each day. The information in Excel is in a pivot table format and shows multiple rows for each date. I believe this is because of the time portion of the date. I've formatted the date in Excel to MM/DD/YYYY and this does not change the results. Help! ...

Pivot tables downgrade 2007->2003
Welcome. I've created a few pivot tables in my report in Ex2007. The problem is that there is a group of ppl who have Ex2003 and these pivot tables looses their functionality (expanding) when opened in this version of Excel. Of course files are saved as *.xls format but it doesn't change anything. The one way to solve this problem is to create once again these pvt tables in Ex2003 using source table, but i think its just time wasting process. Besides there are pvt tables without source, so i would have to generate them again. I'll appreciate any hint helping me...

Assigning values to a variable set
I am trying to create a spreadsheet that allows me to assign a predetermined number to a specific set of numbers. (This is to help me with automation of grading students.) There is a predetermined grid we must follow that has an x axis zero to 10 The y axis is zero to 50. The rest of the grid tells the proper grade that a student would get is they score specific numbers. For example a 50 and a 10 would be a 100%, a 45 and a 7 would be another number like a 83% I want to be able to enter the 2 (X and Y grid) numbers for students and have excel calculate the proper percentage ...

Data values contain Parentheses (N) with String inside
I have a database which has data values in one field where the data contains values that have parentheses which also contain a string (Letter). One value has a full.stop in the middle of the name. When trying to use these data values in an IN clause I get an error which seems to suggest Access SQL is interpreting the parentheses as a Function, which it isn't. They are just plain vanilla data values being used in the SQL. Actual examples of these data values are: Canterbury(N) M.Valley(N) Randwick(I) How can I successfully deploy these values into (a) an IN claus...

desktop shortcuts to another user's task list?
Exchange 2003, Outlook 2003, Windows 2000/XP I would like to be able to put a shortcut on a user's desktop to another person's shared task list. Is this possible? No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After a lot of head-scratching, Jason Gallas asked: | Exchange 2003, Outlook 2003, Windows 2000/XP | | I would like to be able to put a shortcut on a user's desktop to | another person's share...

Catalog values
How do you catalog every value or word from every cell in alphanumeric order with the number of respective repetitions? Ex. If every cell in a range contains a description of various scientific results in sentence form, the goal would be to list all words and values found as discrete elements (separated by spaces) and to list the number of times each was found in adjacent cells. The purpose for this application would be to find keywords and flag their associated records (rows). ...

Excel 03 DB query pulls data in wrong order, how to resolve?
New user to Excel DB queries. Have set up DB and query, however query does not import DB in the column-order of the query. This worked fine the past couple months. Dont understand why it wont work this month. Have re-created query and the problem still exists. Cant find any information on how to troubleshoot this issue. Thanks for your help. Data>Import External Data>Data Range Properties Uncheck Preserve column sort/filter/layout Does that help? -- Regards, Ron ...

How block a highlighted list of Senders not one-at-time ?
New Windows 7 (prof level but for single-user home use) with Office 2007, trying to add hundreds of Junk emails to list of Blocked senders but think that is failing beause still geting many of the same senders coming through. Tried deleting them, both in highlighted lists and one-at-a-time, but Adding to Blocked Senders list can only be dome one-at-a-time; and still seems to be not working. Somebody know what I'm not doing, or doing wrong, please ? Blocked Senders is only marginally useful because the spammers just change their apparent addresses on a near-hourly basi...

Excel to protected application? Unique Computer ID
I have a excel spreadsheet that ideally I would like to run as a standalone application. Is it possible to do this? I want to distribute by disk - but restrict the use of the to a specific pc. Is it possible to locate a Unique Computer ID variable in Excel (such as MAC address) to implement a key system? Any help appreciated. See Chris Rae's page at http://www.chrisrae.com/vba/routines/getusername.html and additional information at http://www.mvps.org/dmcritchie/excel/property.htm---HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]My Excel Pages: htt...

Freezing Table Headers and Column Headers
I am able to freeze table headers and column headers. Simple report with a one row table header. Table Headers : Tablix property, row group, advance mode, static, set fixed data to true Column: same using column groups Issue is when both are frozen and I scroll horizontally everything is fine. When I scroll vertically everything is fine. If I scroll vertically then horizontally the frozen column disappears. If I scroll horizontially then vertically the frozen column disappears. Can anyone explain this behavior? What am I missing. ...