Based on a condition in one column, search for a year in another column, and display data from anoth

[MPSingley]

Inspection Date Hot/ Cold Condition Code Comments
9/29/1989 H 0 Comment
1
11/10/1989 H 0 Comment 2
1/16/1991 H 0 Comment
3
6/21/1991 H 0 Comment
4
12/6/1991 C 0
3/18/1992 H 0
8/12/1992 H 0
3/1/1993 H 0
3/16/1993 C 0
3/23/1994 H 0
3/30/1994 C 0
4/1/1995 C 0
4/15/2003 H 0
5/6/2003 C 0
I need to search for the first year that the inspection was hot, in
this case 9/29/89 and 11/10/89, and return the condition code for the
last hot date in 89 in one cell and the comments for all hot dates in
89 in another, as i am consolidating into a table by years. This then
needs to be done again for cold. A formula using just the "next year
available" as the previous one needs to be used because i am leaving
space for additional dates to be added later.

To me, my question makes sense, to others it may not. Let me know if I

should rephrase this. Thankyou
-matt

 

[ShaneDevenshire]

Hi MP,

Assume your data starts in cell A1 (titles) then the following macro will do
the job:

Sub Macro3()
Dim myComm As Comment
Range("A1" & [A1].End(xlDown).Row).Sort _
Key1:=Range("B2"), Order1:=xlDescending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("F1") = Range("B2")
Range("A2").Select
Set myComm = Range("D2").Comment
If Not myComm Is Nothing Then
Comm = myComm.Text
End If
Do Until Year(Selection) <> Year(Selection.Offset(1, 0))
Set myComm2 = Selection.Offset(1, 3).Comment
If Not myComm2 Is Nothing Then
Comm = Comm & ", " & myComm2.Text
End If
Selection.Offset(1, 0).Select
Loop
Range("G2") = Comm
End Sub

Note that this macro sorts your data, if that is not acceptable have the
macro insert a column after the data and enter sequential numbers in that
column. Make this the first step of the macro. At the end of the macro
resort the data on this column and then delete the column.

 

[ShaneDevenshire]

Hi Again,

I noticed that you want the condition code for the LAST date, I gave you the
first date.

Try this modification instead:

Sub Macro3()
Dim myComm As Comment
Range("A1" & [A1].End(xlDown).Row).Sort _
Key1:=Range("B2"), Order1:=xlDescending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("A2").Select
Set myComm = Range("D2").Comment
If Not myComm Is Nothing Then
Comm = myComm.Text
End If
Do Until Year(Selection) <> Year(Selection.Offset(1, 0))
Set myComm2 = Selection.Offset(1, 3).Comment
If Not myComm2 Is Nothing Then
Comm = Comm & ", " & myComm2.Text
End If
Selection.Offset(1, 0).Select
Loop
Range("G1") = Selection.Offset(0, 1)
Range("G2") = Comm
End Sub