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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]23[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF(G5="","",INDEX($D$1:$D$85,G5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]{=IF(MAX((F5=$C$5:$C$85)*(ROW($C$5:$C$85)))=0,"",MAX((F5=$C$5:$C$85)*(ROW($C$5:$C$85))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kishan]

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

	G	H

<tbody>
[TD="align: center"]5
[/TD]
[TD="align: right"]51
[/TD]
[TD="align: right"]23
[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]H5
[/TH]
[TD="align: left"]=IF(G5="","",INDEX($D$1:$D$85,G5))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

DRSteele, getting requested results Prefect!!

Thank you for giving a spot on solution

Regards,
Kishan

 

[DRSteele]

Good show! You're welcome.