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

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:D" & [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.

--
Cheers,
Shane Devenshire


" wrote:

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

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:D" & [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
--
Cheers,
Shane Devenshire


" wrote:

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