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

' ARRANGE DATA IN FOLLOWING FORMAT
' [  A  ][ B ][     C     ][     D     ][  E  ][  F  ][     G     ][     H     ][  I  ]
' [NAME ][ID ][F635 MEDIAN][F532 MEDIAN][FLAGS][blank][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, 8)).Cut _
            Destination:=Range(ActiveCell.Offset(0, 10), ActiveCell.Offset(0, 18))
        End If
    End If
ActiveCell.Offset(1, 0).Select
Loop

' SORT DATA BY FEATURE NAME
Columns("A:I").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
Columns("K:S").Sort Key1:=Range("K2"), 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("K2").End(xlDown).Row - 1

Range("F2", Range("F2").Offset(markernumber - 1, 0)).Copy _
    Destination:=Range("J2", Range("J2").Offset(markernumber - 1, 0))

If cyratio = 5 Then
    Union(Range("P2", Range("P2").Offset(controlnumber - 1, 0)), _
    Range("T2", Range("T2").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
        Union(Range("P2", Range("P2").Offset(controlnumber - 1, 0)), _
    Range("T2", Range("T2").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
Columns("L:L").Insert Shift:=xlToRight
Columns("S:S").Insert Shift:=xlToRight
Union(Range("S2", Range("S2").Offset(controlnumber - 1, 0)), _
Range("X2", Range("X2").Offset(controlnumber - 1, 0))).FormulaR1C1 = _
    "=IF(AND(RC[-1]>=0.3,RC[-1]<=3.3),RC[-1],"""")"

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

' NORMALIZE RATIOS
Union(Range("G2", Range("G2").Offset(markernumber - 1, 0)), _
Range("L2", Range("L2").Offset(markernumber - 1, 0))).FormulaR1C1 = _
    "=IF(RC[-1]<>"""",RC[-1]/R" & controlnumber + 3 & "C[12],"""")"
    
' CALCULATE AVERAGE OF NORMALIZED RATIOS
' SCREEN OUT AVERAGES WITH HIGH STANDARD DEVIATION BETWEEN REPLICATES
Columns("M:N").Insert Shift:=xlToRight
Range("M2", Range("M2").Offset(markernumber - 1, 0)).FormulaR1C1 = _
    "=IF(AND(RC[-1]<>"""",RC[-6]<>""""),IF(STDEV(RC[-1],RC[-6])<=0.6,AVERAGE(RC[-1],RC[-6]),""""),IF(OR(RC[-1]<>"""",RC[-6]<>""""),AVERAGE(RC[-1],RC[-6]),""""))"

' FLAG AVERAGES FROM ONLY ONE REPLICATE, MARKERS WITH NO DATA, REPLICATES WITH HIGH STANDARD DEVIATION
' FLAG RATIOS WITH NEGATIVE SIGNAL IN ONE CHANNEL
Columns("N:O").Insert Shift:=xlToRight
Range("N2", Range("N2").Offset(markernumber - 1, 0)).FormulaR1C1 = _
    "=IF(OR(RC[-2]="""",RC[-7]=""""),IF(AND(RC[-2]="""",RC[-7]=""""),""A"",""B""),IF(STDEV(RC[-2],RC[-7])>0.6,""C"",""""))"
Range("O2", Range("O2").Offset(markernumber - 1, 0)).FormulaR1C1 = _
    "=IF(OR(RC[-3]<>"""",RC[-8]<>""""),IF(OR(RC[-6]<0,RC[-7]<0,RC[-11]<0,RC[-12]<0),""N"",""""),"""")"
Columns("P:P").Insert Shift:=xlToRight
With Range("P2", Range("P2").Offset(markernumber - 1, 0))
    .FormulaR1C1 = "=CONCATENATE(RC[-1],RC[-2])"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With
Columns("N:O").Delete Shift:=xlToLeft
    
' ANNOTATION KEY
' A = NO USEABLE DATA FROM EITHER REPLICATE, DUE TO LOW SIGNAL, ETC.
' B = DATA FROM ONLY ONE REPLICATE
' C = REPLICATES HAVE HIGH STANDARD DEVIATION > 0.6
' N = SIGNAL IN ONE CHANNEL WAS NEGATIVE

' FORMATTING HEADERS
Range("F1,K1").FormulaR1C1 = "Ratio"
Range("G1,L1").FormulaR1C1 = "Norm."
Range("B1:F1").Copy Destination:=Range("Q1:U1")
Range("C1:F1").Copy Destination:=Range("W1:Z1")
Range("V1,AA1").FormulaR1C1 = "Screen"
Range("M1").FormulaR1C1 = "Average"
Range("P1").FormulaR1C1 = "Control"
Rows("1:1").Font.Bold = True
    
'EXPORT FOR PYTHON SCRIPT
Union(Range("A1", Range("B2").Offset(markernumber - 1, 0)), _
    Range("M1", Range("M2").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