Attribute VB_Name = "Module1"
Sub formatgpr()
' Macro recorded 2/7/2006 by Hana Lee

'FORMAT GPR FILE FOR ANALYSIS
Dim exptname
exptname = _
Application.InputBox(Prompt:="What is the experiment name?", _
Title:="Experiment Name", Type:=2)
ActiveSheet.Name = exptname
    
ActiveSheet.Copy
Workbooks(Application.Workbooks.Count).Sheets(exptname).Select
    
Rows("1:31").Delete Shift:=xlUp
Range("A1").Select
Do While IsEmpty(ActiveCell) = False
    If ActiveCell.Formula = "Name" _
    Or ActiveCell.Formula = "ID" _
    Or ActiveCell.Formula = "F635 Median - B635" _
    Or ActiveCell.Formula = "F532 Median - B532" _
    Or ActiveCell.Formula = "Flags" Then
        ActiveCell.Offset(0, 1).Select
    Else
        Range(ActiveCell, ActiveCell.End(xlDown)).Delete Shift:=xlToLeft
    End If
Loop

Application.Dialogs(xlDialogSaveAs).Show
    
End Sub
Sub control()
' Macro recorded 2/7/2006 by Hana Lee

' DECIDE WHICH FORMAT TO USE
Dim arraytype
arraytype = _
    Application.InputBox(Prompt:="What type of array format are you using? (Answer 2 or 16).", _
    Title:="Two replicates or multiwell format", Type:=1)

Select Case arraytype
    Case 2
        Call twoarray
    Case 16
       Call multiwell
    Case Else
        Exit Sub
End Select

End Sub
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
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
Sub datainput()
' Macro recorded 2/7/2006 by Hana Lee

'SORT BY ORDER ALONG CHROMOSOME
Dim i
Columns("A:A").Select
For i = 1 To 9 Step 1
    Selection.Replace What:="_" & i & "_", Replacement:="_0" & i & "_", LookAt:=xlPart
Next i

Columns("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

'AVERAGE ALL REPLICATES
Range("A1").Select
Do While IsEmpty(ActiveCell) = False
    If ActiveCell.Formula Like "*_R" = True _
    Or ActiveCell.Formula Like "*_A" = True Then
        Range(ActiveCell, ActiveCell.Offset(0, 2)).Insert Shift:=xlDown
        ActiveCell.Formula = ActiveCell.Offset(1, 0).Formula
        ActiveCell.Offset(0, 1).Formula = ActiveCell.Offset(1, 1).Formula
        If ActiveCell.Offset(2, 0).Formula Like "*_RA" = True Then
            With ActiveCell.Offset(0, 2)
                .FormulaR1C1 = "=AVERAGE(R[1]C,R[2]C,R[3]C)"
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
            ActiveCell.Offset(0, -2).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(3, 2)).Delete Shift:=xlUp
        Else
            With ActiveCell.Offset(0, 2)
                .FormulaR1C1 = "=AVERAGE(R[1]C,R[2]C)"
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
            ActiveCell.Offset(0, -2).Select
            Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(2, 2)).Delete Shift:=xlUp
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Loop

Columns("C:C").Replace What:="#DIV/0!", Replacement:="", LookAt:=xlPart

With Columns("A:A")
    .Replace What:="_RA", Replacement:="", LookAt:=xlPart
    .Replace What:="_R", Replacement:="", LookAt:=xlPart
    .Replace What:="_A", Replacement:="", LookAt:=xlPart
End With

End Sub