Find year

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

Data in column B:C</SPAN></SPAN>
Unique pattern in column E</SPAN></SPAN>
Count Patter in column F </SPAN></SPAN>

I need a solution to find year for the unique pattern result in column G:N</SPAN></SPAN>

Example</SPAN></SPAN> data</SPAN>

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

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5		Year	Patt		Unique Patt	Count	Year	Year	Year	Year	Year	Year	Year	Year
6		72/73	0 | 0 | 0 | 0		1 | 1 | 2 | 1
7		72/73	1 | 2 | 1 | 2		1 | 1 | 1 | 2
8		74/75	1 | 0 | 2 | 2		2 | 0 | 0 | 0
9		74/75	0 | 1 | 0 | 0		0 | 1 | 0 | 0	4	74/75	97/98	97/98	97/98
10		74/75	1 | 1 | 1 | 1		1 | 1 | 1 | 1	4	74/75	76/77	79/80	79/80
11		74/75	2 | 1 | 0 | 1		2 | 1 | 0 | 1	6	74/75	88/89	99/00	99/00	99/00	99/00
12		76/77	0 | 0 | 0 | 2		2 | 0 | 1 | 2	1	97/98
13		76/77	1 | 2 | 0 | 0		1 | 2 | 0 | 0	1	76/77
14		76/77	1 | 1 | 1 | 1		2 | 1 | 1 | 0	2	76/77	95/96
15		76/77	2 | 1 | 1 | 0		0 | 0 | 0 | 2	2	76/77	76/77
16		76/77	0 | 0 | 0 | 2		2 | 1 | 1 | 2
17		79/80	1 | 1 | 1 | 1		0 | 1 | 0 | 1	3	88/89	97/98	99/00
18		79/80	1 | 1 | 1 | 1		1 | 2 | 0 | 1	2	79/80	88/89
Sheet

 

[Rick Rothstein]

If you are looking for a VBA solution, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub GetCountsAndDates()
  Dim R As Long, X As Long, Data As Variant, Patterns As Variant
  Dim YearPatt As Variant, Matches As Variant, Result As Variant
  Data = Range("B6", Cells(Rows.Count, "C").End(xlUp))
  Patterns = Range("E6", Cells(Rows.Count, "E").End(xlUp))
  ReDim YearPatt(1 To UBound(Data))
  For R = 1 To UBound(YearPatt)
    YearPatt(R) = Data(R, 1) & "X" & Data(R, 2)
  Next
  ReDim Result(1 To UBound(Data), 1 To 99)
  For R = 1 To UBound(Patterns)
    Matches = Filter(YearPatt, Patterns(R, 1))
    Result(R, 1) = 1 + UBound(Matches)
    For X = 0 To UBound(Matches)
      Result(R, 2 + X) = Split(Matches(X), "X")(0)
    Next
  Next
  Range("F6").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

Another VBA solution could be a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Find_Year(rData As Range, sPatt As String, Num As Long) As String
  On Error Resume Next
  Find_Year = Split(Filter(Application.Transpose(Evaluate(rData.Columns(1).Address & "&""@""&" & rData.Columns(2).Address)), sPatt)(Num - 1), "@")(0)
End Function

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Year	Patt		Unique Patt	Count	Year	Year	Year	Year	Year	Year	Year	Year
6	72/73	0 | 0 | 0 | 0		1 | 1 | 2 | 1
7	72/73	1 | 2 | 1 | 2		1 | 1 | 1 | 2
8	74/75	1 | 0 | 2 | 2		2 | 0 | 0 | 0
9	74/75	0 | 1 | 0 | 0		0 | 1 | 0 | 0	4	74/75	97/98	97/98	97/98
10	74/75	1 | 1 | 1 | 1		1 | 1 | 1 | 1	4	74/75	76/77	79/80	79/80
11	74/75	2 | 1 | 0 | 1		2 | 1 | 0 | 1	6	74/75	88/89	99/00	99/00	99/00	99/00
12	76/77	0 | 0 | 0 | 2		2 | 0 | 1 | 2	1	97/98
13	76/77	1 | 2 | 0 | 0		1 | 2 | 0 | 0	1	76/77
14	76/77	1 | 1 | 1 | 1		2 | 1 | 1 | 0	2	76/77	95/96
15	76/77	2 | 1 | 1 | 0		0 | 0 | 0 | 2	2	76/77	76/77
16	76/77	0 | 0 | 0 | 2		2 | 1 | 1 | 2
17	79/80	1 | 1 | 1 | 1		0 | 1 | 0 | 1	3	88/89	97/98	99/00
Sheet1

 

[Rick Rothstein]

Function Find_Year(rData As Range, sPatt As String, Num As Long) As String
  On Error Resume Next
  Find_Year = Split(Filter(Application.Transpose(Evaluate(rData.Columns(1).Address & "&""@""&" & rData.Columns(2).Address)), sPatt)(Num - 1), "@")(0)
End Function

The OP is using Excel 2000... does all of that work in his version (I wasn't sure)?

 

[Peter_SSs]

.. does all of that work in his version (I wasn't sure)?

Absolutely no idea, that's why I said " .. solution could be .."
Same question would apply to your code, or any suggestions really, wouldn't it? This OP asks quite a few questions and I suspect not many, if any, of the people offering suggestions have a version that old to check.

 

[Kishan]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down as needed.

Spreadsheet Formulas

Cell Formula G6 {=IF(COLUMNS($G$5:G5)>$F6,"",INDEX($B$6:$B$92,SMALL(IF($E6=$C$6:$C$92,ROW($C$6:$C$92)-ROW($C$6)+1),COLUMNS($G$5:G5))))} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

AhoyNC, your formula worked well<o></o>
<o></o>
Thank you for your help<o></o>
<o></o>
Kind Regards,
Kishan

 

[Kishan]

Another VBA solution could be a user-defined function. To implement ..

Spreadsheet Formulas

Cell Formula G9 =Find_Year($B$6:$C$92,$E9,COLUMNS($G9:G9)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Peter_SSs, yes Function worked perfectly </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

If you are looking for a VBA solution, give this macro a try...

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sub GetCountsAndDates()
  Dim R As Long, X As Long, Data As Variant, Patterns As Variant
  Dim YearPatt As Variant, Matches As Variant, Result As Variant
  Data = Range("B6", Cells(Rows.Count, "C").End(xlUp))
  Patterns = Range("E6", Cells(Rows.Count, "E").End(xlUp))
  ReDim YearPatt(1 To UBound(Data))
  For R = 1 To UBound(YearPatt)
    YearPatt(R) = Data(R, 1) & "X" & Data(R, 2)
  Next
  ReDim Result(1 To UBound(Data), 1 To 99)
  For R = 1 To UBound(Patterns)
    Matches = Filter(YearPatt, Patterns(R, 1))
    Result(R, 1) = 1 + UBound(Matches)
    For X = 0 To UBound(Matches)
      Result(R, 2 + X) = Split(Matches(X), "X")(0)
    Next
  Next
  Range("F6").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub
[/TD]
[/TR]
</TBODY>[/TABLE]

Rick Rothstein, nice one it counts the patterns and search the year and finally live the values I liked your complete VBA one touch solution.</SPAN></SPAN>

Thank you very much for your help</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan </SPAN></SPAN>