Sub multiwell()
' Macro recorded 2/7/2006 by Hana Lee

' ARRANGE DATA IN FOLLOWING FORMAT
' [  A  ][ B ][     C     ][     D     ][  E  ]
' [NAME ][ID ][F635 MEDIAN][F532 MEDIAN][FLAGS]

' REMOVE BLANK ROWS
' SEPARATE CONTROL FEATURES
Range("A1").Select
Do While IsEmpty(ActiveCell) = False
    If ActiveCell.Formula = "blank" Then
        ActiveCell.EntireRow.Delete Shift:=xlUp
        ActiveCell.Offset(-1, 0).Select
    Else
        If ActiveCell.Formula Like "ALD*" = True _
        Or ActiveCell.Formula Like "HSP90*" = True _
        Or ActiveCell.Formula Like "POS*" = True Then
            Range(ActiveCell, ActiveCell.Offset(0, 4)).Cut _
            Destination:=Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 10))
        End If
    End If
ActiveCell.Offset(1, 0).Select
Loop

' SORT DATA BY FEATURE NAME
Columns("A:E").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
Columns("G:K").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
' INPUT RATIO FOR ANALYSIS
cyratio = _
Application.InputBox(Prompt:="Which channel goes in the ratio denominator? (Answer 5 or 3.)", _
    Title:="Cy5/Cy3 or Cy3/Cy5", Default:=5, Type:=1)

' SCREEN OUT LOW SIGNALS AND FLAGGED FEATURES
' CALCULATE RATIOS
Range("F2").Select
Do While ActiveCell.Offset(0, -5) <> "x"
    If (cyratio = 5 And ActiveCell.Offset(0, -5).Formula Like "INS*" = True) = True _
    Or (cyratio = 3 And ActiveCell.Offset(0, -5).Formula Like "DEL*" = True) = True Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-3]+RC[-2]>75,RC[-3]<>0,RC[-2]<>0,RC[-1]>=0),ABS(RC[-2]/RC[-3]),"""")"
    Else
        If (cyratio = 5 And ActiveCell.Offset(0, -5).Formula Like "DEL*" = True) = True _
        Or (cyratio = 3 And ActiveCell.Offset(0, -5).Formula Like "INS*" = True) = True Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-3]+RC[-2]>75,RC[-3]<>0,RC[-2]<>0,RC[-1]>=0),ABS(RC[-3]/RC[-2]),"""")"
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Loop

Dim markernumber
markernumber = Range("F2").End(xlDown).Row - 1
Dim controlnumber
controlnumber = Range("G2").End(xlDown).Row - 1

If cyratio = 5 Then
    Range("L2", Range("L2").Offset(controlnumber - 1, 0)).FormulaR1C1 = _
    "=IF(AND(RC[-3]+RC[-2]>75,RC[-3]<>0,RC[-2]<>0,RC[-1]>=0),ABS(RC[-2]/RC[-3]),"""")"
Else
    If cyratio = 3 Then
        Range("L2", Range("L2").Offset(controlnumber - 1, 0)).FormulaR1C1 = _
        "=IF(AND(RC[-3]+RC[-2]>75,RC[-3]<>0,RC[-2]<>0,RC[-1]>=0),ABS(RC[-3]/RC[-2]),"""")"
    End If
End If

' SCREEN OUT OUTLIERS IN THE CONTROLS
Columns("G:G").Insert Shift:=xlToRight
Range("N2", Range("N2").Offset(controlnumber - 1, 0)).FormulaR1C1 = _
    "=IF(AND(RC[-1]>=0.3,RC[-1]<=3.3),RC[-1],"""")"

' CALCULATE NORMALIZATION FACTOR
Range("N2").Offset(controlnumber + 1, 0).FormulaR1C1 = _
    "=AVERAGE(R[-" & controlnumber + 1 & "]C:R[-2]C)"

' NORMALIZE RATIOS
Range("G2", Range("G2").Offset(markernumber - 1, 0)).FormulaR1C1 = _
    "=IF(RC[-1]<>"""",RC[-1]/R" & controlnumber + 3 & "C[7],"""")"
    
' FLAG AVERAGES FROM ONLY ONE REPLICATE, MARKERS WITH NO DATA, REPLICATES WITH HIGH STANDARD DEVIATION
' FLAG RATIOS WITH NEGATIVE SIGNAL IN ONE CHANNEL
    Columns("H:I").Insert Shift:=xlToRight
    Range("H2", Range("H2").Offset(markernumber - 1, 0)).FormulaR1C1 = _
        "=IF(RC[-1]="""",""A"",IF(OR(RC[-4]<0,RC[-5]<0),""N"",""""))"
    
' ANNOTATION KEY
' A = NO USEABLE DATA DUE TO LOW SIGNAL, ETC.
' N = SIGNAL IN ONE CHANNEL WAS NEGATIVE

' FORMATTING HEADERS
    Range("F1").FormulaR1C1 = "Ratio"
    Range("G1").FormulaR1C1 = "Norm."
    Range("B1:F1").Copy Destination:=Range("K1:O1")
    Range("P1").FormulaR1C1 = "Screen"
    Range("J1").FormulaR1C1 = "Control"
    Rows("1:1").Font.Bold = True
    
'EXPORT FOR PYTHON SCRIPT
Union(Range("A1", Range("B2").Offset(markernumber - 1, 0)), _
    Range("G1", Range("G2").Offset(markernumber - 1, 0))).Copy
Workbooks.Add
Workbooks(Application.Workbooks.Count).Sheets(1).Range("A1", Range("C2").Offset(markernumber - 1)).PasteSpecial Paste:=xlPasteValues

End Sub