match to find the 2nd occurence

[rohith554]

Hello All,

I need a formula that can fetch the 2nd occurrence in the row.

Below is the example data:

Name Dept

xyz 10
xhu 12
xyz 15

Now i need a formula to fetch the 2nd match(here for xyz - Answer should be 15)

xyz 15

Thanks in advance!

 

[mrmmickle1]

Hey Welcome to the MessageBoard!
Put the below code in a Regular Code Module. Then you can use the function from the spreadsheet....

From the OzGrid Website:

[COLOR=#0000ff]Function[/COLOR] Nth_Occurrence(range_look[COLOR=#0000ff] As [/COLOR]Range, find_it [COLOR=#0000ff]As String[/COLOR], occurrence[COLOR=#0000ff] As Long[/COLOR], offset_row [COLOR=#0000ff]As Long[/COLOR], offset_col [COLOR=#0000ff]As Long[/COLOR])

[COLOR=#0000ff]Dim [/COLOR]lCount [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]rFound [COLOR=#0000ff]As Range[/COLOR]

[COLOR=#0000ff]    Set[/COLOR] rFound = range_look.Cells(1, 1)

      [COLOR=#0000ff]  For [/COLOR]lCount = 1 [COLOR=#0000ff]To[/COLOR] occurrence

            [COLOR=#0000ff]Set[/COLOR] rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]        Next[/COLOR] lCount

    Nth_Occurrence = rFound.Offset(offset_row, offset_col)

[COLOR=#0000ff]End Function[/COLOR]

 

[rohith554]

Hey Welcome to the MessageBoard!
Put the below code in a Regular Code Module. Then you can use the function from the spreadsheet....

From the OzGrid Website:

[COLOR=#0000ff]Function[/COLOR] Nth_Occurrence(range_look[COLOR=#0000ff] As [/COLOR]Range, find_it [COLOR=#0000ff]As String[/COLOR], occurrence[COLOR=#0000ff] As Long[/COLOR], offset_row [COLOR=#0000ff]As Long[/COLOR], offset_col [COLOR=#0000ff]As Long[/COLOR])

[COLOR=#0000ff]Dim [/COLOR]lCount [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]rFound [COLOR=#0000ff]As Range[/COLOR]

[COLOR=#0000ff]    Set[/COLOR] rFound = range_look.Cells(1, 1)

      [COLOR=#0000ff]  For [/COLOR]lCount = 1 [COLOR=#0000ff]To[/COLOR] occurrence

            [COLOR=#0000ff]Set[/COLOR] rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]        Next[/COLOR] lCount

    Nth_Occurrence = rFound.Offset(offset_row, offset_col)

[COLOR=#0000ff]End Function[/COLOR]

Thank you for the reply mrmmickle1.

I am looking for a excel formulae to get the result.

Thanks,
Rohit

 

[mrmmickle1]

This is a formula solution:

See Below:

Excel 2012
	A	B	C	D	E
1	Values	Values2		xyz
2	xyz	10		1st Occ	10
3	xhu	1st		2nd Occ	15
4	xyz	15		3rd Occ	20
5	xyz	20		4th Occ	25
6	xyz	25
7	xhu	2nd		xhu
8				1st Occ	1st
9				2nd Occ	2nd
Sheet1

Cell Formulas
Range	Formula
E2	=Nth_Occurrence($A$1:$A$7, "xyz", 1, 0, 1)
E3	=Nth_Occurrence($A$1:$A$7, "xyz", 2, 0, 1)
E4	=Nth_Occurrence($A$1:$A$7, "xyz", 3, 0, 1)
E5	=Nth_Occurrence($A$1:$A$7, "xyz", 4, 0, 1)
E8	=Nth_Occurrence($A$1:$A$7, "xhu", 1, 0, 1)
E9	=Nth_Occurrence($A$1:$A$7, "xhu", 2, 0, 1)

Note: Make Sure to use the Header in your Range or the calculations will be off.

Arguments: NthOccurence(RangeToLookIn, ValueToLookFor, Occurrence, RowOffset,ColumnOffset)

 

[AhoyNC]

Another option.
An array formula that must be entered with

Excel Workbook
	A	B	C	D	E	F
1	Name	Dept
2	xyz	10		Find
3	xhu	12		Name	Dept
4	xyz	15		xyz	15
5	xyz	34
6	ftd	56
7	xhu	32
8
CTRL-SHIFT-ENTER.

 

[Aladin Akyurek]

Hello All,

I need a formula that can fetch the 2nd occurrence in the row.

Below is the example data:

Name Dept

xyz 10
xhu 12
xyz 15

Now i need a formula to fetch the 2nd match(here for xyz - Answer should be 15)

xyz 15

Thanks in advance!

Whichever actually suits...

1)

[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Dept[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]xyz[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]xyz[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]xhu[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]xyz[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

D2, control+shift+enter, not just enter, anc copy down:

=IFERROR(INDEX($B$2:$B$4,SMALL(IF($A$2:$A$4=D$1,
  ROW($A$2:$A$4)-ROW($A$2)+1),ROWS(D$2:D2))),"")

2)


[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Dept[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]xyz[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]xyz[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]xhu[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]Result[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]xyz[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[/TR]
</TBODY>[/TABLE]

D1: xyz
D2: 2 (meaning second occurrence)

D4, control+shift+enter, not just enter:

=IF(COUNTIF($A$2:$A$4,D$1)>=2,INDEX($B$2:$B$4,
  SMALL(IF($A$2:$A$4=D$1,ROW($A$2:$A$4)-ROW($A$2)+1),D$2)),"")

 

[rohith554]

Thank you for the reply.

This works

 

[Peter_SSs]

Others have offered flexible suggestions to find the 2nd, 3rd, 4th etc occurrence but if your requirement is only to look up the 2nd occurrence, then you could use either of these non array-entered formulas.
The D2 formula should work in all Excel versions.
The D3 formula required Excel 2010 or later.

The D3 formula can also be used in a flexible way to find the nth occurrence as shown to the right of my screen shot.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Dept		xyz				Name	Dept		xyz
2	xyz	10		15				xyz	10		3
3	xhu	12		15				xhu	12		21
4	xyz	15						xyz	15
5								abc	18
6								xyz	21
7								def	24
8								xyz	27
9
Lookup Second

 

[rohith554]

Peterson - Amazing

 

[mrmmickle1]

These methods are all great. Thanks for contributing! I didn't know this was possible without a UDF