Direction for a VBA Logic loop to clean data

smd747 · Jan 10, 2014

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

Excel tables to the web >> Excel Jeanie HTML 4

hiker95 · Jan 11, 2014

smd747,

raw data
Stock Status Report (2)

According to the above quote, worksheet Stock Status Report (2) contains the raw data, and, the macro will remove the Total rows, and, you want the results to replace the raw data?????

Your reply #8 - I thought the original contained Total rows?

If so, please supply another screenshot with the totals. I need to see the actual raw data.

smd747 · Jan 12, 2014

hiker95 said:
smd747,

Your reply #8 - I thought the original contained Total rows?

If so, please supply another screenshot with the totals. I need to see the actual raw data.

Screen #8 is the shot of the raw data in the format it is dumped.
I then used the loop macro that you modified to removed "Total For Unit of Measure:"

Code:

Option Explicit
Sub ReorgData()
' hiker95, 01/11/2014, ME749822
Dim r As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("Stock Status Report")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 1 Step -1
    If Left(.Cells(r, 1), 5) = "Total" Then
      .Rows(r).Delete
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

The next step I am thinking is to loop again to move remaining data so I can work with it.The macro recorder gave me this to start the other loop

Code:

Sub MoveData()

    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Cut Destination:=ActiveCell.Offset(-1, 4).Range("A1")
    ActiveCell.Offset(0, 1).Range("A1:C1").Select
    ActiveCell.Offset(1, 0).Range("A1:C1").Cut Destination:=ActiveCell.Range( _
        "A1:C1")
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
End Sub

This will go down one cell in A1 and move the " Cmx Whs Zone Aisle Bay Level Pos Slot Location" to E column. Then take cell in B:D and move them up 1 row then go down and delete the empty row and then start over. The raw data is in #8

Thanks for taking the time to look at this

hiker95 · Jan 12, 2014

smd747,

1. In the raw data worksheet Stock Status Report (3) what would be the maximum last row ever used?

If the raw data on worksheet Stock Status Report (3) looks like this (not all rows are shown for brevity):

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

5

Total For Unit of Measure:

6

SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6

7

100 100 851 851 2 1 28 1 00002151951

EA

46

8

Total For Unit of Measure:

46

9

SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8

10

100 100 010 VENDOR RETURNS

EA

7

11

Total For Unit of Measure:

7

12

SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15

13

100 100 850 850 1 1 6 1 00001203349

EA

124

14

Total For Unit of Measure:

124

24

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

25

100 100 850 850 1 1 4 1 00001203347

EA

190

26

100 100 850 850 1 1 4 1 00004708459

EA

1

0

27

Total For Unit of Measure:

191

190

44

Stock Status Report (3)

2. What do you want the results to look like, the YELLOW cells, or the GREEN cells, or the BLUE cells?

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

100 100 851 851 2 1 45 1 00002152007

3

SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6

EA

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

100 100 010 VENDOR RETURNS

5

SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15

EA

124

100 100 850 850 1 1 6 1 00001203349

6

7

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

EA

190

100 100 850 850 1 1 4 1 00001203347

8

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

EA

1

0

100 100 850 850 1 1 4 1 00004708459

9

10

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

EA

191

190

100 100 850 850 1 1 4 1 00001203347 100 100 850 850 1 1 4 1 00004708459

11

12

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

EA

190

100 100 850 850 1 1 4 1 00001203347

13

Stock Status Report (3)_results

3. Or, some other variation? If so, then please supply another screenshot for just SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19.

hiker95 · Jan 12, 2014

smd747,

After seeing the second macro in your reply #12, see if below is what you are looking for.

Sample raw data in worksheet Stock Status Report (3):

Excel 2007

A

B

C

D

E

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

5

Total For Unit of Measure:

6

SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6

7

100 100 851 851 2 1 28 1 00002151951

EA

46

8

Total For Unit of Measure:

46

9

SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8

10

100 100 010 VENDOR RETURNS

EA

7

11

Total For Unit of Measure:

7

12

SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15

13

100 100 850 850 1 1 6 1 00001203349

EA

124

14

Total For Unit of Measure:

124

15

SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16

16

100 100 850 850 1 1 3 1 00001203346

EA

39

17

Total For Unit of Measure:

39

18

SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17

19

100 100 850 850 1 1 5 1 00001203348

EA

98

20

Total For Unit of Measure:

98

21

SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18

22

100 100 850 850 1 1 2 1 00001203269

EA

56

23

Total For Unit of Measure:

56

24

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

