# Code To Look For Part Then Find Earliest And Latest Years

#### Dazzawm

##### Well-known Member
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
4101990011993NYA
6061990101994NYA
7091980031986NYA
8061986091989NYA
9091987011993NYA
10061986101987NYA
11101987011993NYA
12012007122009NYA
13011985101987NYA
17
18
19
20Earliest year so takes 74
21
22Latest year so takes 90
23
Sheet1

Excel Workbook
ABC
1PartYear
3
Sheet2

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

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.

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.
Code:
``````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``````

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?

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.

Give this a try.
Code:
``````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, 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 ?

Same approach as I offered earlier but in macro:

Code:
``````Sub MinMaxYear()
Dim i As Long
Dim wbThis As Workbook: Set wbThis = ThisWorkbook

Application.ScreenUpdating = 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

End Sub``````

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.

Replies
10
Views
156
Replies
2
Views
564
Replies
1
Views
238
Replies
8
Views
189
Replies
3
Views
226

### Forum statistics

1,196,053
Messages
6,013,118
Members
441,749
Latest member
batel19 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back