get 2nd occurence value

Match function fetches row number of 1st occurence of a value in a range,How 
to get 2nd or 3rd or later occurences of that value in a range?
0
9/29/2005 6:28:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
223 Views

Similar Articles

[PageSpeed] 31

Assuming that A1:A100 contains your data, and that B1 contains your 
lookup value, try the following for the second occurrence...

=SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.  For the third 
occurrence, change the 2 at the end of the formula to 3.

Hope this helps!

In article <6FC05ACF-01DA-43C1-B3AF-252211D1BF47@microsoft.com>,
 TUNGANA KURMA RAJU <TUNGANAKURMARAJU@discussions.microsoft.com> wrote:

> Match function fetches row number of 1st occurence of a value in a range,How 
> to get 2nd or 3rd or later occurences of that value in a range?
0
domenic22 (716)
9/29/2005 7:07:24 PM
Hello DOMENIC,
Thanks.you have done it.It helped me to solve a problem.With regards

"Domenic" wrote:

> Assuming that A1:A100 contains your data, and that B1 contains your 
> lookup value, try the following for the second occurrence...
> 
> =SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2)
> 
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.  For the third 
> occurrence, change the 2 at the end of the formula to 3.
> 
> Hope this helps!
> 
> In article <6FC05ACF-01DA-43C1-B3AF-252211D1BF47@microsoft.com>,
>  TUNGANA KURMA RAJU <TUNGANAKURMARAJU@discussions.microsoft.com> wrote:
> 
> > Match function fetches row number of 1st occurence of a value in a range,How 
> > to get 2nd or 3rd or later occurences of that value in a range?
> 
0
10/1/2005 7:00:01 AM
Reply:

Similar Artilces:

