Isolating Specific Data

[mesolomo]

I have data that I'm copying from a PDF and pasting in an excel sheet. The data that I need is mostly in groups of three rows, but sometimes there will be two extra rows under each data group. The two extra lines are extremely similar to two of the lines of data I need, so I can't think of a way to isolate them with a filter, and each document is 15,000 rows long, so doing it manually doesn't seem to be an option.

One of the lines of data that I need always starts with a dollar sign, so my thought is I could cut those so there would be a blank cell, and then I would just need a way to select the two rows immediately above every blank cell, leaving the two data rows I don't want below every blank cell. Is this possible? How would I do it?

 

[Phox]

If I am understanding you correctly, your data is something like this:

WANTED
WANTED
$WANTED
TRASH
TRASH
WANTED
WANTED
$WANTED

Assuming that is correct, and the data is in Column A, you can use this code to remove the unwanted rows:

Sub RemoveTrash()

Dim i As Long

For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Not Left(Cells(i, 1).Value, 1) = "$" Then
        Rows(i).Delete
    Else
        i = i - 2
    End If
Next i

End Sub

 

[mesolomo]

I may have explained the data poorly: not all of the groups of 3 have the extra two lines, only about 1/5 of them. So it's more like:

Wanted
Wanted
$wanted
Wanted
Wanted
$Wanted
Wanted
Wanted
$Wanted
Trash
Trash
Wanted
Wanted
$Wanted

The only way I can think of that they would stand out to the program is that anytime that there are 4 rows between 2 rows beginning with $, I would like to get rid of the top 2 of the 4. Does this make sense? I hope I'm explaining it well.

 

[Phox]

Try out my code. It shouldn't matter where the trash lines are, it simply deleted everything except valid groups of three.

 

[mesolomo]

I will give it a shot. Thank you for your help!

I'm sorry I am so ignorant about this, but where do I plug in the code, and do I just copy and paste what you posted? Thanks!

 

[Phox]

Press Alt+F11 to open the Visual Basic Editor (VBE). Go to the Insert Menu -> Module. Paste the code in the large blank window. Close VBE and go to Tools Menu -> Macro -> Macros and run the macro named RemoveTrash.

 

[mesolomo]

I found where to enter the code, but i cannot find the tool menu to run the macro. I'm running 2007 Excel, if that makes a difference. The only "tool" option I can find is "data tools" under the data tab, and that doesn't have a macro option. Where should I look?

 

[mesolomo]

Wow! I found how to run the macro, and it looks like it worked! Thank you so much!

Since that worked so well, you might be able to help me find solutions to a couple of other things I thought were lost:

So, the three rows of data that I'm working with are:

Name
City, State Zip
$123.00 mm/dd/yy

The sheets I'm working with are thousands of rows long, with these three rows of data repeating for every person. I have two problems with them, however:

1. I ultimately need each of those pieces of information into a seperate cell, in separate columns. I've been transposing the data then doing "Text to Columns" to get it from three separate rows in Column A to one row in Columns A, B, C etc.

The problem with this is the names aren't always uniform. Sometimes there's Mr. or Mrs. in front of them, sometimes they have a middle initial, or an "esq." behind them, which completely messes up the "Text to Columns." Is there a better way to break them up into separate cells?


2. Secondly, not all of the names are individuals- some are businesses or charities. I want to remove all of these (and the two lines of data that go with each of them), so I only have individual people. Do you have any idea of how to make excel know the difference between, for example, "Mr. John Smith" and "Smith and Sons Realty"?

Thanks for the help you've already given me, and let me know if you've got any ideas!

 

[mesolomo]

I have one more question:

Once I have all of this data spread out into the different columns
(Last Name, First Name, City, State, Zip, Date, Amount), I need to compare it to another list. Basically, I've got one list of data configured in a similar way, and I want to identify any repeats between the two lists. I know that excel has a way to delete repeats, but what I need is a way to delete everything BUT repeats. Any ideas?

 

[Phox]

Here's the code for getting rows to columns:

Sub RowsToColumns()
    
    Columns("B:G").Insert
    For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
        If Left(Cells(i, 1).Value, 1) = "$" Then
            Cells(i, 2).Value = Cells(i - 2, 1).Value
            l = InStr(1, Cells(i - 1, 1).Value, ",") - 1
            Cells(i, 3).Value = Left(Cells(i - 1, 1).Value, l)
            Cells(i, 4).Value = Mid(Cells(i - 1, 1).Value, l + 3, 2)
            Cells(i, 5).Value = Right(Cells(i - 1, 1).Value, Len(Cells(i - 1, 1).Value) - l - 8)
            strMonth = Mid(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 7, 2)
            strDay = Mid(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 4, 2)
            strYear = Right(Cells(i, 1).Value, 2)
            Cells(i, 6).Value = DateSerial(2000 + strYear, strMonth, strDay)
            Cells(i, 7).Value = Left(Cells(i, 1).Value, InStr(1, Cells(i, 1).Value, " ") - 1)
        End If
    Next i
    Columns(1).Delete
    Range("A1", Range("A" & Rows.Count).End(xlUp)).EntireRow.Sort Key1:=Range("A1")

End Sub

As for the name issues, you're kinda on your own. The repeats can be found, but that relies on the fact that the names will be exactly the same on both lists.