Solved: How to clean up data

[JohnnyBravo]

These numbers were pasted from a PDF file which explains the odd square symbols in some of the rows.

My question is how can go from this:

http://aycu30.webshots.com/image/20509/2002979559202605058_rs.jpg



To this:

http://aycu37.webshots.com/image/18196/2002990050037862700_rs.jpg

There are a several hundred rows so to this by hand would take a while. Also starting with Cell A1, I'd like to have the alphabet letters inserted after every 2 blank rows (as in my 2nd screenshot above).

I don't even know what in the world those funny symbols are. I tried to do a Ctrl + H and Excel couldn't even process them. :-(

[lucas]

I think they are vbNewLine but I'm not sure since it's pasted data...If they are this should remove them(the squares)...it's going to look at sheet 1 so if it's different you will have to change the code.
Sub ReplaceIt2()
Sheets("Sheet1").Select
Cells.Replace What:=vbNewLine, Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True
Sheets("Sheet1").Select
End Sub

[JohnnyBravo]

Lucas, I just tried running your code on my worksheet and absolutely nothing happened. Zilch...nada...

I thought maybe the fact the data was pasted from a PDF file might have corrupted somthing in the file, so I clicked on the entire sheet, formatted the cells into numbers (with no decimal places). Then I re-ran your code. Again - absolutely nothing. What next? :-(

[JohnnyBravo]

I just re-read your post above. These lines with the strange squares (as well as the blank rows) were not created in VBA. I see your code above checks for vbNewLines.....? Is that checking for rows inserted with VBA automation? If so, it might account for why your code has no affect on my worksheet.

[mikerickson]

Put =CLEAN(A1) in B1 and fill down.
If you like what you see, you can PasteValues it.

[JohnnyBravo]

Thanks mikerickson, but I'm not sure that's going to work for my situation. It does indeed "clean" out the junk from the cells but when I click on Col.B. and use the Text to Columns feature, I get this:

http://www.pcqanda.com/dc/user_files2/19844.jpg


That's a problem. Because what I'm trying to achieve is to separate each number into it's own cell. You see the value in Cell A2 above? I want that string of numbers 14, 20, 43, 53, 63 separated into each cell horizontally across columns B thru F.

Not only that, but the clean function doesn't address the problem of deleting the extra rows each set of numbers. I want only 1 blank row in between those set of numbers as shown in my 2nd screenshot above. I also need the letters A thru Z inserted as I have shown above. Thanks.

[Charlize]

What about using this kind of workflow :
1. check with split function if value of first item is numeric (sepearated with space)
2. if not, remove whole row
3. since every set of numbers has 5 rows and 3 blocs of rows you can reformat the rows after the cleanup

if you could post a result op the paste action in a sheet, we could try something.

[JohnnyBravo]

Actually, I think mikerickson had a good solution. What I ended up doing was selecting column B and chose "Paste Special". I pasted only the value into Col.C.

Then I chose Col. C. and did the Text to Columns thing and chose Cell B2 as my target. Sort of bass ackwards...but hey...whatever gets the job done.

I'm half way there, now if I can only figure out to get rid of the extra blank rows to make it look like my 2nd screenshot.

[rbrhodes]

Hi JB,

These will clean & trim then group as 5 with "alpha" headers. Paste the whole thing into a standard module and run "doIt".


Option Base 1
Option Explicit
Sub doIt()
Application.ScreenUpdating = False
McKlean
GroupOf5
Application.ScreenUpdating = True

End Sub
Sub McKlean()
Dim cvalue
Dim i As Long
Dim lastrow As Long
'get end of data
lastrow = Range("A65536").End(xlUp).Row
'do all cells
For i = lastrow To 1 Step -1
'get value from cell
cvalue = Cells(i, 1)
'clean and remove leading & trailing spaces
With Application.WorksheetFunction
cvalue = .Clean(cvalue)
cvalue = .Trim(cvalue)
End With
If cvalue <> "" Then
'put cleaned value back in cell
Cells(i, 1) = cvalue
Else
'delete row
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub
Sub GroupOf5()
Dim i As Long
Dim j As Long
Dim gCount As Long
Dim lastrow As Long
Dim alpha As Variant
'build list for headers
alpha = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J" _
, "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W" _
, "X", "Y", "Z")
'last row of data
lastrow = Range("A65536").End(xlUp).Row
'count of groups
If lastrow Mod 5 = 0 Then
gCount = (lastrow / 5) - 1
Else
gCount = lastrow / 5
End If
'first row blank
Cells(1, 1).EntireRow.Insert
'header
Cells(1, 1) = alpha(1)
'init
j = 7

'groups of 5
For i = 1 To gCount
'spacer row
Cells(j, 1).EntireRow.Insert
'header
Cells(j, 1) = alpha(i + 1)
'incr
j = j + 6
Next i
End Sub

[JohnnyBravo]

rbrhodes, thank you so much for your work. When I run your routine, the code breaks here as shown:
http://www.pcqanda.com/dc/user_files2/19845.jpg


I noticed 2 problems right away. One is that it finds & deletes every blank row. I need exactly 1 blank row between each group of numbers. Please reference my 2nd screenshot (first posting above).

Another thing I noticed is that your routine inserts the alphabet letters into every blank row. I need the alphabet letters into every 3rd blank row. This 2nd problem is not a big deal, but 1st problem is definitely a big problem as I can't continue my work in that form.

I will upload a my Excel file so you can see what it looks like. Thanks for your efforts.

[rbrhodes]

Hi,

More than 26 rows I allowed for in the "alpha" array. That would provide 26 groups of 5.

I've doubled it to 52 and you only need one every three rows so you should be good for a bit. 52 *3 = 156 groups

If you exceed that you'll get a msgbox. Just add more letters to the array in sub "GroupsOf5" if you think you will need more.

Also leaves blank rows as requested and does Column to rows...etc

Cheers,

dr

[Charlize]

This will do the cleanup and add the letters. Working on the division of the 15 numbers. Will post later when more time. --- added the routine for the dividing by groups of 5.Option Explicit
Option Base 1
Sub clean_up()
Dim lrow As Long
Dim cellvalue
Dim vloop As Long
Dim counter As Long
Dim alphacounter As Long
Dim alpha
alpha = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", _
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", _
"U", "V", "W", "X", "Y", "Z")
lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
For vloop = lrow To 1 Step -1
cellvalue = ActiveSheet.Cells(vloop, 1).Value
With Application.WorksheetFunction
cellvalue = .Clean(cellvalue)
cellvalue = .Trim(cellvalue)
End With
If cellvalue <> "" Then
ActiveSheet.Cells(vloop, 1).Value = cellvalue
counter = counter + 1
If counter = 15 Then
ActiveSheet.Cells(vloop, 1).EntireRow.Insert
counter = 0
End If
Else
ActiveSheet.Cells(vloop, 1).EntireRow.Delete
End If
Next vloop
lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
alphacounter = 0
For vloop = 1 To lrow
If Range("A" & vloop).Value = "" And vloop < lrow Then
alphacounter = alphacounter + 1
If alphacounter > 26 Then
alphacounter = 1
End If
Range("A" & vloop).Value = alpha(alphacounter)
End If
Next vloop
'Added the routine for the dividing by 5 rows
counter = 1
For vloop = lrow To 1 Step -1
If vloop > 1 Then
If Len(Trim(Range("A" & vloop - 1).Value)) = 1 Then
counter = 0
ElseIf counter = 5 Then
ActiveSheet.Cells(vloop, 1).EntireRow.Insert
counter = 1
Else
counter = counter + 1
End If
End If
Next vloop
End Sub

[JohnnyBravo]

I want to thank rbrhodes and everyone who helped me with this excel problem. I'v been pretty busy past several days but I wanted to come back and let you know that I'm grateful for your help. This will certainly save me a lot of time.