How can I paste a unique values list?
I have a very big list contains a lot of repeated values and I want to get a copy of the same list BUT only with unique values. For Example the column Reg.No. includes the following Numbers: 1154 1168 1166 1154 908455 907558 1166 908455 1154 1166 List might have thousans of records. I want to get a copy of this list without repeating any of the numbers included. { Uniqe value list }. Thanks and regards. JAK You can add a header, select the range, do data>filter>advanced filter, select copy to another location and unique records only -- Regards, Peo Sjoblom (No private emails ...

When inserting a simple "Sum" formula all I get is the formula.
I need to see the calculation and not the formula. I've used this process for years and have never had problems. goto tools=options=view and make sure formulas are not ticked off -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708 View this thread: http://www.excelforum.com/showthread.php?threadid=517335 1) Make sure the cell is not formatted Text; if so, format it General then open the cell (F2 or double click) then Enter. 2) Make sure you do not have ...

How do I get an outlook e-mail address?
I was not given an e-mail address at the time of installation or when internet was first installed on my computer. Then you'll need to contact your ISP to get one. "Miss J" <Miss J@discussions.microsoft.com> wrote in message news:46C528BC-2DB3-453F-99AA-89037EC312AB@microsoft.com... :I was not given an e-mail address at the time of installation or when : internet was first installed on my computer. Miss J wrote: > I was not given an e-mail address at the time of installation or when > internet was first installed on my computer. You need to get one as Outloo...

Trying to get CDO to Work
Trying to use CDO to send mail from a classic ASP page running on Win Small Business Server 2008 (with IIS 7.0 and Exchange 2007 on the same box). I get the following error 006~ASP 0177~Server.CreateObject Failed~8002801d from the following code set oMail = server.createobject("CDO.Message") if err <> 0 then response.write err.number & " - " & err.description response.end end if The script is running under the IUSR_Machine account (determined by printing request.servervariables("LOGON_USER") and gtting back a blank). I ca...

Getting shaded details
I have looked at the examples for shading everyother row of a detail but I would like every third detail to be shaded and have it restart each time a new group header is displayed. My report: Group Header 1 - grouped Detail - non grouped Footer - group footer, keep together with first detail. I would also like to have an index on the left showing a number for each detail that would start over for each group also. Is there a way to do this? Thanks in advance Assuming Access 2000 or later, you can do this without code, using a Running Sum text box and conditional formatting. 1. Ope...

Creating A Custom Occurance for Tasks
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I'm trying to create a task and have it occur every other week (which is easily done in the calendar). However, when I go to the &quot;Occurs&quot; drop down that is not an option so I choose &quot;Custom&quot;. Once I do this my entire Entourage freezes and I can't do anything within Entourage. My Force Quit doesn't show Entourage as &quot;Not Responding&quot; but the Entourage toolbars grey out and I can't do anything in which case I have to Force Quite Ent...

CPrintDialog default values
I am trying to set the default values of the CPrintDialog in my 'InPreparePrinting' method of CMyView. The problem that I have is that the 'To Page' always defaults to 'MaxPages' no matter what I do. I have tries setting the 'm_pd.nToPage' to a value but it does not set the default. I would like to default the 'To Page' to the value 'MaxPages - 1'. Can the 'To Page' be changes without changing 'MaxPages'????? -- Hank Williams Quantum Technologies, Inc. HaWilliams(at)spamcop.net When I add '1' to 'nToPage'...

Lookup value in list and return value
This is probably something super simple. But for some reason, i cant figure it out. I have a list of dates in ascending order. (C2:C1000) 6/20 6/21 6/22 6/23 6/24 6/25 6/26 6/27 etc... In A:2, i would i want to enter a specific date that is provided in the date list above. 6/20 and return the value 10rows down. Any suggestions? =INDEX(C2:C1000,MATCH(A2,C2:C1000,1)+10) -- Kind regards, Niek Otten Microsoft MVP - Excel "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message news:1183486779.522088.129820@c77g2000hse.googlegroups.com... | This is probably somethin...

how to get a name just by typing a number?
I am doing my schedule using number for each of my employees. The problem is that I know who corresponds to each number, but that not the case of my employees or patients... so I have to retype a special schedule for them... is it possible to affect a name to a number so that when I type 1 in a case I will get Mark for example? thanks for your help You'd need to create a table that lists all numbers and corresponding names, and then use a VLOOKUP formula to pull in the name. See these sites for examples: http://www.contextures.com/xlFunctions02.html http://www.techonthenet.com/exce...

Using names to represent values
I am trying to get names to represent values, e.g. Bill=3 bob=4 Stan=22 so that after I type a bunch of names into the cells, the view is stil | Bill | Bob | Stan | but that I can total them so it would look like this | Bill | Bob | Stan | 29 | is this possible and how? I have been moneying with insert define nam for the past hour and I am going crazy I want the names to show in the cells so that readility remains, th numbers associated with particular names are not important and ar defined elsewhere but I have a ton to manipulate mathematically a...

Time sheet Wrong posting values
Dears, I am facing a probelm while posting Time sheet from (Time Card Entry) and they are using (Enter duration hours/minutes) after posting it shows the date of entry (from date) is 00/00/0000 and the (to date) is 00/00/1900. and it shows a negative value in the (Cost put into the WIP) from Manufacturing order variance inqury. please advice, ...

im not getting incoming mail
i have exchange 2003 this setup was working find up until the 9th. I have not found any changes made any where on the network. My users are able to send mail out of my network but nothing is comming in. and all outside senders are getting bouce backs. Where should i begin to troubleshoot this. i have exchage2003 - fw (flow point) - dsl -internet and again all mail is going out to the internet and also user are able to send to each other within the network. please help what is the email domain? have you checked to see that all applicable MX and A records are in place and correc...

I cannot get my blackberry Tour to sync to Microsoft outlook 2010
I cannot get the Blackberry software to configured to see Microsoft Outlook 2010 beta. It says there are no files for outlook. I need to sync my calendar and contacts. You might need updated software that recognizes Beta software. css2004 wrote: > I cannot get the Blackberry software to configured to see Microsoft Outlook > 2010 beta. It says there are no files for outlook. I need to sync my > calendar and contacts. Are you using 32 or 64 bit outlook? It will not work with the 64bit version but might with the 32 bit. -- Diane Poremsky [MVP - Outlook] Ou...

VBA Sub to delete rows based on a Column Value
I have a workshhet named Final Short Quote. Anywhere in Column C wher the cell conatins the word Remove, I want to delete that row. How can do that -- Message posted from http://www.ExcelForum.com Hi try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If instr(Cells(row_index, 3).Value,"Remove")>0 then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub -- Regards Fra...

Where do I get the CD?
I installed the 60-day trial of MS Money 2004 Deluxe, then bought the full version. I think I asked for the CD at that time, but not sure. I have a laptop I want to move the program to, but can't move the program from current pc w/o error. I downloaded the trial to the new pc but .mny file won't work with it. How do I transfer what works to the new PC without the CD? At the store like the rest of us. Seriously, please search newsgroup microsoft.public.money at http://groups.google.com for lots of other discussions about this issue. You will find out how to reenter the activ...

Create 2nd toolbar
Hi there, I was wondering how do I create a 2nd toolbar right underneath the first one? If I use the resource editor and created a single toolbar IDR_MAINFRAME and I load it with: if (!m_wndToolBar.CreateEx(this, TBSTYLE_FLAT, WS_CHILD | WS_VISIBLE | CBRS_TOP | CBRS_SIZE_FIXED | CBRS_TOOLTIPS) || !m_wndToolBar.LoadToolBar(IDR_MAINFRAME)) { TRACE0("Failed to create toolbar\n"); return -1; // fail to create } If I need a 2nd toolbar (IDR_SECONDTOOL), how do I load that if I use the resource editor to create the toolbar? Thanks, Robert Did you try? ...

Subject: retrieving the XmlEnumAttribute values for an Enum
I have an enum defined as public enum velocityUom { /// <remarks/> [System.Xml.Serialization.XmlEnumAttribute("m/s")] ms, /// <remarks/> [System.Xml.Serialization.XmlEnumAttribute("cm/a")] cma, ... } This class was generated by xsd.exe, from a schema like <xsd:simpleType name="velocityUom"> <xsd:enumeration value="m/s"/> <xsd:enumeration value="cm/a"/> .... </xsd:restriction> </xsd:simpleType> I would like to retrieve the XmlEnu...

how to get the number of milliseconds between two System.DateTime objects
how to get the number of milliseconds between two System.DateTime objects DateTime dt = DateTime.Now; TimeSpan ts = DateTime.Now.Subtract( dt ); Convert.ToString(ts.TotalMilliseconds) ; ...

Top Values in a column
Hi there, I have a column of values (E3:E50) and I want to list below this column, in a cell (lets say E52) the SUM of the top 5 highest values. The glitch (as there always is one) is that I don't want every cell to be considered - I only want, for example, E5, E8, E11, E14, E17, E20, E23, E26, E29, E32, E35, E38, E41, E44, E47 & E50 to be included. Is there a way to do this? Whilst I mention it - would there be a way, with conditional formatting perhaps, that any cell in that selection (E5, E8, E11, etc.) that IS a top 5 value - could it become a different colour? Any help would ...

Why I get "Internet Explorer Script Error" if I click Personal Fol
I have installed Microsoft Office Professional 2007 recently. I used the Outlook for managing my emails and daily task. For a while, it was perfectly normal. However for some reasons, which I am not sure of, it gave some problems of late. When I click the "Personal Folder", it pop up a window "Internet Explorer Script Error". Can anyone help? Regards YM-Singapore <YM-Singapore@discussions.microsoft.com> wrote: > I have installed Microsoft Office Professional 2007 recently. I used > the Outlook for managing my emails and daily task. For a while, it >...

How To get Attributes of an XML Element Using XPATH
Does anyone have any code to get Attributes of an XML Element Using XPATH. I have been trying all day and cannot get this to work. I have been using XPathNavigator, XPathDocument and the XPathNodeIterator trying to get the url attribute of the following XML: <?xml version='1.0' encoding='utf-8' ?> <Message> <Internet> <site url='http://www.yahoo.com' /> </Internet> </Message> Code Below: XmlTextReader reader = new XmlTextReader(new StringReader(xml)); docNav = new XPathDocument(reader); ...

Can't get into Hotmaill
I have XP SP2, Outlook Express 6, (But I do not use outlook expressI to access hotmail-right from the web page) have not been able to access hotmail the last two days- yesterday my id and password were wrong, and today, I only get a blank page when i click on hotmail) If this is wrong place for question, please direct me to right place, I already emailed MSN from the MSN home page tech support, but since I can't access their hot mail, I do not know - it is one thing I have never understood is why I can get e-mail on web pages and /or on Outlook Express. I tried to put my hot ma...

M06 Question
How can I show the value of an account as of a particular date? I'm in Account List | {account name} | Show Transactions From All Dates I see the amount of each transaction, and the balance of the account for today. I'd like to see what the value was on a particular date. Having a running total visible in the register would get me this, but the ability to change column sizes or to choose columns isn't available. Any ideas? In microsoft.public.money, Erik Sojka wrote: >How can I show the value of an account as of a particular date? > >I'm in Accoun...

Combine the Table and Insert a value
I tried the below query using 23 tables, i got a error "Duplicate output alias File_Date, could you please advise me. SELECT Mon.[Customer Name (DFF)], Mon.Amount, "weekMon" AS File_Date FROM Mon Union All SELECT Tue.[Customer Name (DFF)], Tue.Amount, "weekTue" AS File_Date FROM Tue; Your field names are derived from your first statement in a union query so you do not need to specify it in the statements after. SELECT Mon.[Customer Name (DFF)], Mon.Amount, "weekMon" AS File_Date FROM Mon; Union All SELECT Tue.[Customer Name (DFF)], Tue.Amoun...

Save values and formatting but not links
I would like to save a file with multiple sheets while retaining th current values and format, but not the links so I can minimize fil storage space and load time. I wrote an add in that creates a new sheet. Is is possible to modif this to save in a file format without links, or is there another way can approach this? Thanks in advance for any input. Sub SelectNewFile() Dim MyFileName As String, Fdate As String, NewName As String, awb A Workbook, BackupFileName As String, i As Integer, OK As Boolean If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub Set awb = ActiveWorkbook I...