Nth occurrence

[Bugas]

Hi!

My table looks this:

	x	y	z	aa	ab
100	Client	Jan-12	Fev-12	Mar-12	Apr-12
101	aaa	1	1	3	1
102	bbb	3	2	1	2
103	ccc	1	2	1	2
104	ddd	4	4	3	4
105	eee	5	4	5	4
106	fff	5	6	5	6
107	ggg	5	6	7	6
108	hhh	5	8	8	8

<tbody>
</tbody>

For Jan-12 I would get this information:

	Jan-12
1	aaa
1	ccc
3	bbb
4	ddd
5	eee
5	fff
5	ggg
5	hhh

<tbody>
</tbody>

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.

100</SPAN>	Client</SPAN>	Jan-12</SPAN>	01-Feb</SPAN>	Mar-12</SPAN>	Apr-12</SPAN>			Rank</SPAN>	Return</SPAN>	Mar-12</SPAN>
101</SPAN>	aaa</SPAN>	1</SPAN>	1</SPAN>	3</SPAN>	1</SPAN>			3</SPAN>	1</SPAN>	bbb</SPAN>
102</SPAN>	bbb</SPAN>	3</SPAN>	2</SPAN>	1</SPAN>	2</SPAN>			1</SPAN>	1</SPAN>	ccc</SPAN>
103</SPAN>	ccc</SPAN>	1</SPAN>	2</SPAN>	1</SPAN>	2</SPAN>			2</SPAN>	3</SPAN>	aaa</SPAN>
104</SPAN>	ddd</SPAN>	4</SPAN>	4</SPAN>	3</SPAN>	4</SPAN>			4</SPAN>	3</SPAN>	ddd</SPAN>
105</SPAN>	eee</SPAN>	5</SPAN>	4</SPAN>	5</SPAN>	4</SPAN>			5</SPAN>	5</SPAN>	eee</SPAN>
106</SPAN>	fff</SPAN>	5</SPAN>	6</SPAN>	5</SPAN>	6</SPAN>			6</SPAN>	5</SPAN>	fff</SPAN>
107</SPAN>	ggg</SPAN>	5</SPAN>	6</SPAN>	7</SPAN>	6</SPAN>			7</SPAN>	7</SPAN>	ggg</SPAN>
108</SPAN>	hhh</SPAN>	5</SPAN>	8</SPAN>	8</SPAN>	8</SPAN>			8</SPAN>	8</SPAN>	hhh</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL span=2><COL><COL span=2></COLGROUP>

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
93
94
95
96
97
98
99
100		Client	1-Jan	1-Feb	Mar-12	Apr-12
101		aaa	1	1	3	1
102		bbb	3	2	1	2
103		ccc	1	2	1	2
104		ddd	4	4	3	4
105		eee	5	4	5	4
106		fff	5	6	5	6
107		ggg	5	6	7	6
108		hhh	5	8	8	8
109
110
111
112
113
114
115

<tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C
1		1-Jan
2	1	aaa
3	1	ccc
4	3	bbb
5	4	ddd
6	5	eee
7	5	fff
8	5	ggg
9	5	hhh
10

<tbody>
</tbody>

Return​

Worksheet Formulas Cell Formula A2 =SMALL(INDEX(Sheet1!$Z$101:$AC$108,,MATCH(B$1,Sheet1!$Z$100:$AC$100,0)),ROWS(A$2:A2)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B2 {=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)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.