25

100 100 850 850 1 1 4 1 00001203347

EA

190

26

100 100 850 850 1 1 4 1 00004708459

EA

1

0

27

Total For Unit of Measure:

191

190

28

SVP410SH LEN 4X10 FT SRV WGT SH SOIL PP 20

29

100 100 850 850 1 1 1 1 00001203268

EA

165

30

Total For Unit of Measure:

165

31

SVP55SH LEN 5X5 FT SRV WGT SH SOIL PIP 21

32

100 100 850 850 1 1 19 1 00001203368

EA

28

33

Total For Unit of Measure:

28

34

SVP510SH LEN 5X10 FT SRV WGT SH SOIL PP 22

35

100 100 850 850 1 1 14 1 00001203362

EA

16

36

Total For Unit of Measure:

16

37

SVP65SH LEN 6X5 FT SRV WGT SH SOIL PIP 23

38

100 100 850 850 1 1 20 1 00001203369

EA

20

39

Total For Unit of Measure:

20

40

SVP610SH LEN 6X10 FT SRV WGT SH SOIL PP 24

41

100 100 850 850 1 1 15 1 00001203363

EA

27

42

100 100 850 850 1 1 21 1 00001203370

EA

2

43

Total For Unit of Measure:

2

44

Stock Status Report (3)

After the new macro in worksheet Stock Status Report (3):

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

100 100 851 851 2 1 45 1 00002152007

3

SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6

EA

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

100 100 010 VENDOR RETURNS

5

SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15

EA

124

100 100 850 850 1 1 6 1 00001203349

6

SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16

EA

39

100 100 850 850 1 1 3 1 00001203346

7

SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17

EA

98

100 100 850 850 1 1 5 1 00001203348

8

SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18

EA

56

100 100 850 850 1 1 2 1 00001203269

9

SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19

EA

190

100 100 850 850 1 1 4 1 00001203347

10

SVP410SH LEN 4X10 FT SRV WGT SH SOIL PP 20

EA

165

100 100 850 850 1 1 1 1 00001203268

11

SVP55SH LEN 5X5 FT SRV WGT SH SOIL PIP 21

EA

28

100 100 850 850 1 1 19 1 00001203368

12

SVP510SH LEN 5X10 FT SRV WGT SH SOIL PP 22

EA

16

100 100 850 850 1 1 14 1 00001203362

13

SVP65SH LEN 6X5 FT SRV WGT SH SOIL PIP 23

EA

20

100 100 850 850 1 1 20 1 00001203369

14

SVP610SH LEN 6X10 FT SRV WGT SH SOIL PP 24

EA

27

100 100 850 850 1 1 15 1 00001203363

15

Stock Status Report (3)

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).

Code:

Option Explicit
Sub ReorgDataV2()
' hiker95, 01/12/2014, ME749822
Dim r As Long, lr As Long, lr2 As Long
Dim Area As Range
Application.ScreenUpdating = False
Sheets("Stock Status Report (3)").Activate
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  If Left(Cells(r, 1), 5) = "Total" Then
    Rows(r).ClearContents
  End If
Next r
Rows(1).Delete
Cells(1, 1).Resize(, 5).Value = Array("Product", "Uom", "On Hand..", "Available", "Cmx Whs Zone Aisle Bay Level Pos Slot Location.......")
For Each Area In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    If .Rows.Count = 2 Then
      Range("B" & .Row).Resize(, 3).Value = Range("B" & .Row + 1).Resize(, 3).Value
      Range("B" & .Row + 1).Resize(, 3).ClearContents
      Range("E" & .Row).Value = Range("A" & .Row + 1).Value
      Range("A" & .Row + 1).ClearContents
    ElseIf .Rows.Count = 3 Then
      Range("B" & .Row).Resize(, 3).Value = Range("B" & .Row + 1).Resize(, 3).Value
      Range("B" & .Row + 1).Resize(2, 3).ClearContents
      Range("E" & .Row).Value = Range("A" & .Row + 1).Value
      Range("A" & .Row + 1).Resize(2).ClearContents
    End If
  End With
Next Area
On Error Resume Next
Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
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 ReorgDataV2 macro.

smd747 · Jan 12, 2014

That worked out perfect, exactly what I was looking for, now I can do text to columns in columns E to split the numbers I have to see if I can automate that part
I had to change one thing to get it to work "Sheets("Stock Status Report (3)").Activate to Sheets("Stock Status Report").Activate.
Thank you for you time and guidance and all your help with such a great solution. Thanks you Smd747

