Extracting names from a database.


In column A I have  twenty groups, in Column B ref#, In Column C First
Name, In Column D Surname and In Column E the numerical value. 
The data  rows are from 2 to 534. 
I can find the total and maximum for each group using arrays. 
1. What I like is the name of who has the highest value in each group.
2. If they are tied then all the names that are tied maximum. 
3. If possible all the names in the group with a value over 2.

I hope that this makes sense.
0
Campbell
9/7/2004 9:15:29 PM
excel 39879 articles. 2 followers. Follow

2 Replies
638 Views

Similar Articles

[PageSpeed] 40

Hi
try:
1. =INDEX(D2:D534,MATCH(MAX(E2:E534),E2:E534,0))

2. Try the following array formula:
=INDEX($D$2:$D$534,SMALL(IF($E$2:$E$534=MAX(E2:E534),ROW($E$2:$E$534)),
ROW(1:1)))
and copy this down

3. Use a Filter for this


--
Regards
Frank Kabel
Frankfurt, Germany


Campbell wrote:
> In column A I have  twenty groups, in Column B ref#, In Column C
First
> Name, In Column D Surname and In Column E the numerical value.
> The data  rows are from 2 to 534.
> I can find the total and maximum for each group using arrays.
> 1. What I like is the name of who has the highest value in each
group.
> 2. If they are tied then all the names that are tied maximum.
> 3. If possible all the names in the group with a value over 2.
>
> I hope that this makes sense.

0
frank.kabel (11126)
9/7/2004 9:37:31 PM
On Tue, 7 Sep 2004 23:37:31 +0200, "Frank Kabel"
<frank.kabel@freenet.de> wrote:

>Hi
>try:
>1. =INDEX(D2:D534,MATCH(MAX(E2:E534),E2:E534,0))
>
>2. Try the following array formula:
>=INDEX($D$2:$D$534,SMALL(IF($E$2:$E$534=MAX(E2:E534),ROW($E$2:$E$534)),
>ROW(1:1)))
>and copy this down
>
>3. Use a Filter for this

Frank thanks for your response, what this gives is name top for the
whole range. However I have 20 different names in column A which are
sorted alphabetically. 
e.g one of the groups called NJK in column A which person has the
highest value. I have a helper column G with all 20 names in G2:G21

0
Campbell
9/7/2004 10:07:32 PM
Reply:

Similar Artilces:

how to get remote host name
Hello! I have a problem with getting the hostname of the other part of the connection. I had used gethostbyaddr() to retreive the hostname of the other side. (When I am the server.) If the server and the client in the same subnetwork everything work just fine. But as the client come from an other subnetwork this function fails and get back a HOSTENT* structure with NULL value and with and error: this host exists but no associated name. My question is about how to obtain in this case the hostname of the client? There is a general way? Some function? Thanks in advance. Best Regards, Nagyja...

Outlook 2003 Email Select Names Type name or select from list
New Message: When I try to use the Select Names - Type name or select from list in an Email New Message, the search is not as expected. For example, I enter the Name Smith, and the selection will be another name starting with S, but not Smith. This Address was from Outlook Express and worked fine there. Thank you. Please describe more completely what you are doing. Perhaps you are using autocompletion instead of autoresolution. You didn't provide enough details for us to tell. -- Russ Valentine [MVP-Outlook] "nsnews.microsoft.com" <nfn02958@naples.net> wrote in message...

Err.Description as error proc name trail, error handling
Hi All, I'm looking for guidance on how to retrofit into a large AddIn the 'best' or possible uses of the Err object in providing an information path to help track down run time errors in a 'production' environment. Some Background, 4 questions follow. I was new to VBA, self taught, when the coding started, (but with experience in other languages), and the vba error handling seemed too complex at the time, huge mistake on my part. The procs below are learning vehicles, which I'm just starting to play with, but they do show the essence o...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Database / Items
I open Store Operations Manager. I open Database / Items. How do I customise the columns in Items dialog box. I want to add "Cost" column next to "Price" column for quick review. I am using ver 1.3 Thanks Don You can't modify the built in forms. You could build a custom Add-in that displays what you want. Digital Retail Solutions has an existing Add-in that does allow you to modify the displayed columns - search the newsgroup, they post occasionally. As a work around, you use an item report (Item Quantity List maybe). Use Add/Remove columns to display the ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Getting the bitmap OLE Object from Microsoft Access database table
Hi, I have bitmaps attached to Microsoft Access table. I need to read the Access table and get the bitmap. Im able to get to the record and get the field value using GetFieldValue() which returns as a COleVariant. How can I get the bitmap and palette(CPalette) information. Im using MFC and C++ Will appreciate any help/suggestion with the above. Thanks MA Jeff, Please provide the attachement. Thanks MA "Jeff Partch [MVP]" wrote: > > Hi, > > > > I have bitmaps attached to Microsoft Access table. I need to read the > Access > > table and get the bi...

