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,

We now have two raw data worksheets, with two different structures:

Excel 2007
	A	B	C	D
1	Product.......... Description................ Cmx Whs Zone Aisle Bay Level Pos Slot Location.........on handAvailable	Item...
2	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......	Uom	On Hand..	Available
3	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
4	100 100 851 851 2 1 45 1 00002152007	EA	6	6
5	Total For Unit of Measure:		6	6
Stock Status Report (3)

Excel 2007
	A	B	C	D
1	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......	Uom	On Hand..	Available
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
3	100 100 851 851 2 1 45 1 00002152007	EA	6	6
4	Total For Unit of Measure:		6	6
Stock Status Report

Sample raw data on worksheet Stock Status Report:

Excel 2007
	A	B	C	D
1	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......	Uom	On Hand..	Available
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
3	100 100 851 851 2 1 45 1 00002152007	EA	6	6
4	Total For Unit of Measure:		6	6
5	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6
6	100 100 851 851 2 1 28 1 00002151951	EA	46	46
7	Total For Unit of Measure:		46	46
8	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8
9	100 100 010 VENDOR RETURNS	EA	7	7
10	Total For Unit of Measure:		7	7
11	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15
12	100 100 850 850 1 1 6 1 00001203349	EA	124	124
13	100 100 850 850 1 1 6 1 00001203350	EA	123	123
14	100 100 850 850 1 1 6 1 00001203351	EA	111	111
15	100 100 850 850 1 1 6 1 00001203352	EA	23	23
16
Stock Status Report

After the new macro designed for the Stock Status Report worksheet:

Excel 2007
	A	B	C	D	E
1	Product	Uom	On Hand..	Available	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5	EA	6	6	100 100 851 851 2 1 45 1 00002152007
3	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6	EA	46	46	100 100 851 851 2 1 28 1 00002151951
4	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8	EA	7	7	100 100 010 VENDOR RETURNS
5	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15	EA	124	124	100 100 850 850 1 1 6 1 00001203349
6
Stock Status Report

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataStockStatusReport()
' 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
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
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
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataStockStatusReport macro.

 

[smd747]

I tried the box.com and been attempting to get it to work for an hour. Signed up can't get the shared link to work.
I tried in skydrive try this link http://sdrv.ms/1ahIDNW

get a subscript out of range on "ReDim na(1 To n, 1 To 5)"

thanks again for working through this with me

 

[hiker95]

smd747,

I tried in skydrive try this link http://sdrv.ms/1ahIDNW

I am not able to download/open the workbook(s).

I was able to get the data for worksheet Stock Status Report.

 

[smd747]

[URL]https://skydrive.live.com/redir?resid=310DC7EA32853E!26840&authkey=!AEheI8sIvoC7c_g&ithint=folder%2c.xlsm[/URL]

I have it so you can download and edit

 

[smd747]

http://sdrv.ms/1dfCkiemaybe this

 

[hiker95]

smd747,

The data in column B that contains the EA's actually has a leading space, like this " EA".

Before I continue any further, please verify that the EA's have a leading space character.

 

[smd747]

yes it has a leading space. The spreadsheet is the data dump formatted as I get it

 

[hiker95]

smd747,

The data in column B that contains the EA's actually has a leading space, like this " EA".

The data in column B that contains the blank looking cells actually contains a space character, like this " ".


Sample raw data in worksheet Stock Status Report (not all of the 36,201 rows are shown for brevity):

Excel 2007
	A	B	C	D
1	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......	Uom	On Hand..	Available
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
3	100 100 851 851 2 1 45 1 00002152007	EA	6	6
4	Total For Unit of Measure:		6	6
5	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6
6	100 100 851 851 2 1 28 1 00002151951	EA	46	46
7	Total For Unit of Measure:		46	46
8	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8
9	100 100 010 VENDOR RETURNS	EA	7	7
10	Total For Unit of Measure:		7	7
11	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15
12	100 100 850 850 1 1 6 1 00001203349	EA	124	124
13	Total For Unit of Measure:		124	124
14	SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16
15	100 100 850 850 1 1 3 1 00001203346	EA	39	39
16	Total For Unit of Measure:		39	39
17	SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17
18	100 100 850 850 1 1 5 1 00001203348	EA	98	98
19	Total For Unit of Measure:		98	98
20	SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18
21	100 100 850 850 1 1 2 1 00001203269	EA	56	56
22	Total For Unit of Measure:		56	56
23	SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19
24	100 100 850 850 1 1 4 1 00001203347	EA	190	190
25	100 100 850 850 1 1 4 1 00004708459	EA	1	0
26	Total For Unit of Measure:		191	190
Stock Status Report

After the macro in worksheet Stock Status Report (not all of the 10,545 rows of results are shown for brevity):

Excel 2007
	A	B	C	D	E
