Find pattern last row num and assigned num

[Kishan]

Using Excel 2000

Hi,

I got pattern in column C and in the column D Assign Num and in the column F got 27 unique patterns

I am looking for 2 results...
1) In the column G find last the last row number of the each pattern
2) In the column H find the assigned number is matched in the column D in the row find pattern

Example....

Book1
	A	B	C	D	E	F	G	H	I
1
2
3							Find Last
4			Pattern	Assigned Num		Pattern	Row N	Assigned Num
5			1 | 1 | 1			1 | 1 | 1	51	23
6			1 | 1 | 2	0		1 | 1 | 2	52	30
7			2 | 2 | 2	0		1 | 1 | X	53	14
8			2 | 2 | X	0		1 | 2 | 1	72	18
9			2 | X | 1	0		1 | 2 | 2	55	14
10			2 | X | 2	0		1 | 2 | X	56	14
11			2 | 2 | 1	0		X | X | 1
12			1 | X | 1	0		1 | X | 2	58	23
13			1 | X | X	0		1 | X | X	59	23
14			1 | 1 | X	0		2 | 1 | 1	60	23
15			1 | 2 | 1	0		X | 2 | X
16			2 | 2 | 1	5		2 | 1 | X	62	18
17			1 | X | X	4		2 | 2 | 1	80	17
18			2 | 1 | 1	0		2 | 2 | 2	81	17
19			2 | 1 | 2	0		2 | 2 | X	82	17
20			X | 1 | X	0		X | X | X
21			1 | 1 | 1	16		2 | X | 2	49	39
22			1 | 1 | 2	16		2 | X | X	50	0
23			1 | 1 | X	9		X | 1 | 1	75	9
24			1 | 2 | 1	9		X | 1 | 2	76	5
25			1 | 2 | 2	0		X | 1 | X	77	9
26			1 | 2 | X	0		X | 2 | 1	78	9
27			1 | X | 1	15		X | 2 | 2	79	9
28			1 | 1 | 1	7		X | X | 2
29			1 | X | 2	0		2 | 1 | 2	61	23
30			1 | X | X	13		1 | X | 1	85	1
31			2 | 1 | 1	13		2 | X | 1	83	9
32			2 | 1 | 2	13
33			2 | 2 | 1	17
34			1 | X | X	4
35			1 | X | 2	6
36			1 | X | X	2
37			2 | 1 | 1	6
38			2 | 1 | 2	6
39			1 | 1 | X	16
40			1 | 2 | 1	16
41			1 | 2 | 2	16
42			1 | 2 | X	16
43			1 | X | 1	16
44			2 | 1 | X	0
45			2 | 2 | 1	12
46			2 | 2 | 2	39
47			2 | 2 | X	39
48			2 | X | 1	39
49			2 | X | 2	39
50			2 | X | X	0
51			1 | 1 | 1	23
52			1 | 1 | 2	30
53			1 | 1 | X	14
54			1 | 2 | 1	14
55			1 | 2 | 2	14
56			1 | 2 | X	14
57			1 | X | 1	14
58			1 | X | 2	23
59			1 | X | X	23
60			2 | 1 | 1	23
61			2 | 1 | 2	23
62			2 | 1 | X	18
63			2 | 2 | 1	18
64			2 | 2 | 2	18
65			2 | 2 | X	18
66			X | 1 | 1	0
67			X | 1 | 2	0
68			X | 1 | X	48
69			X | 2 | 1	0
70			X | 2 | 2	0
71			X | 1 | 2	4
72			1 | 2 | 1	18
73			1 | X | 1	16
74			2 | X | 1	26
75			X | 1 | 1	9
76			X | 1 | 2	5
77			X | 1 | X	9
78			X | 2 | 1	9
79			X | 2 | 2	9
80			2 | 2 | 1	17
81			2 | 2 | 2	17
82			2 | 2 | X	17
83			2 | X | 1	9
84			1 | X | 1	11
85			1 | X | 1	1
86
87
88
89
90
Sheet1

Thank you in advance

Regards,
Kishan

 

[DRSteele]

Try this in G5 and H5 and copy down for each pattern you've listed:

	G	H
5	51	23

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula H5 =IF(G5="","",INDEX($D$1:$D$85,G5)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G5 {=IF(MAX((F5=$C$5:$C$85)*(ROW($C$5:$C$85)))=0,"",MAX((F5=$C$5:$C$85)*(ROW($C$5:$C$85))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[Kishan]

Try this in G5 and H5 and copy down for each pattern you've listed:

	G	H
5	51	23

<tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula H5 =IF(G5="","",INDEX($D$1:$D$85,G5)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G5 {=IF(MAX((F5=$C$5:$C$85)*(ROW($C$5:$C$85)))=0,"",MAX((F5=$C$5:$C$85)*(ROW($C$5:$C$85))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

DRSteele, getting requested results Prefect!!

Thank you for giving a spot on solution

Regards,
Kishan

 

[DRSteele]

Good show! You're welcome.