Sub FormatAndExportCSV()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim savePath As String
    Dim dataArray As Variant
    Dim i As Long
    Dim userId As String
    Dim spektrixEventInstanceId As String
    Dim price As String
    Dim commission As String
    Dim ticketTypeName As String
    Dim colMap As Object
    Dim inputCol As String
    Dim inputCol2 As String
    Dim deleteUnmapped As Boolean
    Dim transactionDateColumnNumber As Long
    Dim key As Variant

    ' Set the active worksheet
    Set ws = ActiveSheet

    ' Prompt user for required values
    userId = InputBox("Enter the ID:", "User Input")
    If userId = "" Then Exit Sub
    spektrixEventInstanceId = InputBox("Enter the Spektrix Event Instance ID:", "User Input")
    If spektrixEventInstanceId = "" Then Exit Sub
    price = InputBox("Enter the Price:", "User Input")
    commission = InputBox("Enter the Commission:", "User Input")
    ticketTypeName = InputBox("Enter the Ticket Type Name:", "User Input")

    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Create a dictionary to store column mappings
    Set colMap = CreateObject("Scripting.Dictionary")

    ' Output column names
    colMap.Add "OwnerSpektrixId", ""
    colMap.Add "TransactionDate", ""
    colMap.Add "SpektrixEventInstanceId", ""  ' renamed here
    colMap.Add "SeatingAreaName", ""
    colMap.Add "SeatName", ""
    colMap.Add "Price", ""
    colMap.Add "Commission", ""
    colMap.Add "TicketTypeName", ticketTypeName
    colMap.Add "TicketCustomField:ImportedBarcode", ""

    ' Ask for column mappings
    For Each key In colMap.Keys
        If key <> "OwnerSpektrixId" And key <> "SpektrixEventInstanceId" And _
           key <> "Price" And key <> "Commission" And key <> "TicketTypeName" Then

            If key = "TransactionDate" Then
                 Do
                    inputCol = InputBox("Enter the column letter for '" & key & "':", "Column Mapping")
                    If inputCol = "" Then
                        MsgBox "This field is required", vbExclamation
                    ElseIf Not IsValidColumn(inputCol) Then
                        MsgBox "Invalid column letter.", vbExclamation
                    Else
                        Exit Do
                    End If
                Loop
                colMap(key) = inputCol

            ElseIf key = "SeatName" Then
                Do
                    inputCol = InputBox("Enter the first column letter for '" & key & "':", "Column Mapping")
                    inputCol2 = InputBox("Enter the second column letter for '" & key & "':", "Column Mapping")
                    If inputCol = "" And inputCol2 = "" Then
                        MsgBox "At least one column is required for SeatName", vbExclamation
                    ElseIf (inputCol <> "" And Not IsValidColumn(inputCol)) Or _
                           (inputCol2 <> "" And Not IsValidColumn(inputCol2)) Then
                        MsgBox "Invalid column letter.", vbExclamation
                    Else
                        Exit Do
                    End If
                Loop
                colMap(key) = inputCol & "," & inputCol2

            Else
                Do
                    inputCol = InputBox("Enter the column letter for '" & key & "':", "Column Mapping")
                    If inputCol = "" Then
                        MsgBox "This field is required", vbExclamation
                    ElseIf Not IsValidColumn(inputCol) Then
                        MsgBox "Invalid column letter.", vbExclamation
                    Else
                        Exit Do
                    End If
                Loop
                colMap(key) = inputCol
            End If
        End If
    Next key

    deleteUnmapped = (MsgBox("Delete unmapped columns?", vbYesNo) = vbYes)

    ' Prompt user for save location
    With Application.FileDialog(msoFileDialogSaveAs)
        .Title = "Save CSV File"
        If .Show = -1 Then
            savePath = .SelectedItems(1)
            If Right(savePath, 4) <> ".csv" Then savePath = savePath & ".csv"
        Else
            Exit Sub
        End If
    End With

    ' Read data into an array for faster processing
    dataArray = ws.Range("A1:" & LastColumnLetter(ws) & lastRow).Value

    transactionDateColumnNumber = ColumnLetterToNumber(colMap("TransactionDate"))
    If transactionDateColumnNumber = 0 Then
        MsgBox "Error: Invalid column letter for TransactionDate.  Please check the column mapping.", vbCritical
        Exit Sub
    End If

    ' Open file for writing
    Dim fileNum As Integer
    fileNum = FreeFile
    Open savePath For Output As #fileNum

    ' Write headers
    Print #fileNum, Join(colMap.Keys, ",")

    ' Write data rows
    For i = 2 To UBound(dataArray, 1)
        Dim outputRow As String
        If transactionDateColumnNumber > 0 And transactionDateColumnNumber <= UBound(dataArray, 2) Then
            outputRow = userId & "," & dataArray(i, transactionDateColumnNumber) & "," & _
                        spektrixEventInstanceId & ","
        Else
            MsgBox "Error: TransactionDate column is out of range for row " & i & ".", vbCritical
            Exit Sub
        End If

        For Each key In colMap.Keys
            If key <> "OwnerSpektrixId" And key <> "TransactionDate" And key <> "SpektrixEventInstanceId" Then

                If key = "Price" Then
                    outputRow = outputRow & price & ","
                ElseIf key = "Commission" Then
                    outputRow = outputRow & commission & ","
                ElseIf key = "TicketTypeName" Then
                    outputRow = outputRow & ticketTypeName & ","
                ElseIf key = "TicketCustomField:ImportedBarcode" Then
                    Dim barcodeValue As Variant
                    barcodeValue = dataArray(i, ColumnLetterToNumber(colMap(key)))
                    If IsNumeric(barcodeValue) Then
                        outputRow = outputRow & "=""" & Format(barcodeValue, "0") & ""","
                    Else
                        outputRow = outputRow & "=""" & "0" & ""","
                    End If
                ElseIf key = "SeatName" Then
                    ' Remove all spaces from the concatenated seat name
                    inputCol = Split(colMap(key), ",")(0)
                    inputCol2 = ""
                    If UBound(Split(colMap(key), ",")) > 0 Then inputCol2 = Split(colMap(key), ",")(1)

                    Dim seatVal As String
                    seatVal = ""
                    If ColumnLetterToNumber(inputCol) > 0 Then seatVal = dataArray(i, ColumnLetterToNumber(inputCol))
                    If ColumnLetterToNumber(inputCol2) > 0 Then seatVal = seatVal & " " & dataArray(i, ColumnLetterToNumber(inputCol2))
                    seatVal = Replace(seatVal, " ", "")  ' remove all spaces
                    outputRow = outputRow & seatVal & ","
                Else
                    inputCol = Split(colMap(key), ",")(0)
                    inputCol2 = ""
                    If UBound(Split(colMap(key), ",")) > 0 Then inputCol2 = Split(colMap(key), ",")(1)
                    Dim colIndex As Long, colIndex2 As Long
                    colIndex = ColumnLetterToNumber(inputCol)
                    colIndex2 = ColumnLetterToNumber(inputCol2)
                    If colIndex > 0 Then outputRow = outputRow & dataArray(i, colIndex)
                    If colIndex2 > 0 Then outputRow = outputRow & " " & dataArray(i, colIndex2)
                    outputRow = outputRow & ","
                End If
            End If
        Next key
        outputRow = Left(outputRow, Len(outputRow) - 1)
        Print #fileNum, outputRow
    Next i

    Close #fileNum
    MsgBox "CSV file saved successfully to " & savePath, vbInformation
End Sub

' Convert column letter (A, B, C) to number
Function ColumnLetterToNumber(colLetter As String) As Long
    On Error Resume Next
    Dim result As Long
    result = Range(colLetter & "1").Column
    If Err.Number <> 0 Then
        ColumnLetterToNumber = 0
    Else
       ColumnLetterToNumber = result
    End If
    On Error GoTo 0
End Function

' Validate if input is a valid column letter
Function IsValidColumn(colLetter As String) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "^[A-Z]+$"
    regex.IgnoreCase = False
    regex.Global = False
    IsValidColumn = regex.Test(colLetter)
End Function

' Get the last column letter in the sheet
Function LastColumnLetter(ws As Worksheet) As String
    LastColumnLetter = Split(ws.Cells(1, ws.Columns.Count).End(xlToLeft).Address, "$")(1)
End Function