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

 

[rallcorn]

Sheet2 B2 formula (ARRAY formula, after typing in the formula instead of pressing "Enter", press Ctrl+Shift+Enter and formula will automatically be surrounded by {} - don't type {}!)
=RIGHT(MIN(IF(Sheet1!AC:AC=A2,Sheet1!O:O)),2)&"-"&RIGHT(MAX(IF(Sheet1!AC:AC=A2,Sheet1!Q:Q)),2)

Copy formula down in Sheet2 Col B

 

[Dazzawm]

Thanks but I think this will need a macro. There are thousands of different numbers and could be on sheet 1 in AC several hundred times in the sheet.

 

[JLGWhiz]

Thanks but I think this will need a macro. There are thousands of different numbers and could be on sheet 1 in AC several hundred times in the sheet.

Give this a try.

Sub StrtEnd()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, fltRng As Range
Dim sd As Variant, ed As Variant
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:A" & lr)
    For Each c In rng
        If c <> "" Then
            With sh1
                Set fltRng = .Range("O2", .Cells(Rows.Count, "AC"))
                fltRng.AutoFilter 15, c.Value
                sd = WorksheetFunction.Min(Columns("O").SpecialCells(xlCellTypeVisible))
                ed = WorksheetFunction.Max(Columns("Q").SpecialCells(xlCellTypeVisible))
                sd = Right(sd, 2)
                ed = Right(ed, 2)
            End With
            c.Offset(0, 1) = sd & "-" & ed
            fltRng.AutoFilter
        End If
    Next
End Sub

 

[Dazzawm]

ADVERTISEMENT

So with this would each part, start year and end year be kept in a variable then it chooses the earliest and latest years for that part and gives me that as an answer?

 

[rallcorn]

Dazzawm, You'll notice that regardless of whether you use a macro or the formula I gave you, in both instances you're still using Min & Max formulas to find the Minimum & Maximum years for each part.

 

[Dazzawm]

ADVERTISEMENT

Give this a try.

Sub StrtEnd()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, fltRng As Range
Dim sd As Variant, ed As Variant
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:A" & lr)
    For Each c In rng
        If c <> "" Then
            With sh1
                Set fltRng = .Range("O2", .Cells(Rows.Count, "AC"))
                fltRng.AutoFilter 15, c.Value
                sd = WorksheetFunction.Min(Columns("O").SpecialCells(xlCellTypeVisible))
                ed = WorksheetFunction.Max(Columns("Q").SpecialCells(xlCellTypeVisible))
                sd = Right(sd, 2)
                ed = Right(ed, 2)
            End With
            c.Offset(0, 1) = sd & "-" & ed
            fltRng.AutoFilter
        End If
    Next
End Sub

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

 

[Dazzawm]

Dazzawm, You'll notice that regardless of whether you use a macro or the formula I gave you, in both instances you're still using Min & Max formulas to find the Minimum & Maximum years for each part.

It is something I will be using on several workbooks lots of times. I would be putting it in my Personal Macro Workbook so it would be more user-friendly for me to just select and run instead of copying>pasting formulas etc each time.

Also as I am curious, without some sort of variable how will the formula remember the earliest start year of a part in row 28 for example when it is found again in row 2000 then row 3000 etc etc ?

 

[rallcorn]

Same approach as I offered earlier but in macro:

Sub MinMaxYear()
Dim i As Long
Dim wbThis As Workbook: Set wbThis = ThisWorkbook
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
wbThis.Activate
 
Sheets("Sheet2").Activate
 
For i = 2 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
 
    'Assuming you don't already have something in Sheet2 Col D
    Range("D" & i).FormulaArray = "=RIGHT(MIN(IF(Sheet1!AC:AC=A" & i & ",Sheet1!O:O)),2)"
    
    'Assuming you don't already have something in Sheet2 Col E
    Range("E" & i).FormulaArray = "=RIGHT(MAX(IF(Sheet1!AC:AC=A" & i & ",Sheet1!Q:Q)),2)"
    
    'Concatenate Col D & Col E
    Range("B" & i).Formula = "=D" & i & "&""-""&E" & i
    
    'Turn to values
    Range("B" & i).Copy
        
        With Worksheets("Sheet2").Range("B" & i)
            .PasteSpecial xlValues
        End With
    
    Application.CutCopyMode = False
    
    'Get rid of the Col D & Col E formulas
    Range("D" & i & ":E" & i).ClearContents
 
Next i
 
MsgBox "Done!"
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

 

[rallcorn]

Also as I am curious, without some sort of variable how will the formula remember the earliest start year of a part in row 28 for example when it is found again in row 2000 then row 3000 etc etc ?

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.