Read Text File and Copy Job Data

[PingJu]

Hello, I am trying to read a text (01202_WIP_DATA.txt) file with over 459,000 Rows of Data, The data is broken up into Project Numbers, with corresponding activity codes and costs. how can I have the User select the file to read, and then get data in VBA?
Then, On Worksheet("WIP") in Cells "A1" I will Manually insert a Job number (530004) that I want to pull data from the Txt file. (I will have roughly 40 of these down my spreadsheet) Row 38 will be my next project.
In Column "B4" thru "B30" , I will have Descriptions, and "C4" thru "C30" I will have Costs Codes: I want to pull the data for all of these cost Codes. Any Help would greatly be appreciated!

The TXT File Data:

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 1
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530004 01 - OFFICE PROJECT 1 PM1: 21781 Generic 1 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
199.00|INV UNDER $500 | | | | | | | | | 458| 458| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
EQUIPMENT :Subtotal | | | | | | | | 458| 458| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
245.00|Parts & Supplie| | | | | | | | | 52| 52| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC DUCT :Subtotal | | | | | | | | 52| 52| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
441.00|DDC Controls (C| | | | | | | | | 13,139| 13,139| 20,942|
464.00|Programming (Co| | | 40| | | | | | | | 2,480|
465.00|Commissioning | | | 172| | | | | | | | 8,600|
466.00|Training (Contr| | | 24| | | | | | | | 1,200|
468.00|Graphics | | | 40| | | | | | | | 2,000|
469.00|Supervision (Co| | 5.0 | 48| | | 64.43| | 322| 63| 385| 2,400|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SYS OPN/BAL :Subtotal | 5.0 | 324| | | 64.43| | 322| 13,202| 13,524| 37,622|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 324| | | 51.48| | 16,680| 20,942| 37,622| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 324| | | 51.48| | 16,680| 20,942| 37,622| 37,622|
| % Compl.| | | 1.5 % | | | | | 1.9 % | 63.0 % | 35.9 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
503.00|CONTROLS DESIGN| | 124.0 | 64| | | 56.59| | 7,018| 1,550| 8,568| 3,328|
520.00|PANEL ASSEMBLY | | 32.0 | 56| | | 53.77| | 1,721| 400| 2,121| 2,688|
599.00|Total Misc. Lab| | | | | | | | | | | 1,600|
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 2
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530004 01 - OFFICE PROJECT 1 PM1: 21781 Generic 1 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ENGINEERING :Subtotal | 156.0 | 120| | | 56.02| | 8,738| 1,950| 10,688| 7,616|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 120| | | 50.13| | 6,016| 1,600| 7,616| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 120| | | 50.13| | 6,016| 1,600| 7,616| 7,616|
| % Compl.| | | 130.0 % | | | | | 145.3 % | 121.9 % | 140.3 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
667.00|Freight & Trans| | | | | | | | | 112| 112| 6,785|
683.00|Miscellaneous | | | | | | | | | 557| 557| |
694.00|Sales / Use Tax| | | | | | | | | 1,297| 1,297| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
RENTAL/OTHER :Subtotal | | | | | | | | 1,966| 1,966| 6,785|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | 6,785| 6,785| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | 6,785| 6,785| 6,785|
| % Compl.| | | | | | | | | 29.0 % | 29.0 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
Total, Phase 1 | | | | | | | | | | | |
| Actual | 161.0 | 444| | | 56.28| | 9,061| 17,626| 26,687| 52,023|
| | | | | | | | | | | | |
| | | |<-------------- Budget --------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 444| | | 51.12| | 22,696| 29,327| 52,023| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 444| | | | | 22,696| 29,327| 52,023| |
| % Compl.| | | 36.3 % | | | | | 39.9 % | 60.1 % | 51.3 % | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 3
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
199.00|INV UNDER $500 | | | | | | | | | 2,595| 2,595| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
EQUIPMENT :Subtotal | | | | | | | | 2,595| 2,595| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
245.00|Parts & Supplie| | | | | | | | | -764| -764| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC DUCT :Subtotal | | | | | | | | -764| -764| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
441.00|DDC Controls (C| | | | | | | | | 99,271| 99,271| 109,854|
460.00|Install Control| | 2,948.0 | 995| | | 68.86| | 203,004| 36,916| 239,920| 79,600|
463.00|Controls TI (Co| | 4.0 | | | | 39.34| | 157| 50| 207| |
464.00|Programming (Co| | 87.5 | 88| | | 73.38| | 6,421| 1,094| 7,514| 7,040|
465.00|Commissioning | | 445.0 | 800| | | 50.30| | 22,384| 5,563| 27,947| 64,000|
466.00|Training (Contr| | | 8| | | | | | | | 640|
468.00|Graphics | 25.0| 168.0 | 80| | | 71.28| | 11,975| 2,100| 14,075| 6,400|
469.00|Supervision (Co| | 66.0 | 80| | | 81.42| | 5,373| 825| 6,198| 6,400|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SYS OPN/BAL :Subtotal 25.0| 3,718.5 | 2,051| | | 67.05| | 249,315| 145,818| 395,133| 273,934|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 1,920| | | 80.00| | 153,600| 95,000| 248,600| |
|APPROVED C/Os | | | 131| | | 80.00| | 10,480| 14,854| 25,334| |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 2,051| | | 80.00| | 164,080| 109,854| 273,934| 273,934|
| % Compl.| | | 181.3 % | | | | | 151.9 % | 132.7 % | 144.2 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
501.00|Design Engineer| | 4.0 | | | | 58.89| | 236| 50| 286| |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 4
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
503.00|CONTROLS DESIGN| | 16.0 | 80| | | 56.59| | 906| 200| 1,106| 6,400|
530.00|Project Enginee| 4.0| 43.0 | | | | 63.13| | 2,715| 538| 3,252| |
551.00|Project Manager| | | 40| | | | | | | | 4,000|
599.00|Total Misc. Lab| | | | | | | | | | | 3,000|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ENGINEERING :Subtotal 4.0| 63.0 | 120| | | 61.20| | 3,856| 788| 4,643| 13,400|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 120| | | 86.67| | 10,400| 3,000| 13,400| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 120| | | 86.67| | 10,400| 3,000| 13,400| 13,400|
| % Compl.| | | 52.5 % | | | | | 37.1 % | 26.3 % | 34.7 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
618.00|Consulting Engi| | | | | | | | | 30| 30| |
628.00|MISC SUBCONTRAC| | | | | | | | | 2,425| 2,425| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SUBCONTRACTS :Subtotal | | | | | | | | 2,455| 2,455| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
666.00|Rentals - Misce| | | | | | | | | 1,254| 1,254| |
667.00|Rentals, Freigh| | | | | | | | | 1,705| 1,705| |
683.00|Miscellaneous | | | | | | | | | 41| 41| |
694.00|Sales / Use Tax| | | | | | | | | 10,348| 10,348| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
RENTAL/OTHER :Subtotal | | | | | | | | 13,347| 13,347| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
709.00|Miscellaneous | | 10.0 | | | | 41.32| | 413| 125| 538| |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 5
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC OTHER :Subtotal | 10.0 | | | | 41.32| | 413| 125| 538| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
TOTAL |NCTC0220 | | | | | | | | | | | 142,801|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ADDTNL EST COST :Subtotal | | | | | | | | | | 142,801|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | 16,000| 126,801| 142,801| |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | 16,000| 126,801| 142,801| 142,801|
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
Total, Phase 1 | | | | | | | | | | | |
| Actual 29.0| 3,791.5 | 2,171| | | 66.88| | 253,584| 164,363| 417,947| 430,135|
| | | | | | | | | | | | |
| | | |<-------------- Budget --------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 2,040| | | 80.39| | 164,000| 98,000| 262,000| |
|APPROVED C/Os | | | 131| | | | | 10,480| 14,854| 25,334| |
|REVISIONS/PENDING | | | | | | | 16,000| 126,801| 142,801| |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 2,171| | | | | 190,480| 239,655| 430,135| |
| % Compl.| | | 174.6 % | | | | | 133.1 % | 68.6 % | 97.2 % | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 6
COST DIST B-4

 

[DanteAmor]

Try the following macro on a small txt file for you to review the results. I don't know how long it will take excel to read and process a file of more than 4500,000 lines.
The result will be on the "WIP" sheet according to the format you put in the image.
Perhaps the name of the "Project Name" and "Project Manager" data are not complete, since I do not know the pattern to identify where that data begins and ends.

Sub read_txt_files()
  Dim ff As Long, i As Long, j As Long, lngFileLen As Long
  Dim arrLines As Variant, a As Variant, b As Variant, c As Variant
  Dim pickFile As String, strWholeFile As String, jobPhase As String, job1 As String
  Dim dic As Object
  '
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select txt file"
    .Filters.Clear
    .Filters.Add "txt files", "*.txt"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If Not .Show Then Exit Sub
    pickFile = .SelectedItems.Item(1)
  End With
  '
  ff = FreeFile
  lngFileLen = FileLen(pickFile)
  strWholeFile = Space(lngFileLen)
  Open pickFile For Binary Access Read As #ff
    Get #ff, , strWholeFile
  Close #ff
  arrLines = Split(strWholeFile, vbCrLf)
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("WIP").Range("A1:O" & Sheets("WIP").Range("A" & Rows.Count).End(3).Row + 40).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
      dic("" & a(i, 1)) = i + 3
    End If
  Next
  '
  ReDim b(1 To UBound(arrLines), 1 To 4)
  For i = 0 To UBound(arrLines)
    If Left(arrLines(i), 10) = "Job/Phase:" Then
      j = j + 1
      jobPhase = arrLines(i)
      job1 = Split(jobPhase, " ")(1)
      b(j, 1) = job1
      b(j, 2) = Mid(jobPhase, 11 + Len(job1) + 2, InStr(13, jobPhase, ":") - (11 + Len(job1) + 2))
    ElseIf IsNumeric(Left(arrLines(i), 1)) Then
      j = j + 1
      b(j, 1) = job1
      b(j, 4) = arrLines(i)
    End If
  Next
  '
  For i = 1 To UBound(b)
    If b(i, 2) <> "" Then
      j = dic(b(i, 1)) - 3
      If a(j, 2) = "" Then
        a(j, 2) = Left(b(i, 2), InStrRev(b(i, 2), " ") - 1)
        a(j, 3) = Mid(b(i, 2), InStrRev(b(i, 2), " ") + 1)
      End If
    End If
    If b(i, 4) <> "" Then
      j = dic(b(i, 1))
      c = Split(b(i, 4), "|")
      
      a(j, 2) = c(1)
      a(j, 3) = c(0)
      a(j, 5) = c(2)
      a(j, 6) = c(3)
      a(j, 7) = c(4)
      a(j, 8) = c(5)
      a(j, 9) = c(6)
      a(j, 10) = c(7)
      a(j, 11) = c(8)
      a(j, 12) = c(9)
      a(j, 13) = c(10)
      a(j, 14) = c(11)
      a(j, 15) = c(12)
      
      dic(b(i, 1)) = dic(b(i, 1)) + 1
    End If
    
  Next
  Sheets("WIP").Range("A1").Resize(UBound(a), 15).Value = a
End Sub

 

[PingJu]

Dante, Thank you so much for the Help! Any thought on the below issue?

I am getting a Run-time error '9': Subscript out of range

a(i,1) = <Subscript Out Of Range>

Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("WIP").Range("A2:O" & Sheets("WIP").Range("A" & Rows.Count).End(xlUp).Row + 40).Value2
For i = 1 To UBound(a, 1)
If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
dic("" & a(i, 1)) = i + 3
End If
Next

debugging stops on the following line:

ReDim b(1 To UBound(arrLines), 1 To 4)

 

[DanteAmor]

You modified something in the macro, because I don't have any line with this statement: a(i, 1) =
Again, on which line do you have the error?
And what does the error message say?
You can put an image of what you have on your "WIP" sheet and an image of the data in your txt file.

 

[PingJu]

Hi Dante, sorry, after reading my question, it is a bit confusing. the error Message I get is Run-time error '9': Subscript out of range.

When I click on the Debug, it stops on the following line:

ReDim b(1 To UBound(arrLines), 1 To 4)

 

[DanteAmor]

But you did not put an image of your sheet nor did you put an image of the data from the txt file you are testing.
You can do the test again, use a txt file that has 3 projects. Those project numbers should be on your sheet.

I simplified the code a bit.
Make the small test with the following code:

Sub read_txt_files()
  Dim ff As Long, i As Long, j As Long, lngFileLen As Long
  Dim arrLines As Variant, a As Variant, c As Variant
  Dim pickFile As String, strWholeFile As String, jobPhase As String, job1 As String, job2 As String
  Dim dic As Object
  '
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select txt file"
    .Filters.Clear
    .Filters.Add "txt files", "*.txt"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If Not .Show Then Exit Sub
    pickFile = .SelectedItems.Item(1)
  End With
  '
  ff = FreeFile
  lngFileLen = FileLen(pickFile)
  strWholeFile = Space(lngFileLen)
  Open pickFile For Binary Access Read As #ff
    Get #ff, , strWholeFile
  Close #ff
  arrLines = Split(strWholeFile, vbCrLf)
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("WIP").Range("A1:O" & Sheets("WIP").Range("A" & Rows.Count).End(3).Row + 40).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
      dic("" & a(i, 1)) = i + 3
    End If
  Next
  '
  For i = 0 To UBound(arrLines)
    If Left(arrLines(i), 10) = "Job/Phase:" Then
      jobPhase = arrLines(i)
      job1 = Split(jobPhase, " ")(1)
      job2 = Mid(jobPhase, 11 + Len(job1) + 2, InStr(13, jobPhase, ":") - (11 + Len(job1) + 2))
      j = dic(job1) - 3
      If a(j, 2) = "" Then
        a(j, 2) = Left(job2, InStrRev(job2, " ") - 1)
        a(j, 3) = Mid(job2, InStrRev(job2, " ") + 1)
      End If
    ElseIf IsNumeric(Left(arrLines(i), 1)) Then
      j = dic(job1)
      c = Split(arrLines(i), "|")
      a(j, 2) = c(1)
      a(j, 3) = c(0)
      a(j, 5) = c(2)
      a(j, 6) = c(3)
      a(j, 7) = c(4)
      a(j, 8) = c(5)
      a(j, 9) = c(6)
      a(j, 10) = c(7)
      a(j, 11) = c(8)
      a(j, 12) = c(9)
      a(j, 13) = c(10)
      a(j, 14) = c(11)
      a(j, 15) = c(12)
      dic(job1) = dic(job1) + 1
    End If
  Next
  '
  Sheets("WIP").Range("A1").Resize(UBound(a), 15).Value = a
End Sub

 

[PingJu]

Donte, here are the images:

 

[DanteAmor]

Make the small test

 

[DanteAmor]

Time.
In your first example, the amounts were in the first position.
But now in your image the amounts have spaces on the left side
So the macro will not work.
You could upload some txt file to the cloud to really see how the data is.
An example with 5 projects is enough, don't upload a file of 400,000 records.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[PingJu]

Dante, all your help is greatly appreciated. my apologies for the Text file format that I dropped into the Thread.

Here is the Link:

REPORT SAMPLE.txt

Shared with Dropbox

www.dropbox.com

This is Sample Data of roughly 5 projects.