1	Product	Uom	On Hand..	Available	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5	EA	6	6	100 100 851 851 2 1 45 1 00002152007
3	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6	EA	46	46	100 100 851 851 2 1 28 1 00002151951
4	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8	EA	7	7	100 100 010 VENDOR RETURNS
5	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15	EA	124	124	100 100 850 850 1 1 6 1 00001203349
6	SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16	EA	39	39	100 100 850 850 1 1 3 1 00001203346
7	SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17	EA	98	98	100 100 850 850 1 1 5 1 00001203348
8	SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18	EA	56	56	100 100 850 850 1 1 2 1 00001203269
9	SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19	EA	190	190	100 100 850 850 1 1 4 1 00001203347
Stock Status Report

The macro run time to produce the above screenshot was 0.188 seconds.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataStockStatusReportV2()
' 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
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
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
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataStockStatusReportV2 macro.


In the future I would suggest that you start with a screenshot of your actual raw data, and, supply a screenshot of what the results should look like (manually formatted by you). That way we probably can find a solution on the first go.

 

[smd747]

Wow, lighting fast and perfect. trying to understand and learn from your code. I had so many test sheets and workbooks trying to figure this out but your correct you get lost sometimes and I will
"In the future I would suggest that you start with a screenshot of your actual raw data, and, supply a screenshot of what the results should look like (manually formatted by you). That way we probably can find a solution on the first go. "

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

 

[hiker95]

smd747,

I just added a few lines of code to the macro to trim all the text data in the worksheet.

Sample raw data:

Excel 2007
	A	B	C	D
1	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......	Uom	On Hand..	Available
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
3	100 100 851 851 2 1 45 1 00002152007	EA	6	6
4	Total For Unit of Measure:		6	6
5	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6
6	100 100 851 851 2 1 28 1 00002151951	EA	46	46
7	Total For Unit of Measure:		46	46
8	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8
9	100 100 010 VENDOR RETURNS	EA	7	7
10	Total For Unit of Measure:		7	7
11	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15
12	100 100 850 850 1 1 6 1 00001203349	EA	124	124
13	Total For Unit of Measure:		124	124
14	SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16
15	100 100 850 850 1 1 3 1 00001203346	EA	39	39
16	Total For Unit of Measure:		39	39
17	SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17
18	100 100 850 850 1 1 5 1 00001203348	EA	98	98
19	Total For Unit of Measure:		98	98
20	SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18
21	100 100 850 850 1 1 2 1 00001203269	EA	56	56
22	Total For Unit of Measure:		56	56
23	SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19
24	100 100 850 850 1 1 4 1 00001203347	EA	190	190
25	100 100 850 850 1 1 4 1 00004708459	EA	1	0
26	Total For Unit of Measure:		191	190
Stock Status Report

At the beginning of the macro you would then see this:

Excel 2007
	A	B	C	D
1	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......	Uom	On Hand..	Available
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
3	100 100 851 851 2 1 45 1 00002152007	EA	6	6
4	Total For Unit of Measure:		6	6
5	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6
6	100 100 851 851 2 1 28 1 00002151951	EA	46	46
7	Total For Unit of Measure:		46	46
8	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8
9	100 100 010 VENDOR RETURNS	EA	7	7
10	Total For Unit of Measure:		7	7
11	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15
12	100 100 850 850 1 1 6 1 00001203349	EA	124	124
13	Total For Unit of Measure:		124	124
14	SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16
15	100 100 850 850 1 1 3 1 00001203346	EA	39	39
16	Total For Unit of Measure:		39	39
17	SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17
18	100 100 850 850 1 1 5 1 00001203348	EA	98	98
19	Total For Unit of Measure:		98	98
20	SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18
21	100 100 850 850 1 1 2 1 00001203269	EA	56	56
22	Total For Unit of Measure:		56	56
23	SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19
24	100 100 850 850 1 1 4 1 00001203347	EA	190	190
25	100 100 850 850 1 1 4 1 00004708459	EA	1	0
26	Total For Unit of Measure:		191	190
Stock Status Report

Then after the macro is finished you will see this:

Excel 2007
	A	B	C	D	E
1	Product	Uom	On Hand..	Available	Cmx Whs Zone Aisle Bay Level Pos Slot Location.......
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5	EA	6	6	100 100 851 851 2 1 45 1 00002152007
3	SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6	EA	46	46	100 100 851 851 2 1 28 1 00002151951
4	SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8	EA	7	7	100 100 010 VENDOR RETURNS
5	SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15	EA	124	124	100 100 850 850 1 1 6 1 00001203349
6	SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16	EA	39	39	100 100 850 850 1 1 3 1 00001203346
7	SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17	EA	98	98	100 100 850 850 1 1 5 1 00001203348
8	SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18	EA	56	56	100 100 850 850 1 1 2 1 00001203269
9	SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19	EA	190	190	100 100 850 850 1 1 4 1 00001203347
Stock Status Report

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

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
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataStockStatusReportV3 macro.