smd747 · Jan 12, 2014

I discovered one flaw with the code that when it move then 1 location for item it skips it like in this screen shot. I stepped through the code but do not see when I can correct that one item, one location works fine multiply does not work
Stock Status Report

	A	B	C	D	E
1	Product	Uom	On Hand..	Available	XXX
2	SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5	EA	6	XXX	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	XXX	XXX
5	XXX	XXX	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	XXX
8	XXX	XXX	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
10	SVP410SH LEN 4X10 FT SRV WGT SH SOIL PP 20	EA	165	165	100 100 850 850 1 1 1 1 00001203268
11	SVP55SH LEN 5X5 FT SRV WGT SH SOIL PIP 21	XXX	28	XXX	100 100 850 850 1 1 19 1 00001203368
12	SVP510SH LEN 5X10 FT SRV WGT SH SOIL PP 22	XXX	XXX	16	100 100 850 850 1 1 14 1 00001203362
13	SVP65SH LEN 6X5 FT SRV WGT SH SOIL PIP 23	EA	20	20	100 100 850 850 1 1 20 1 00001203369
14	SVP610SH LEN 6X10 FT SRV WGT SH SOIL PP 24	XXX	27	XXX	100 100 850 850 1 1 15 1 00001203363
15	SVP810SH LEN 8X10 FT SRV WGT SH SOIL PP 26	EA	3	3	100 100 850 850 1 1 16 1 00001203364
16	100 100 850 850 1 1 16 1 00001203364	EA	XXX	XXX
17	XXX	EA	3	3
18	100 100 850 850 1 1 16 1 00001203364	EA	XXX	XXX
19	100 100 850 850 1 1 16 1 00001203364	EA	3	XXX
20	100 100 850 850 1 1 16 1 00001203364	XXX	3	XXX
21	SVP105SH LEN 10X5 FT SRV WGT SH SOIL PI 27	XXX	XXX	3	100 100 850 850 1 1 22 1 00001203371

Excel tables to the web >> Excel Jeanie HTML 4

What do you think

hiker95 · Jan 12, 2014

smd747,

I have 159740 lines of data that look simular to this below

With that much raw data you should be using the ReorgDataV3 macro that uses two arrays in memory to process the data (and, there is no deleting Total rows, etc.), on worksheet Stock Status Report (3).

Your latest screenshot is of worksheet Stock Status Report????

I thought that the raw data, and, results, were on worksheet Stock Status Report (3)????

I do not understand.

I will have to see the raw data for your reply #16.

hiker95 · Jan 12, 2014

smd747,

In reference to your Private Message:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

smd747 · Jan 12, 2014

Hiker95

I am using ReorgDataV3 macro that uses two arrays in memory to process the data (and, there is no deleting Total rows, etc.), on worksheet Stock Status Report (3).

I just renamed Stock Status Report (3) to Stock status Report. I had two many test sheets trying to figure this out and learn from your code.

Your loops works fine except I noticed when I scrolled down that if there were multiply locations for a item it only moved the first one

then skipped over to the next singe.

Stock Status Report

A B C D E F
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 will move
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 will move
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 will move
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 will move
13 100 100 850 850 1 1 6 1 00001203350 EA 123 123 problem
14 100 100 850 850 1 1 6 1 00001203351 EA 111 111 problem
15 100 100 850 850 1 1 6 1 00001203352 EA 23 23 problem

Excel tables to the web >> Excel Jeanie HTML 4

#17 was result of running ReorgDataV3 macro and shows what did not move

smd747 · Jan 12, 2014

This screen shot should have looked like

Stock Status Report

	A	B	C	D	E	F
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		will move
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		will move
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		will move
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		will move
13	100 100 850 850 1 1 6 1 00001203350	EA	123	123		problem
14	100 100 850 850 1 1 6 1 00001203351	EA	111	111		problem
15	100 100 850 850 1 1 6 1 00001203352	EA	23	23		problem

Excel tables to the web >> Excel Jeanie HTML 4

Direction for a VBA Logic loop to clean data

smd747

Board Regular

hiker95

Well-known Member

Excel Facts

smd747

Board Regular

hiker95

Well-known Member

hiker95

Well-known Member

smd747

Board Regular

smd747

Board Regular

hiker95

Well-known Member

hiker95

Well-known Member

smd747

Board Regular

smd747

Board Regular

Similar threads

Forum statistics

Share this page

Direction for a VBA Logic loop to clean data

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

Well-known Member

Board Regular

Board Regular

Well-known Member

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock