Direction for a VBA Logic loop to clean data

S

smd747

Board Regular
Joined
Apr 24, 2011
Messages
214
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

ABCD
1Total For Unit of Measure:8383
2 XH3G 3" DUAL TITE GASKETS XH (75PK 342
3 100 100 521 502 4 700 14 1 00004519329EA6363
4Total For Unit of Measure:6363
5 XH4G 4" DUAL TITE GASKETS XH (60PK 343
6 100 100 500 503 2 4 3 1 00004236988EA6565
7Total For Unit of Measure:6565
8 XH5G 5" DUAL TITE GASKETS XH (30PKQ 344
9 100 100 621 602 10 600 13 1 00004598010EA4343
10Total For Unit of Measure:4343
11 XH8G 8" DUAL TITE GASKETS XH (20PK 346
12 100 100 500 503 12 1 4 1 00003603261EA2020
13 100 100 500 503 12 1 4 1 00003603261EA1010
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
I figured I do this in stages. Part one delete all the rows that have totals
Code: 
Sub PartOneDeleteTotals()

    ' Figure out where the last row is
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' loop through all rows from 2 to Final Row
    For i = FinalRow To 1 Step -1
        If Cells(i, 1) = "*Total*" Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i
    
    
    
End Sub

Then possibly loop down and move lines starting with 100 into column B
this way I get a table file. Then do a test to column split and convert the text to numbers

Does any one have any ideas for the best way??
 
Upvote 0
smd747,

When we normally insert and/or delete rows we start from the last row and go up.

Does your raw data have titles? If so, can we see them?


Sample raw data:


Excel 2007
ABCD
1Total For Unit of Measure:8383
2XH3G 3" DUAL TITE GASKETS XH (75PK 342
3100 100 521 502 4 700 14 1 00004519329EA6363
4Total For Unit of Measure:6363
5XH4G 4" DUAL TITE GASKETS XH (60PK 343
6100 100 500 503 2 4 3 1 00004236988EA6565
7Total For Unit of Measure:6565
8XH5G 5" DUAL TITE GASKETS XH (30PKQ 344
9100 100 621 602 10 600 13 1 00004598010EA4343
10Total For Unit of Measure:4343
11XH8G 8" DUAL TITE GASKETS XH (20PK 346
12100 100 500 503 12 1 4 1 00003603261EA2020
13100 100 500 503 12 1 4 1 00003603261EA1010
14
Sheet1


After the macro:


Excel 2007
ABCD
1XH3G 3" DUAL TITE GASKETS XH (75PK 342
2100 100 521 502 4 700 14 1 00004519329EA6363
3XH4G 4" DUAL TITE GASKETS XH (60PK 343
4100 100 500 503 2 4 3 1 00004236988EA6565
5XH5G 5" DUAL TITE GASKETS XH (30PKQ 344
6100 100 621 602 10 600 13 1 00004598010EA4343
7XH8G 8" DUAL TITE GASKETS XH (20PK 346
8100 100 500 503 12 1 4 1 00003603261EA2020
9100 100 500 503 12 1 4 1 00003603261EA1010
10
11
12
13
14
Sheet1


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 ReorgData()
' hiker95, 01/11/2014, ME749822
Dim r As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  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

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 ReorgData macro.


You have not given us a screenshot of the results.

Can we have a screenshot of what the results should look like (manually formatted by you for the results you are looking for)?
 
Upvote 0
the titles are Product.......... Description... Cmx Whs Zone Aisle Bay Level Pos Slot Location.........on hand……………Available
 
Upvote 0
hiker95 said:
smd747,

your loop suggestion worked wonderful



Code: 
Option Explicit
Sub ReorgData()
' hiker95, 01/11/2014, ME749822
Dim r As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  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

Here is what the result should look like
Stock Status Report (3)

ABCDE
1Product UomOn Hand..Available Cmx Whs Zone Aisle Bay Level Pos Slot Location.......
2 SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5 EA66 100 100 851 851 2 1 45 1 00002152007
3 SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6 EA4646 100 100 851 851 2 1 28 1 00002151951
4 SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8 EA77 100 100 010 VENDOR RETURNS
5

<colgroup><col style="width: 30px;"><col style="width: 430px;"><col style="width: 72px;"><col style="width: 140.67px;"><col style="width: 91.33px;"><col style="width: 320px;"></colgroup><tbody>
</tbody>


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Thanks for the help with direction
Click to expand...
 
Upvote 0
smd747,

Your latest screenshot, your reply #5, does not match the information in your first reply #1.

Please supply a separate screenshot of your actual raw data, and, a separate screenshot of the results.
 
Last edited:
Upvote 0
So after the first loop the data looks like this.
Stock Status Report

ABCD
1Product.......... Description................ Cmx Whs Zone Aisle Bay Level Pos Slot Location.........on hand……………AvailableItem...
2 Cmx Whs Zone Aisle Bay Level Pos Slot Location.......UomOn Hand..Available
3100 100 851 851 2 1 41 12152003EA 2 2
4
5 SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
6 100 100 851 851 2 1 45 1 00002152007 EA66
7 SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6
8 100 100 851 851 2 1 28 1 00002151951 EA4646
9 SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8
10 100 100 010 VENDOR RETURNS EA77
11 SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15
12 100 100 850 850 1 1 6 1 00001203349 EA124124
13 SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16
14 100 100 850 850 1 1 3 1 00001203346 EA3939
15 SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17
16 100 100 850 850 1 1 5 1 00001203348 EA9898
17 SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18
18 100 100 850 850 1 1 2 1 00001203269 EA5656
19 SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19
20 100 100 850 850 1 1 4 1 00001203347 EA190190
21 100 100 850 850 1 1 4 1 00004708459 EA10
22 SVP410SH LEN 4X10 FT SRV WGT SH SOIL PP 20
23 100 100 850 850 1 1 1 1 00001203268 EA165165
24 SVP55SH LEN 5X5 FT SRV WGT SH SOIL PIP 21
25 100 100 850 850 1 1 19 1 00001203368 EA2828
26 SVP510SH LEN 5X10 FT SRV WGT SH SOIL PP 22
27 100 100 850 850 1 1 14 1 00001203362 EA1616
28 SVP65SH LEN 6X5 FT SRV WGT SH SOIL PIP 23
29 100 100 850 850 1 1 20 1 00001203369 EA2020
30 SVP610SH LEN 6X10 FT SRV WGT SH SOIL PP 24
31 100 100 850 850 1 1 15 1 00001203363 EA2727
32 100 100 850 850 1 1 21 1 00001203370 EA22
33 SVP810SH LEN 8X10 FT SRV WGT SH SOIL PP 26
34 100 100 850 850 1 1 16 1 00001203364 EA33
35 SVP105SH LEN 10X5 FT SRV WGT SH SOIL PI 27
36 100 100 850 850 1 1 22 1 00001203371 EA33
37 SVP1010SH LEN 10X10 FT SRV WGT SH SOIL P 28
38 100 100 850 850 1 1 17 1 00001203366 EA1010
39 SVP12I10SH LEN 12X10 FT SRV WGT SH SOIL P 30
40 100 100 850 850 1 1 18 1 00001203367 EA33
41 SVP25DH LEN 2X5 FT SRV WGT DH SOIL PPE 33
42 100 100 850 850 1 1 9 1 00001203352 EA109109
43
 SVP35DH LEN 3X5 FT SRV WGT DH SOIL PIP 34
44 100 100 850 850 1 1 8 1 00001203351 EA112112
45 SVP45DH LEN 4X5 FT SRV WGT DH SOIL PIP 35
46 100 100 850 850 1 1 7 1 00001203350 EA7575
47 SVP55DH LEN 5X5 FT SRV WGT DH SOIL PIP 36
48 100 100 920 OUTSIDE STAGING EA11
49 100 100 850 850 1 1 23 1 00001203376 EA44
50 100 100 850 850 1 1 24 1 00001203377 EA77
51 100 100 851 851 2 1 33 1 00002151957 EA5353
52

<colgroup><col style="width: 30px;"><col style="width: 471.33px;"><col style="width: 72px;"><col style="width: 140.67px;"><col style="width: 91.33px;"></colgroup><tbody>
</tbody>


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8 no I need to figure how to run another loop to move data over and up
 
Upvote 0
Please supply a separate screenshot of your actual raw data, and, a separate screenshot of the results.
Click to expand...

raw data
Stock Status Report (2)

ABCDE
1Product.......... Description................ Cmx Whs Zone Aisle Bay Level Pos Slot Location.........on hand……………AvailableItem...
2 Cmx Whs Zone Aisle Bay Level Pos Slot Location.......UomOn Hand..Available
3 SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5
4 100 100 851 851 2 1 45 1 00002152007 EA66
5Total For Unit of Measure:66
6 SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6
7 100 100 851 851 2 1 28 1 00002151951 EA4646
8Total For Unit of Measure:4646
9 SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8
10 100 100 010 VENDOR RETURNS EA77
11Total For Unit of Measure:77
12 SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15
13 100 100 850 850 1 1 6 1 00001203349 EA124124
14Total For Unit of Measure:124124
15 SVP210SH LEN 2X10 FT SRV WGT SH SOIL PP 16
16 100 100 850 850 1 1 3 1 00001203346 EA3939
17Total For Unit of Measure:3939
18 SVP35SH LEN 3X5 FT SRV WGT SH SOIL PIP 17
19 100 100 850 850 1 1 5 1 00001203348 EA9898
20Total For Unit of Measure:9898
21 SVP310SH LEN 3X10 FT SRV WGT SH SOIL PP 18
22 100 100 850 850 1 1 2 1 00001203269 EA5656
23Total For Unit of Measure:5656
24 SVP45SH LEN 4X5 FT SRV WGT SH SOIL PIP 19
25 100 100 850 850 1 1 4 1 00001203347 EA190190
26 100 100 850 850 1 1 4 1 00004708459 EA10
27Total For Unit of Measure:191190
28 SVP410SH LEN 4X10 FT SRV WGT SH SOIL PP 20
29 100 100 850 850 1 1 1 1 00001203268 EA165165
30Total For Unit of Measure:165165
31 SVP55SH LEN 5X5 FT SRV WGT SH SOIL PIP 21
32 100 100 850 850 1 1 19 1 00001203368 EA2828
33Total For Unit of Measure:2828
34 SVP510SH LEN 5X10 FT SRV WGT SH SOIL PP 22
35 100 100 850 850 1 1 14 1 00001203362 EA1616
36Total For Unit of Measure:1616
37 SVP65SH LEN 6X5 FT SRV WGT SH SOIL PIP 23
38 100 100 850 850 1 1 20 1 00001203369 EA2020
39Total For Unit of Measure:2020
40 SVP610SH LEN 6X10 FT SRV WGT SH SOIL PP 24
41 100 100 850 850 1 1 15 1 00001203363 EA2727
42 100 100 850 850 1 1 21 1 00001203370 EA22
43Total For Unit of Measure:22

<colgroup><col style="width: 30px;"><col style="width: 471.33px;"><col style="width: 72px;"><col style="width: 140.67px;"><col style="width: 91.33px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
 
Upvote 0
Please supply a separate screenshot of your actual raw data, and, a separate screenshot of the results.
Click to expand...

format what I am looking to do (small sample)

Stock Status Report (3)

ABCDE
1Product UomOn Hand..Available Cmx Whs Zone Aisle Bay Level Pos Slot Location.......
2 SV3CO 3 SRV WGT CI CLEANOUT W/PLUG 5 EA66 100 100 851 851 2 1 45 1 00002152007
3 SV4CO 4 SRV WGT CI CLEANOUT W/PLUG 6 EA4646 100 100 851 851 2 1 28 1 00002151951
4 SV4CRLTTY2RS 4 SRV WGT CR LTTY W/2 RH SO CA 8 EA77 100 100 010 VENDOR RETURNS
5 SVP25SH LEN 2X5 FT SRV WGT SH SOIL PPE 15 EA124124 100 100 850 850 1 1 6 1 00001203349
6

<colgroup><col style="width: 30px;"><col style="width: 379.33px;"><col style="width: 72px;"><col style="width: 87.33px;"><col style="width: 84.67px;"><col style="width: 354px;"></colgroup><tbody>
</tbody>


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
 
Upvote 0
I recorded a macro to move the data up and over
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
Problem is when I have muti location numbers such as:

Stock Status Report (3)



SVP510SH LEN 5X10 FT SRV WGT SH SOIL PP 22
100 100 850 850 1 1 14 1 00001203362 EA 16 16
SVP65SH LEN 6X5 FT SRV WGT SH SOIL PIP 23
100 100 850 850 1 1 20 1 00001203369 EA 20 20
SVP610SH LEN 6X10 FT SRV WGT SH SOIL PP 24
100 100 850 850 1 1 15 1 00001203363 EA 27 27
100 100 850 850 1 1 21 1 00001203370 EA 2 2
100 100 850 850 1 1 9 1 00001203352 EA 109 109


Excel tables to the web >> Excel Jeanie HTML 4

need to automate with a loop

Thanks for help with a solution
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA to split data
Replies
1
Views
274
duggie33
D
willow1985
  • Solved
VBA copy set amount of rows into forms until end of data set
2
Replies
15
Views
233
willow1985
willow1985
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
278
madi1004
M
D
  • Question
Data in cell wrong way round ie "15.22=1|13.33=3" should be "1=15.22|3=13.33" - must finish with data in 1 cell
Replies
3
Views
303
Fluff
Fluff
A
  • Question
VBA Loop to clear contents of fiscal year rows in specific month columns
Replies
6
Views
500
kevin9999
K

Forum statistics

Threads
1,214,810
Messages
6,121,690
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top