Nth occurrence

[Bugas]

Hi!

My table looks this:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[TD]aa[/TD]
[TD]ab[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Client[/TD]
[TD]Jan-12[/TD]
[TD]Fev-12[/TD]
[TD]Mar-12[/TD]
[TD]Apr-12[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]bbb[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]ccc[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]ddd[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]eee[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]fff[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]ggg[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]hhh[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


For Jan-12 I would get this information:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan-12[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]aaa[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ccc[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bbb[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ddd[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]eee[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]fff[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ggg[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]hhh[/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to get the second, third, ... occurrence. It's not starting on A1. When I use vlookup, I only get the first occurance, ignoring the 2nd or the 3rd. I

I want to use excel formula.

 

[Michael M]

Hi and welcome to the Board.
Why don't you simply sort the table, Ascending, by Jan-12...

 

[Bugas]

Thanks for your support, but sorting is not an option.

Hi and welcome to the Board.
Why don't you simply sort the table, Ascending, by Jan-12...

 

[Robert Mika]

Thanks for your support, but sorting is not an option.

[TABLE="width: 825"]
<TBODY>[TR]
[TD]100</SPAN>[/TD]
[TD]Client</SPAN>[/TD]
[TD]Jan-12</SPAN>[/TD]
[TD]01-Feb</SPAN>[/TD]
[TD]Mar-12</SPAN>[/TD]
[TD]Apr-12</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Rank</SPAN>[/TD]
[TD]Return</SPAN>[/TD]
[TD="align: right"]Mar-12</SPAN>[/TD]
[/TR]
[TR]
[TD]101</SPAN>[/TD]
[TD]aaa</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]bbb</SPAN>[/TD]
[/TR]
[TR]
[TD]102</SPAN>[/TD]
[TD]bbb</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[/TR]
[TR]
[TD]103</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]aaa</SPAN>[/TD]
[/TR]
[TR]
[TD]104</SPAN>[/TD]
[TD]ddd</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]ddd</SPAN>[/TD]
[/TR]
[TR]
[TD]105</SPAN>[/TD]
[TD]eee</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]eee</SPAN>[/TD]
[/TR]
[TR]
[TD]106</SPAN>[/TD]
[TD]fff</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]6</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]6</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]fff</SPAN>[/TD]
[/TR]
[TR]
[TD]107</SPAN>[/TD]
[TD]ggg</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]6</SPAN>[/TD]
[TD]7</SPAN>[/TD]
[TD]6</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7</SPAN>[/TD]
[TD]7</SPAN>[/TD]
[TD]ggg</SPAN>[/TD]
[/TR]
[TR]
[TD]108</SPAN>[/TD]
[TD]hhh</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]8</SPAN>[/TD]
[TD]8</SPAN>[/TD]
[TD]8</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]8</SPAN>[/TD]
[TD]8</SPAN>[/TD]
[TD]hhh</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL span=2><COL><COL span=2></COLGROUP>[/TABLE]



With an helper column (I)
I2 copy down
=RANK(HLOOKUP($K$1,$C$1:$F$9,ROWS($A$1:A2),0),INDEX($C$2:$F$9,,MATCH(K$1,$C$1:$F$1,0)),-1)+COUNTIF(INDEX($C$2:F2,,MATCH(K$1,$C$1:$F$1,0)),HLOOKUP($K$1,$C$1:$F$9,ROWS($A$1:A2),0))-1
J2 copy down
=INDEX($C$2:$F$9,MATCH(ROW()-1,$I$2:$I$9,0),MATCH(K$1,$C$1:$F$1,0))
K2 copy down:
=INDEX($B$2:$B$9,MATCH(ROW()-1,$I$2:$I$9,0))
you can change the date in K1 to one from the first row

 

[Canapone]

Hi All,

if in column B (January 12) there are numbers, in order to get an ascending list in (example) G2 you could try:

=SMALL($B$2:$B$100,ROWS($A$1:A1))

Then in H2 in order to get first, second... occurence (A2:A100=clients),

=INDEX($A$2:$A$100,SMALL(IF($B$2:$B$100=G2,ROW($A$2:$A$100)-1),COUNTIF(G$2:G2,G2)))

This formula must be confirmed with control+shift+enter

Hope that helps

 

[Bugas]

Thank you all for your help. It works fine when I put them starting on A1. But my data doesn't start on A1. Client aaa for Jan-2012 is on Y101. If i move that cells it gives me error.

I put my data on Column Y, Z, AA, AB and rows 100, 101, 102, 130, 104, 105, 106, 107, 108.

 

[Robert Mika]

Thank you all for your help. It works fine when I put them starting on A1. But my data doesn't start on A1. Client aaa for Jan-2012 is on Y101. If i move that cells it gives me error.

I put my data on Column Y, Z, AA, AB and rows 100, 101, 102, 130, 104, 105, 106, 107, 108.

Sheet2 column A start in A2:
=SMALL(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0)),ROWS(Sheet1!$A$1:A1))
in B2 confirm COntrol+Shift+enter
=INDEX(Sheet1!$Y$101:$Y$108,SMALL(IF(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0))=$A2,ROW($A$2:$A$9)-ROW($A$2)+1),COUNTIF($A$2:A2,A2)))

B1 is your Date
01/01/12

 

[Bugas]

Thank you for your support, but I can't make it with that formula. It gives me error #N/A​

Sheet2 column A start in A2:
=SMALL(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0)),ROWS(Sheet1!$A$1:A1))
in B2 confirm COntrol+Shift+enter
=INDEX(Sheet1!$Y$101:$Y$108,SMALL(IF(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0))=$A2,ROW($A$2:$A$9)-ROW($A$2)+1),COUNTIF($A$2:A2,A2)))

B1 is your Date
01/01/12

 

[Canapone]

Hi EVERYONE,

can you upload a small example in order to help you?

Probably you did not correctly confirm the array formulae from Robert.

Cheers

Here a small file where I've tried the array formulae:

http://dl.dropbox.com/u/106022761/MREXCEL.xls

 

[Robert Mika]

Thank you for your support, but I can't make it with that formula. It gives me error #N/A​

Excel 2010

	X	Y	Z	AA	AB	AC	AD	AE
Client
aaa
bbb
ccc
ddd
eee
fff
ggg
hhh

<tbody>
[TD="align: center"]93[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]94[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]97[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jan[/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"]Mar-12[/TD]
[TD="align: right"]Apr-12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]101[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]102[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]103[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]104[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]105[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]106[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]107[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]108[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]109[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]110[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]111[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]112[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]113[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]115[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Excel 2010

	A	B	C
aaa
ccc
bbb
ddd
eee
fff
ggg
hhh

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Return​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=SMALL(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0)),ROWS(A$2:A2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=INDEX(Sheet1!$Y$101:$Y$108,SMALL(IF(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0))=$A2,ROW($A$2:$A$9)-ROW($A$2)+1),COUNTIF($A$2:A2,A2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

[/TD]
[/TR]
</tbody>[/TABLE]



Are the values different foreach column or they can be diffrent lenght?
If yes we need some adjustemnt to the formulas.

Probably you did not correctly confirm the array formulae from Robert.

Thanks Canapone.