I am working in Excel with 2 spreadsheets such as the following:
Sheet 1: 'Summary'
| -- | ---A -----| ------ B -----|--C-- |-- D - |-- E-- |-- F-- |- G- |
| 1 |__ ID___ | Start Date | FEB | MAR | APR | MAY | JUN || 2 | Y01A01 |__________|_1_ |_ 1 __| _ 1_ |_ 1__ | 1_ |
| 3 | Y01A02 |__________|_0_ |_ 0__ |_ 0__ |_ 1__ | 1 || 4 | Y01A03 |__________|_ 1_ |_ 1_ | _ 1_ |__ 1_ |_ 0 |
Sheet 2: 'Feb'
| -- | ---- A - | ---- B ----- | -- C - |-- D -- | ----- E -------- |
| 1 | ID____ | Date____ | Fever|Cough|Running Nose|| 2 | Y01A01 | 1/2/2013 | NA __ | NA__ | NA _______ |
| 3 | Y01A01 | 2/2/2013 | NA__ | NA __ | NA _______ |
| 4 | Y01A01 | 3/2/2013 | 0 ___ |_ 1 __ | _ 1_________|
Sheet 1 is a summary table of every participants of a flu prevention program. They would have to record their flu symptom everyday month by month. Under the column of C:G (i.e. Feb to June), "1" indicates yes the person has done so and "0" indicates no. However, not every person participate to the program and starts recording on the first day of a month. For instance on Sheet 2, participant Y01A01 started on 3/2/2013 and thus no records were shown (i.e. NA) on 1/2/2013 and 2/2/2013. (on Sheet 2, 0 = no; 1 = yes)
My question is that I would like to find the start date (Sheet 1, Column B) for every person using Excel formula.
I have tried to use the formula
=INDEX('Feb'!B:B,MATCH(Summary!A2,'Feb'!A:A,0),ISNUMBER(INDEX('Feb'!C:C,MATCH(Summary!A2,'Feb'!A:A,0))))
But it shows 1/2/2013 incorrectly, instead of 3/2/2013.
I hope you know how to handle macros? If you don't please comment I will guide you.
Sub first_date()
Dim MyRange As Range
Dim MyRange1 As Range
Worksheets("Summary").Select
Set MyRange = Worksheets("Summary").Range([a2], [a2].End(xlDown)).Rows.SpecialCells(xlCellTypeVisible)
Worksheets("Feb").Select
Set MyRange1 = Worksheets("Feb").Range([a2], [a2].End(xlDown)).Rows.SpecialCells(xlCellTypeVisible)
Worksheets("Summary").Select
For Each MyCell In MyRange.Cells
With MyRange1
Set rng1 = .Cells.Find(MyCell.Value)
If Not rng1 Is Nothing Then
Do
If IsNumeric(rng1.Offset(0, 2).Value) Then
MyCell.Offset(0, 1).Value = rng1.Offset(0, 1)
Exit Do:
End If
Set rng1 = .Cells.FindNext(rng1)
Loop While rng1.Address <> strAddress
End If
End With
Next
End Sub