Find last row number or the group after condition is applied.

[motilulla]

Hello,

I have 1 to 5 groups in the column "C" and in the cells E6:E10 unique groups numbers array formula is entered in the cells

F6:F10 =IF(MAX((E6=$C$1:$C$52)*(ROW($C$1:$C$52)))=0,,MAX((E6=$C$1:$C$52)*(ROW($C$1:$C$52))))
and it is conformed by CTRL + SHIFT + ENTER.

So I get the last row number each of the group as shown result in the cells F6:F10 this works perfect.

Now what I need I have same 1 to 5 groups in the column "M" and unique group numbers in the cells O6:O10 "but I want apply the condition in the Column "K" by placing "1" in it so to check only the groups 3, 4 and 5 last row number. As this result would be different than previous as shown in the cells P6:P10...please suggest the new formula for this conditional grouping to find last row number in cells P6:P10.

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3											Condition					Conditional
4			Group		Group	Find					To Check		Group		Group	Find
5			Group		Group	Last Row					Last Row		Group		Group	Last Row
6			5		1	23							5		1	0
7			4		2	30							4		2	0
8			4		3	53							4		3	48
9			4		4	51							4		4	49
10			5		5	47							5		5	46
11			3										3
12			4										4
13			5										5
14			4								1		4
15			4										4
16			4										4
17			5										5
18			4										4
19			4								1		4
20			4										4
21			4										4
22			1										1
23			3										3
24			5										5
25			4										4
26			3										3
27			4										4
28			5										5
29			2										2
30			4										4
31			5										5
32			4										4
33			3										3
34			5								1		5
35			3										3
36			4										4
37			5										5
38			4										4
39			5										5
40			5								1		5
41			4										4
42			4								1		4
43			4								1		4
44			5										5
45			3										3
46			5								1		5
47			4								1		4
48			3								1		3
49			4								1		4
50			4										4
51			3										3
52			3										3
53
54
55

For a more detail the image is attached. Pease suggest new conditional formula


Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Fluff]

How about

=IF(ISNA(LOOKUP(2,1/($M$6:$M$52=O6)/($K$6:$K$52=1),ROW($M$6:$M$52))),0,LOOKUP(2,1/($M$6:$M$52=O6)/($K$6:$K$52=1),ROW($M$6:$M$52)))

 

[motilulla]

How about

=IF(ISNA(LOOKUP(2,1/($M$6:$M$52=O6)/($K$6:$K$52=1),ROW($M$6:$M$52))),0,LOOKUP(2,1/($M$6:$M$52=O6)/($K$6:$K$52=1),ROW($M$6:$M$52)))

Fluff, I cannot even imagine that the formula could be written without array this is really amazing it worked magical and result are as treat. ?

This easy formula has been big help for my necessity. I do appreciate your great assist and thank you for the time you spent to solve this in a simple way.

Kind Regards,
Moti

 

[Fluff]

You could also use this instead of you original formula in F6

=IF(ISNA(LOOKUP(2,1/($C$6:$C$52=E6),ROW($C$6:$C$52))),,LOOKUP(2,1/($C$6:$C$52=E6),ROW($C$6:$C$52)))

 

[motilulla]

You could also use this instead of you original formula in F6

=IF(ISNA(LOOKUP(2,1/($C$6:$C$52=E6),ROW($C$6:$C$52))),,LOOKUP(2,1/($C$6:$C$52=E6),ROW($C$6:$C$52)))

Wow Fluff, that is so nice I will replace my older formula with this your's one it is in fact very useful without the array thank you very much for letting me know otherwise I would not have it ended.

Good luck and have a joyful weekend. ?

Kind Regards,
Moti

 

[Fluff]

Glad to help & thanks for the feedback.