Direction for a VBA Logic loop to clean data

[smd747]

I have 159740 lines of data that look simular to this below, item number the next line or several lines locations numbers.
I am think possibly some kind of a loop but looking for a starting direction. to put the data into a table file to work with. Any thoughts how to do this??

Thanks

Sheet1

	A	B	C	D
1	Total For Unit of Measure:		83	83
2	XH3G 3" DUAL TITE GASKETS XH (75PK 342
3	100 100 521 502 4 700 14 1 00004519329	EA	63	63
4	Total For Unit of Measure:		63	63
5	XH4G 4" DUAL TITE GASKETS XH (60PK 343
6	100 100 500 503 2 4 3 1 00004236988	EA	65	65
7	Total For Unit of Measure:		65	65
8	XH5G 5" DUAL TITE GASKETS XH (30PKQ 344
9	100 100 621 602 10 600 13 1 00004598010	EA	43	43
10	Total For Unit of Measure:		43	43
11	XH8G 8" DUAL TITE GASKETS XH (20PK 346
12	100 100 500 503 12 1 4 1 00003603261	EA	20	20
13	100 100 500 503 12 1 4 1 00003603261	EA	10	10
14

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 354px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"> </COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiker95]

smd747,

hiker95 thanks for a great solution and for your time and help educating me. Thank you smd747

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[smd747]

Works great. I had to add the text to column code from the macro record to finish it out
to separate by space. Thanks again much appreciated

Option Explicit
Sub ReorgDataStockStatusReportV3()
' hiker95, 01/12/2014, ME749822
Dim oa As Variant, na As Variant
Dim i As Long, ii As Long
Dim lr As Long, n As Long
Sheets("Stock Status Report").Activate
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
With Range("A1:D" & lr)
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
oa = Range("A1:D" & lr)
n = Application.CountIf(Columns(2), "EA")
ReDim na(1 To n, 1 To 5)
For i = 2 To UBound(oa, 1) - 1
  If oa(i, 2) = "" And oa(i + 1, 2) = "EA" Then
    ii = ii + 1
    na(ii, 1) = oa(i, 1)
    na(ii, 2) = oa(i + 1, 2)
    na(ii, 3) = oa(i + 1, 3)
    na(ii, 4) = oa(i + 1, 4)
    na(ii, 5) = oa(i + 1, 1)
  End If
Next i
Range("A1:D" & lr).ClearContents
Range("A1").Resize(, 5).Value = Array("Product", "Uom", "On Hand..", "Available", "Cmx Whs Zone Aisle Bay Level Pos Slot Location.......")
Range("A2").Resize(UBound(na, 1), UBound(na, 2)) = na
Columns.AutoFit
' added to split column E by space from macro recorder
 Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
       Range("E1:M1").Select
    Selection.Cut Destination:=Range("F1:N1")
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("E1").Select
Application.ScreenUpdating = True
End Sub

 

[hiker95]

smd747,

Nicely done.

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.