Delete Row Prior to certain text

[jemelton]

I know VERY LITTLE about excel, but have a major problem.

I have a database that has over 70k names and occupations. Let me give an example.

Row 1 Joe Smith name adress
Row 2 carpenter
Row 3 John Smith name address
Row 4 plummer
Row 5 Tom Smith name address
Row 6 doctor
Row 7 Ron Smith name address
Row 8 Teacher
Row 9 Tim Smith name address
Row 10 Pilot



Ok, here is my question....I ONLY want to display doctors and plumbers.

If I alphabetically sort then it puts them all in alphabetical order (no correlation between name and occupation) and I have a list of alphabetical names and them when I get to the "do" I have the work doctor 1,000 times in a row.

Is there a way to delete all rows before teachers and carpenters, in other words sort in groups of 2 rows.

The only way that I am doing it now is manually going through the whole spreadsheet, searching for carpenters and teachers and manually deleting the entire row it (their name and address)


Thanks in advance, this has me bummed out!

 

[ret44]

Without the exact layout, this might need some tweaking.

In the same row as the name, use the formula:

=OFFSET(A1,1,0)

If the occupation is in the A column, you'll now have it in the same row with the name. After this, you can just filter for rows with the words "Doctor" or "Plumber"

 

[John Davis]

I know VERY LITTLE about excel, but have a major problem.

I have a database that has over 70k names and occupations. Let me give an example.

Row 1 Joe Smith name adress
Row 2 carpenter
Row 3 John Smith name address
Row 4 plummer
Row 5 Tom Smith name address
Row 6 doctor
Row 7 Ron Smith name address
Row 8 Teacher
Row 9 Tim Smith name address
Row 10 Pilot



Ok, here is my question....I ONLY want to display doctors and plumbers.

If I alphabetically sort then it puts them all in alphabetical order (no correlation between name and occupation) and I have a list of alphabetical names and them when I get to the "do" I have the work doctor 1,000 times in a row.

Is there a way to delete all rows before teachers and carpenters, in other words sort in groups of 2 rows.

The only way that I am doing it now is manually going through the whole spreadsheet, searching for carpenters and teachers and manually deleting the entire row it (their name and address)


Thanks in advance, this has me bummed out!

Does this help in anyway? This assumes Sheet1 is the active sheet and sheet2 is a helper sheet.

Sub jelmelton()
'
'
Dim lr As Long
Dim cl As Range
'
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
'
  For Each cl In Range("A1:A" & lr)

        If cl.Value = "doctor" Or cl.Value = "plummer" Then

                cl.Offset(-1, 0).Resize(2, 1).EntireRow.Hidden = False

         Else
                cl.EntireRow.Hidden = True

         End If

  Next

Range("A1:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
Cells.EntireRow.Hidden = False
Cells.EntireRow.Clear
Sheets("Sheet2").Range("A1").CurrentRegion.Cut Sheets("Sheet1").Range("A1")
End Sub

 

[jemelton]

This is ALL greek to me......I would be willing to pay someone to sort this for me, any takers?

 

[crook_101]

Hi,

If i give you step by step instructions are you willing to have a go??

Cheers,
Ian

 

[jemelton]

Yes, of course, but it has to be step by step and VERY SIMPLIFIED

this would literally save me hundreds of hours

 

[crook_101]

OK - give me a couple of minutes and I will start the process

 

[jemelton]

there is a "master file" that I downloaded that is in Acess (because the file is so large) but was going to cut and paste small sections into excel

 

[crook_101]

does your data look like this??

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Joe Smith name adress</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>carpenter</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>John Smith name address</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>plummer</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Tom Smith name address</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>doctor</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Ron Smith name address</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Teacher</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>Tim Smith name address</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>Pilot</TD></TR></TBODY></TABLE>

Sheet2​

 

[jemelton]

Yes, similar, but sometimes there are 3 rows of different jobs for the same person. I am attaching a screenshot of the access file