We have some SPC software that will export data in the format below, We need to clean it and import it into excel, knowing that the measurements and the number of items being measured will change.
": Height__0.0:"
"NOM (LSL, USL) = 8.8428 (8.7828, 8.9028)"
"Subgroup" 5
"Status" "OS"
"X" 8.8394
"R" 0.1547
"s" 0.0273
"S1" 8.8445
"S2" 8.8493
"S3" 8.8450
"S4" 8.8465
"S5" 8.8483
"S6" 8.8443
"S7" 8.8315
"S8" 8.8322
"Inspector" "kh"
"Machine" "a12"
"Sequence" 1-30
"Station" 71445091
"Material" " "
"Date/Time" " "
"Time" "10:23:02"
"Date" "03/18/2015"
": Bead__45.0:"
"NOM (LSL, USL) = 0.8970 (0.8890, 0.9050)"
"Subgroup" 5
"Status" ""
"X" 0.9013
"R" 0.0050
"s" 0.0012
"S1" 0.9011
"S2" 0.9005
"S3" 0.8991
"S4" 0.9014
"S5" 0.9011
"S6" 0.9017
"S7" 0.9022
"S8" 0.9019
"Inspector" "kh"
"Machine" "a12"
"Sequence" 1-30
"Station" 71445091
"Material" " "
"Date/Time" " "
"Time" "10:23:02"
"Date" "03/18/2015"
I need to build this data into an excel worksheet as so
Height__0.0 Bead__45.0
NOM 8.8428 NOM 0.8970
LSL 8.7828 LSL 0.8890
USL 8.9028 USL 0.9050
8.8445 0.9011
8.8493 0.9005
8.845 0.8991
8.8465 0.9014
etc... Any advice where to start?
Well if it's always in this exac format this code can produce the table you want, you must paste the original code on the A column and run the macro, it will create the table on the B and C column:
Sub test()
Dim c1 As Integer
Dim c2 As Integer
c1 = 0
c2 = 0
Do While c1 < 25
Cells(1, 2 + c2) = Mid(Cells(1 + c1, 1), 4, WorksheetFunction.Search(":", Range("A1"), 3) - 4)
Cells(2, 2 + c2) = "NOM " & Mid(Cells(2 + c1, 1), 19, 6)
Cells(3, 2 + c2) = "LSL " & Mid(Cells(2 + c1, 1), 27, 6)
Cells(4, 2 + c2) = "USL " & Mid(Cells(2 + c1, 1), 35, 6)
For i = 5 To 8
Cells(i, 2 + c2) = Mid(Cells(i + 3 + c1, 1), 9, 6)
Cells(i, 2 + c2).NumberFormat = "0.0000"
Next
c2 = c2 + 1
c1 = c1 + 24
Loop
End Sub
If you'd like to use the last numbers with "." as decimal separator just place this line
Cells(i, 2 + c2) = "'" & Mid(Cells(i + 3 + c1, 1), 9, 6)
in the place of this 2
Cells(i, 2 + c2) = Mid(Cells(i + 3 + c1, 1), 9, 6)
Cells(i, 2 + c2).NumberFormat = "0.0000"