Sub NRI_compile()
'be sure to have raw sheet active

Dim NRI_arr(1 To 18) As Variant
Dim rng As Range
Dim captured As Boolean
Dim IDS_table_width As Long, pasteOffset As Long
Dim IDsheet As Worksheet
Dim lookupRange As Range
Dim IDtableSheet As Worksheet
Dim datasheet As Worksheet
Dim startSheet As Worksheet
Set IDtableSheet = Sheets("IDS")

'find current sheet and active other sheet
Set startSheet = Sheets(ActiveSheet.Name)
Set datasheet = Sheets(InputBox("Name of sheet where data is stored?"))
datasheet.Activate
searchColumns = ActiveSheet.UsedRange.Columns.Count
searchrows = ActiveSheet.UsedRange.Rows.Count

'first determine placement of array output in IDS table
IDS_table_width = Range("ids[#all]").Columns.Count
pasteOffset = IDS_table_width - 2

'add variable headers
For i = 1 To 18
    IDtableSheet.Cells(1, IDS_table_width + i) = "NRI" & i
Next i

'begin search loop and build data array
For Each rng In Range("ids[T1_ID (if participant)]")
    If rng <> "" Then
        On Error GoTo Handler1
        rowNum = WorksheetFunction.Match(rng.Value, Range(Cells(1, 1), Cells(searchrows, 1)), 0)
        On Error GoTo 0
        For i = 1 To searchColumns
            Set lookupRange = Cells(rowNum, i)
        'search for beginning of PGN variables and determine which set to pull data from
            If Mid(Cells(1, i), 7, 3) = "NRI" Then
                If Cells(1, i).Offset(2, 0) Like "How close is * to you?" Then
                    Range(Cells(1, i), Cells(searchrows, i + 29)).Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
                    If WorksheetFunction.CountA(Range(Cells(1, i), Cells(1, i + 1)).Offset(rowNum - 1, 0)) <> 0 Then
                'find initial variable and insert into array
                        NRI_arr(1) = WorksheetFunction.Max(Range(Cells(1, i), Cells(1, i + 1)).Offset(rowNum - 1, 0))
                'find second and third variables and insert into array
                        For Z = 2 To 3
                        NRI_arr(Z) = Cells(1, i + Z).Offset(rowNum - 1, 0)
                        Next Z
                'bind remaining variables to array
                        If WorksheetFunction.CountA(Range(Cells(1, i).Offset(rowNum - 1, 4), Cells(1, i).Offset(rowNum - 1, 18))) <> 0 Then
                            For x = 1 To 15
                            NRI_arr(x + 3) = Cells(1, i).Offset(rowNum - 1, 4 + x - 1)
                            Next x
                        ElseIf WorksheetFunction.CountA(Range(Cells(1, i).Offset(rowNum - 1, 4 + 15), Cells(1, i).Offset(rowNum - 1, 18 + 15))) <> 0 Then
                            For x = 1 To 15
                            NRI_arr(x + 3) = Cells(1, i).Offset(rowNum - 1, 19 + x - 1)
                            Next x
                        End If
                        'set flag to skip second array calc, already found data
                        captured = True
                    End If
                'extract array to IDS table, exit loop if data found and extracted
                    For y = 1 To 18
                        rng.Offset(0, pasteOffset + y - 1) = NRI_arr(y)
                    Next y
                    If captured = True Then Exit For
                End If
            End If
    'delete array before continuing with next ID, reset flags
        captured = False
        Erase NRI_arr
        Next
    End If

Nextloop:
Next rng

'Return to previous functionality
startSheet.Activate
Exit Sub

Handler1:
On Error GoTo 0
Resume Nextloop

End Sub