Hello Lory,

You can use the following formula: =IFERROR(INDEX(‘Round 1′!$B$3:$AJ$25,MATCH(A5,’Round 1’!$B$3:$B$25, 0),34),””)
It will leave a cell blank if no matches are found.

Regards
ExcelDemy

Hello Kndn,

The Measuring unit of the result is “Mile”.

Hello,

You are most welcome.

Regards
ExcelDemy

Hello,

You are welcome.

Regards
ExcelDemy

How do you leave a blank cell, as apposed to “$N/A”, when you are using index and match function.

My cell currently has the formula: =INDEX(‘Round 1′!$B$3:$AJ$25,MATCH(A5,’Round 1’!$B$3:$B$25, 0),34).

Not all of the “matches” will be found on each worksheet (it is a scoresheet and not all individuals compete each round), and therefore return the $N/A.

Thanks!

Thanks a lot

KS

Dear Icarus Builders,

You are most welcome.

Regards
ExcelDemy

Hello Vini,

Updated the code spilt data into multiple Excel file.

Sub Split_Data_Multiple_Files()
    Dim L As Long
    Dim DS As Worksheet
    Dim VCL As Integer
    Dim XCL As Long
    Dim MARY As Variant
    Dim title As String
    Dim titlerow As Integer
    Dim newWorkbook As Workbook
    Dim newSheet As Worksheet
    Dim filePath As String
    
    Application.ScreenUpdating = False
    
    VCL = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Type:=1)
    Set DS = ActiveSheet
    L = DS.Cells(DS.Rows.Count, VCL).End(xlUp).Row
    title = "A1"
    titlerow = DS.Range(title).Cells(1).Row
    XCL = DS.Columns.Count
    DS.Cells(3, XCL) = "Unique"
    
    For X = 2 To L
        On Error Resume Next
        If DS.Cells(X, VCL) <> "" And Application.WorksheetFunction.Match(DS.Cells(X, VCL), DS.Columns(XCL), 0) = 0 Then
            DS.Cells(DS.Rows.Count, XCL).End(xlUp).Offset(1) = DS.Cells(X, VCL)
        End If
    Next
    
    MARY = Application.WorksheetFunction.Transpose(DS.Columns(XCL).SpecialCells(xlCellTypeConstants))
    DS.Columns(XCL).Clear
    
    For X = 2 To UBound(MARY)
        DS.Range(title).AutoFilter field:=VCL, Criteria1:=MARY(X) & ""
        Set New_Workbook = Workbooks.Add
        Set New_Sheet = newWorkbook.Sheets(1)
        DS.Range("A" & titlerow & ":A" & L).EntireRow.Copy New_Sheet.Range("A4")
        DS.AutoFilterMode = False
        New_Sheet.Columns.AutoFit
        filePath = ThisWorkbook.Path & "\" & MARY(X) & ".xlsx"
        New_Workbook.SaveAs filePath
        New_Workbook.Close False
    Next
    
    DS.Activate
    Application.ScreenUpdating = True
End Sub

Regards
ExcelDemy