Problems with 'Names'
I am using MS Excel 2007. I have a series of linked files - 1 main 'parent' file with 5 'children' files. The parent file contains 'names' which are used in all the children files (eg..titles across columns, drop down boxes/lists (referencing name ranges) etc) I need to find and replace some of the external links in the files with new file extensions - XLSM and XLSX. I can find and replace as required in all the formulas which work, however the 'names' in the file still refer to the .XLSM file. If you search the entire workbook .XLSM cann...

Error binding display name
Hello, I have just followed KB article 317327 on my SBS 2003 machine so as to add an email disclaimer. I got the below error when trying to register the sink: - C:\PROGRA~1\Exchsrvr>cd scripts C:\PROGRA~1\Exchsrvr\scripts>cscript smtpreg.vbs /add 1 OnArrival SMTPDisclaimer SMTPEventSink.Disclaimer "mail from=*@mydomain.com" Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. Binding Display Name Specified: SMTPDisclaimer ** Registration Failed ** Err.Number (HRESULT) = 0x1AD Err.Description = Act...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Tracking Changes in a database
I am writing a new program for work that will require the changes to records be versioned and changes tracked. I have a audit table and the triggers to track the changes already, what I don't have is an idea on how to version the records. If a person enters a record and attaches a PDF (to be stored in the database), I need to create version 1 of this record (this I can do). However, if someone makes a change to this record (not an edit only a change), I need to increment the version and keep the previous versions (in case they want to roll back). This is similar to what s...

"From" Name Missing When Sent Mail Saved to PST
I usually save my sent mail to a folder in my PST using one of the Slipstick utilities that prompts for a folder name upon sending. After sending and storing to a folder in my PST, when I view the folder in the 'explorer' mode, the "from" name is missing from the view. When I double click on the message, the 'from' name shows up correctly. I'm stumped as to what's happening here. I tried the same process but only stored the sent mail to a folder I created on my corporate exchange server mailbox ("Temp"), and the name DOES show up in the explorer m...

create defined name using variable file name in reference
please help in create defined name EXAMPLe: create name : ACCPath ---- Referes to (='E:\Data Folder\[externa work book.xlsx]sheet1'!$K$8:$K$10000) this works I NEED to put (external work book.xlsx) in a cell and refere to it as variable in the referes to (='E:\Data Folder\[&U2&]sheet1'!$K$8:$K$10000) where U2 having the file name because it is changing meny times Activeworkbook.Names.Add Name:="ACCPath", RefersTo:= "=" & Activesheet.Range("U2").Value2 --- HTH Bob Phillips "hala" <hala@discussions.mi...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

Auto Fill display name
When I type a name in the to: field it will not recognize John Doe but it will recognize doe, john before I upgraded from 2000 to 2003 it would recognize John Doe. I have been unable to find any setting that would fix this. What you claim is not possible. Outlook 2000 did not even have an autocompletion feature when typing in the To: field. Only Outlook 2002 and 2003 do, and they use a cache of names created from messages you have sent, not your Contacts. -- Russ Valentine [MVP-Outlook] "HTS" <anonymous@discussions.microsoft.com> wrote in message news:572f01c48a35$501...

Must Enter domain name on OWA 2003 Front End?
I noticed two things happen if you do *not* use your domain name when you log onto the OWA 2003 Front End server: 1. When you use the Logout button you get a prompt for your username and password again 2. When you try to open a link within an email message you are prompted for your username and password, and then you must use your domain\username or else the link will never open. Q. Is this because our Internet Mail domain name and our AD domain name are different? I thought the instructions from Microsoft said you could set a default domain, but the options were only for my email domain...

public database wont remount after restore
I have restored our server at work, all went well but the exchange server. After I restore the data to the public database file, it will not remount. I have not yet done the private folder as I don't want to get both stuck unmounted. Any Ideas?? Hi, "Slim" <i@i.com> schrieb im Newsbeitrag news:O9yPRNI$EHA.1400@TK2MSFTNGP11.phx.gbl... >I have restored our server at work, all went well but the exchange server. > After I restore the data to the public database file, it will not remount. > > I have not yet done the private folder as I don't want to get bo...

Contacts Sorted by Last Name
Hi, I recently imported my contacts from an older version of outlook into Outlook 2003. When I create a new message and click the "To" button to select my contacts. When I type in the name, I used to type in the letters of the last name and the name would come up. For example, John Smith. I would type SM and John Smith would come up. Now this isn't possible. I have to type in "John Sm". Any ideas how to change this. I would expect that. You have a new autocompletion/autoresolution cache now, so your old entries aren't there. After your repopulate the ca...

Extracting data to make mailing list
My neighbour has a .PUBS name and address doc. The doc is 10 pages long. Each page of the document is a table with 10 rows and 3 columns. Each cell is name on top the line followed by 2 or 3 lines of address. She then just cuts the full sheet Avery into mailing labels. This has become too difficult to manage, sort, filter, etc. Anyone have a suggestion how to extract this data so I can make a Publisher mailing list? Thanks. -- Ross try bringing it into Excel...you'll some "massaging" to do. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Ross"...