How to select one record of each from a column

Hello,
I have a large worksheet with data.

Here are the columns (a, b & c):

Order #		Employee	               Hrs.
1000		Mike		2
2000		Mike		1
1000		Paul		2
3000		Rose		3
8000		Mike		1
3000		Mike		2
1000		Rose		1
4000		Paul		1
7000		Jhon		3
5000		Rose		2
1000		Jhon		3
6000		Mike		1
9000		Mike		3
1000		Mike		2

Is there a formula to select only one of each record on "Order #" (column a) 
and put them on another column (column d)???

Once the formula is created, column d should look like this:

1000
2000
3000
4000
5000
6000
7000
8000
9000

Help please!!!

Thank you,
Cesar Urquidi
0
Utf
11/20/2009 5:19:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
681 Views

Similar Articles

[PageSpeed] 26

In D1 enter formula
=A1

In D2 enter the below formula and copy down as required. 
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' you can notice the curly 
braces at both ends like "{=<formula>}"

=IF(MIN(IF(ISNA(MATCH($A$1:$A$15,$D$1:D1,0)),ROW($A$1:$A$15))),
INDEX($A$1:$A$15,MIN(IF(ISNA(MATCH($A$1:$A$15,$D$1:D1,0)),
ROW($A$1:$A$15)))),"")

If this post helps click Yes
---------------
Jacob Skaria


"Cesar Urquidi" wrote:

> Hello,
> I have a large worksheet with data.
> 
> Here are the columns (a, b & c):
> 
> Order #		Employee	               Hrs.
> 1000		Mike		2
> 2000		Mike		1
> 1000		Paul		2
> 3000		Rose		3
> 8000		Mike		1
> 3000		Mike		2
> 1000		Rose		1
> 4000		Paul		1
> 7000		Jhon		3
> 5000		Rose		2
> 1000		Jhon		3
> 6000		Mike		1
> 9000		Mike		3
> 1000		Mike		2
> 
> Is there a formula to select only one of each record on "Order #" (column a) 
> and put them on another column (column d)???
> 
> Once the formula is created, column d should look like this:
> 
> 1000
> 2000
> 3000
> 4000
> 5000
> 6000
> 7000
> 8000
> 9000
> 
> Help please!!!
> 
> Thank you,
> Cesar Urquidi
0
Utf
11/20/2009 5:30:02 PM
Reply:

Similar Artilces:

Make second combo column trigger event
Hi Groupies I have a combo box that show a job number and then yes or no. If the value of the second column is yes, I want a label to be visible on my form, if it is no the label needs to hide. I have tried many versions of: If me.combobox.[column](2).value="yes" ...... and I can get nothing to work. I have been able to capture the value of the second column in a textbox with a calculation but I can't even make that trigger anything for me. Your help, as always, is greatly appreciated. -- Thanks for taking the time! CJ -------------------------------------------------...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

Columns
keep first columns showing as i scroll down the page? Select cell A2 and from menu click Windows>Freeze PAnes.... -- Jacob (MVP - Excel) "Marg" wrote: > keep first columns showing as i scroll down the page? ...

Summing in a column
I've got Excell 97. Beginner. Can I make Excel sum up data in a column without specifying a specific cell to display the result? So, the result appears in the cell immediately below the last cell in the column containing data, whatever the position in the column the last cell containing data may happen to be. TIA. Rich. With a macro you can do this Exampl;e for column C Sub test() Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _ ..FormulaR1C1 = "=SUM(R1C:R[-2]C)" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Richard" <knot@good.co...

Inconsistent outgoing messages to one domain
Greetings. Since we've installed SP2 on Microsoft Exchange 2003, we are having problems emailing a particular domain. If I use the Message Tracking Tool to monitor the email status we get two results. The first is, the last line has "This message transferred to <server name> through SMTP (1031). The other result is, "This message transferred to through SMTP (The server name is missing). If the message has the server name in it, the message is being sent and recieved, but if the line doesn't have the server name the message may or may not be delivered. Also, if we...

Two domains and two users
(1) I have domain A and domain B (2) I have user A in domain A and user A in domain B (basically same user name but created at different time - different SIDS) (3) I want to remove domain A but copy user A SID info into user A of domain B - basically merge the SID of two accounts into domain B Question: ======= (1) How do I merge two accounts together into one account - merge SIDS of two accounts into one account? I HAVE NO Option such as delete destination account and recreate so PLEASE do not provide me that alternative or explanation - even if it is the easiest. Thank you k...

