Data consolidation from Multiple CSV files and then Split data into number of files

Can you upload a sample output file showing us how you want the output?

Try this code

Option Explicit

Sub transpose_data()
Dim i As Long, lrow As Long, lcol As Long
Dim sname As String

With Worksheets(1)
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 1 To lrow
        sname = .Range("A" & i + 1).Value
        If Not Evaluate("ISREF('" & sname & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sname
        End If
        .Range("A" & i & ":BLQ" & i).Copy
        lcol = Worksheets(sname).Range("IV2").End(xlToLeft).Column
        Worksheets(sname).Cells(2, lcol + 1).PasteSpecial , Paste:=xlPasteValues, Transpose:=True
        .Range("A" & i + 1 & ":BLQ" & i + 1).Copy
        lcol = Worksheets(sname).Range("IV2").End(xlToLeft).Column
        Worksheets(sname).Cells(2, lcol + 1).PasteSpecial , Paste:=xlPasteValues, Transpose:=True
        i = i + 1
    Next i
End With

For i = 2 To Worksheets.Count
    With Worksheets(i)
        .Columns(1).Delete
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("E2:E" & lrow).FormulaR1C1 = "=IF(RC[-3]=RC[-1],""Pass"",""Fail"")"
    End With
Next i
End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

If you check this part of the code -

For i = 2 To Worksheets.Count
    With Worksheets(i)
        .Columns(1).Delete
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("E2:E" & lrow).FormulaR1C1 = "=IF(RC[-3]=RC[-1],""Pass"",""Fail"")"
    End With
Next i

I have already incorporated the pass and fail formulae. Which part of it is not working?

Can you attach a sample file which you are running it on? I ran it on your earlier sample file and it works.

You have put the code in "ThisWorkbook". You need to put the code in a blank module.

There is some print character in row 49 which was being considered as the lastrow in your file and since it was turning up as blank, the macro was giving an error and not proceeding with the calculation part of the macro.

Change this line in your code from

For i = 1 To lrow

to

For i = 1 To lrow - 1

And it will work.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

I will do it for you this time.