Code To Look For Part Then Find Earliest And Latest Years

[Dazzawm]

I have a file with a list of part numbers in column A on sheet 2. I need the code to look for these numbers on sheet 1 in column AC. When the number is found I need it to look at the earliest start year in column O where it appears and the latest end year in column Q then put in column B next to it on sheet 2.

Using just one part number as an example the very earliest year it appears on sheet 1 is 1974 and the very latest is 1990 so the result next to it would be 74-90 in column B then it would move onto the next number and so on...

Thanks

Excel Workbook
	M	N	O	P	Q	AC	AD
2		06	1983	05	1987	AXRAD60010
3		06	1983	11	1990	AXRAD60010
4		10	1990	01	1993	NYA
5		05	1987	08	1990	AXRAD60010
6		06	1990	10	1994	NYA
7		09	1980	03	1986	NYA
8		06	1986	09	1989	NYA
9		09	1987	01	1993	NYA
10		06	1986	10	1987	NYA
11		10	1987	01	1993	NYA
12		01	2007	12	2009	NYA
13		01	1985	10	1987	NYA
14		05	1974	01	1978	AXRAD60010
15		08	1977	04	1984	AXRAD60010
16		09	1978	04	1984	AXRAD60010
17
18
19
20						Earliest year so takes 74
21
22						Latest year so takes 90
23
Sheet1

Excel Workbook
	A	B	C
1	Part	Year
2	AXRAD60010	74-90	Result from sheet 1
3
Sheet2

 

[Dazzawm]

Thanks rallcorn but I get a runtime error '9' subscript out of range and when I debug it points to Sheets("Sheet2").Activate

 

[rallcorn]

Do you have a sheet in the workbook named Sheet2? What are the actual names of the worksheets in your file?

 

[JLGWhiz]

Thanks but this returns 0-0 in each cell in column B?

My error. There should be a period in front of Columns as shown below.

sd = WorksheetFunction.Min(.Columns("O").SpecialCells(xlCellTypeVisible))
ed = WorksheetFunction.Max(.Columns("Q").SpecialCells(xlCellTypeVisible))

 

[Dazzawm]

Thankyou both so much for your help.

 

[JLGWhiz]

Thankyou both so much for your help.

You're welcome
Regards, JLG

 

[Dazzawm]

In the code I just posted in #9, the variable in the part number in column A & "i" row of loop. Because the Min & Max formulas are array formulas with an "if" statement restricting the results to the Sheet1 Col AC rows that match the Sheet2 A&"i" part variable, it is simply finding the MIN year shown for that part number and the MAX year shown for that part number and parsing the right 2 digits from both of these. Another formula concatenates the Min & Max pieces into Sheet2 Col B.

I meant with the formula you first posted in #2

 

[rallcorn]

I meant with the formula you first posted in #2

Same thing as I described:
Because it is an array formula, the "If" statements are restricting the rows on Sheet1 Col AC to those rows that match the single Sheet2 Col A part for which the formula is being "applied" and then finding the minimum in Sheet1 Col O for ONLY those restricted rows and doing the same thing with the maximum in Sheet1 Col Q for ONLY those restricted rows.

Also, please note: Since you later stated that you're planning to run macro from your Personal Macro Workbook against numerous other workbooks:
If you choose to use the macro I gave you, you'll need to change it up. Get rid of

Dim wbThis As Workbook: Set wbThis = ThisWorkbook
wbThis.Activate

and then you'll have to add some code to refer to the workbook you're running it against AND a way to "find" the right sheets (this would also pertain to the references to Sheet1! within the formulas).

Good luck!