exporting Outlook Express on one PC...
to Outlook on a PC Tablet How does one go about exporting then importing? Do you need a separate program to do this? You must follow instructions for transferring from one installation of OE to the other first. Only then can you import into Outlook. -- Russ Valentine [MVP-Outlook] "Paul Currie" <pwc@sasktel.net> wrote in message news:126ces4t34q6f30@corp.supernews.com... > to Outlook on a PC Tablet > How does one go about exporting then importing? Do you need a separate > program to do this? ...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Duplicate Importance columns displayed.
I have a client using Outlook 2002 and th edisplay shows two Importance columns. Trying to remove one results in an outlib.dll error. Anyone ever see this before? Thanks ...

can autofilters be applied to rows rather than columns?
Is it possible to filter a database set out in rows instead of columns? Alternatively what would be the best way to isolate matching data without using HLookups? cheers, Nadia I don't think that it will work that way. Are you able to Transpose your database? If so, then you'd be able to use the Auto Filter. Rgds, ScottO "Nadia" <Nadia@discussions.microsoft.com> wrote in message news:FC262080-7D90-4A6D-9582-2AE11DB4A87E@microsoft.com... | Is it possible to filter a database set out in rows instead of columns? | Alternatively what would be the best way to isolate mat...

View one record at a time in a report.
I am new to access. I created a report from a table using the wizard. When I view the report, I only want to see one given record at a time - not all records. How do I do this? Thank you Ron The easiest way is to start from a form, where you bring up the record you want to see. Then put a command button on the form, to open the report to just that one record. Here's the code you need for the command button: Print the record in the form at: http://allenbrowne.com/casu-15.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

check double records in database
Hey, I'm looking for a code to know if a row with primarty key ticker is already in my table DDM. I use the following code but there seem som problems. It says the variable TextTicker.Text is not defined. Can someone help me out? Thx s_Path = ActiveWorkbook.Path s_Path = s_Path & "\Thesis.mdb" Set DB = DAO.OpenDatabase(s_Path) s_SQL = "SELECT Ticker & FROM DDM " _ & "WHERE Ticker = ' & Me.TextTicker & " ';" If s_SQL = TextTicker.Text Then MsgBox "Found" Else MsgBox "Not found" GoT...

how do I set up an annual calendar on one worksheet
I need a tempplate to set up on one worksheet an annual calendar with days of the week, months and date numbers. I havae to set up a calendar that has recurring dates e.g. evry 3rd Weds in Feb, 4th Tues in may etc ANy help would be appreciated on all or some of the above start here: http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=calendar -- Gary''s Student "Ian Dee" wrote: > I need a tempplate to set up on one worksheet an annual calendar with days of > the week, months and date numbers. > I havae to set up a calendar that has recurring ...

Money 2004
For some reason my transactions are being categorized but not tracked in one account. It's an AEX account which I use for most of my purchases. I have no idea what I did, but now, when I enter a transaction with category, etc...my thermometer doesn't register the additional transaction and it doesn't show in my budget! When I enter the same transaction in any other account, it shows. Anyone know how to fix this? It was working fine, but I must've done something. Appreciate any response. Phil Roos Go figure...after searching for hours for a solution...me typing this po...

How to change left bottom note that tell number records found?
Hi, At the left bottom corner of Exel 2003 spreadsheet, a display note 'Ready' remains as it is, though I have filtered the items using the filter command, the note usually displays number of records found. How to return back the display note 'number of records found' in Excel 2003 spreadsheet? -- Thank you, Cpviv If you have any formulas that read these filtered records, you will typically not be able to view the # of records found. If you go to Tool - Options - Calculation and change to Manual, you would be able to view # of records. Beware, as this will not au...

displaying a table on a form so that we can insert a new record at any place rather that at the end
how can i display the table on a form so that the user is able to insert a new record not only in the last, but also in any place of his desire. he must be able to insert a new record even before the first record. how can i accomplish this? can any body help me out please........ hi Ravindar, ravindar thati wrote: > how can i display the table on a form so that the user is able to > insert a new record not only in the last, but also in any place of his > desire. he must be able to insert a new record even before the first > record. Tables in Access (in a database) are a differen...

