Based on a condition in one column, search for a year in another column, and display data from another column in the same row look
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 |
Based on a condition in one column, search for a year in another c
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 |
Based on a condition in one column, search for a year in another c
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 |
All times are GMT +1. The time now is 08:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com