Calendar Selection
I would like to have a cell validation with a calendar function. You've seen it; someone clicks on cell and instead of getting a dropdown list, you have a calendar you can select from with month and years to change from. Once the user selects the appropriate date, it places that date in the cell. Can this be done?? Hi DNA, See: http://www.rondebruin.nl/calendar.htm --- Regards, Norman "DNA" <dnoel@fsgbank.com> wrote in message news:8c29b822e21276273f63a3391d963f5b@localhost.talkaboutsoftware.com... >I would like to have a cell validation with a calend...

wrong selection gpago
I speak a litle English (very good Italian) George 01/01/2004 (selection from list) 31/12/200 (selection from list) Gregory 23/06/2004 (selection from list) 30/12/2004 (selection from list) Peter 01/04/2004 (selection from list) 20/05/2004 (selection from list) etc I have many workers about 150 persons. One of them works from dat 01/01/2004 to 31/12/2004. No continually in the year. For examble fro 03/01/04 to 15/02/04 and 10/03/04 to 10/04/04 etc. If he works in the first period from 03/01/04 to 15/02/04 he take 50,0 � every da...

select worksheets are corrupting/freezing
Background situation: I am working with a file that contains around 15 PivotTables, all linked to the same cache. The problem: For about a week I have been having trouble with a few of the worksheets - about 3 different tabs, each contains a chart. The file opens fine, no error messages, but when I click on one of the problematic worksheets and then try to change anything on it - close the field list, move the chart, or even select and move to a different worksheet, Excel begins to freeze, and portions of the screen are frozen to the image of the problematic worksheet. I even tried moving...

Multi selection of an array, ending with sum
*Multi selection within an array, ending with sum*: what combination o functions do I need to use to select a subset within an array and the select within this subset a new selection which I can then sum a colum within this last subset. For example If I have an array of 3 columns and 100 rows and I selec criteria based on 1st column, which produces a new smaller array with columns and 60 rows, I then need to select within this smaller array criteria based on 2nd column, which produces an even smaller subse array from which I sum on the 3rd colum -- sammber --------------------------------...

CListCtrl: Changing height of currently selected item?
What I want to do is a list like the one in Windows Control Panel -> Software, where the currently selected list item is shown with more detailed information. My core problem is: How to change the height of the currently selected list item so that more detailed information can be displayed? Any ideas how to realize this easily (i.e. apart from doing the *complete* drawing of the list items by myself using CustomDraw)? Thanks, Harald hi u give one image list to the list control with regards Harish "Harald Karner" <harald_karner@a1.net> wrote in message news:c12cv8$1...

Can one access newsgroups from Outlook?
thanks In a way, yes. It calls for Outlook Express for this action. see: Outlookhelp - Support for Microsoft Office Outlook http://www.howto-outlook.com/Faq/news.htm "d p" <dp25@access-4-free.com> wrote in message news:%23ZttKlS9EHA.2196@TK2MSFTNGP14.phx.gbl... > thanks > > Thanks so much Walt ... a life saver. "Walt Basil" <see_signature@for_address.com> wrote in message news:#WV4VzS9EHA.2676@TK2MSFTNGP12.phx.gbl... > In a way, yes. It calls for Outlook Express for this action. > > see: > Outlookhelp - Support for Microsoft Offic...

lookup from one column return value from another?
I want to check the value of one column (A) and then depending on the outcome add the value in the same row but column B to a total. Can I do this in a single cell formula or do I have to have an extra column that does the logic test and then a cell to do the totalling? Hi Maria not sure of exactly what you want but does =IF(A1="Fred",B1,0) give you what you want? if not could you type out an example of your data and what you want to see (please don't attach a workbook just type it out) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working...

6 items line-column chart on 2 axis
I would like a bar chart to show (in 2 bars) the forecast dollars and actual percentage, where the forecast $$ use the right axis and the actual % use the left axis. The final 4 indicies will be charted using a line chart. When I create this chart my bars stack instead displaying beside each other presumable because each bar uses a different axis. Is there anyway to show the bars in separate columns? Thank you. http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "...