Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo
------------------------------------------------------------------------
mimmo's Profile: http://www.excelforum.com/member.php...o&userid=26724
View this thread: http://www.excelforum.com/showthread...hreadid=399784

Hi mimmo

Can you not bring all the data into Excel (either into a new workbook or new
worksheet within the current workbook).

Then perhaps you could try this to select the relevant data......

Sub test()
Dim r As Range, r1 As Range, c As Range
With ActiveSheet
Set r = .Range(.Range("A1"), Range("A" & Rows.Count).End(xlUp))
r.Replace What:="location", Replacement:="=2/0", LookAt:=xlWhole
Set r = .Columns("A:A").SpecialCells(xlCellTypeFormulas, 23)
r = "location"
Set r1 = Nothing
For Each c In r
If c.Offset(1, 0) = "depth" Then
If r1 Is Nothing Then
Set r1 = .Range(c, c.Offset(1, 0))
Else
Set r1 = Union(r1, .Range(c, c.Offset(1, 0)))
End If
End If
Next c
If Not r1 Is Nothing Then r1.EntireRow.Select
End With
End Sub
--


XL2003
Regards

William



"mimmo" wrote in
message ...

Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo
------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread: http://www.excelforum.com/showthread...hreadid=399784

assume the location/depth information in column A starting in A1

in b1 put in the formula
=if(And(A1="location",A2="depth"),"Keep",#N/A)

then drag fill down the column

now select the column and do

Edit=Goto=special and select Formulas and Errors

This should select all the rows to delete. Do Edit=Delete and answer
EntireRow.

Now you can delete this dummy column.
--
Regards,
Tom Ogilvy
"mimmo" wrote in
message ...

Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo
------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread: http://www.excelforum.com/showthread...hreadid=399784

mimmo wrote:
Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


Minimally tested; change 16 as required

Sub testIt3a()
Set rng = Range("a1:a16")
For i = rng.Rows.Count To 1 Step -1
If Not rng(i) = "depth" Then
rng(i).EntireRow.Delete
ElseIf Not rng(i - 1) = "location" Then
rng(i).EntireRow.Delete
Else
i = i - 1
End If
Next
End Sub

Alan Beban

Mimmo,

Here's a non-code way. In a helper column (adjacent to the table, not
separated by any empty columns) put this formula (this one's for row 2):

=IF(AND(A2="Location",A3="Depth"),1,IF(AND(A2="Dep th",A1="Location"),1,0))

Now use AutoFilter on the helper column, filtering for 0. Select the
filtered rows (drag through the row headings at left). Delete the rows
(Ctrl -). Now set the Autofilter back to All. The helper column will have
some ref errors, but you don't need it any more. Delete it by selecting it
(click in the column header at top), and press Ctrl minus.

If you want a macro:

Sub RemoveRows()
Dim TestCell As Range
Set TestCell = Range("A2")
Do
If Not (LCase(TestCell) = "location" And LCase(TestCell.Offset(1, 0)) =
"depth") Then
Set TestCell = TestCell.Offset(1, 0)
TestCell.Offset(-1, 0).EntireRow.Delete
Set TestCell = TestCell.Offset(2, 0)

Else
Set TestCell = TestCell.Offset(2, 0)
End If
Loop While TestCell < ""
End Sub

--
Earl Kiosterud
www.smokeylake.com

"mimmo" wrote in
message ...

Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo
------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread: http://www.excelforum.com/showthread...hreadid=399784

Tom,

Your formula marks the Location rows that have a succeeding Depth row, but
OP needs it to also mark the succeeding Depth row.
--
Earl Kiosterud
www.smokeylake.com

"Tom Ogilvy" wrote in message
...
assume the location/depth information in column A starting in A1

in b1 put in the formula
=if(And(A1="location",A2="depth"),"Keep",#N/A)

then drag fill down the column

now select the column and do

Edit=Goto=special and select Formulas and Errors

This should select all the rows to delete. Do Edit=Delete and answer
EntireRow.

Now you can delete this dummy column.
--
Regards,
Tom Ogilvy
"mimmo" wrote in
message ...

Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo
------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread:
http://www.excelforum.com/showthread...hreadid=399784

Mimmo,

Tom Ogilvie's use of #N/A is better, to select the unwanted rows, because
you don't have to set up an Autofilter, with its requisite header row. But
I don't think his formula identifies both the rows in a pair you want. I'll
borrow from his idea:

Insert a row at the top, so your first record starts in row 2.
Put this in a helper column in row 2, and copy down with the Fill Handle:
=IF(AND(A2="Location",A3="Depth"),1,IF(AND(A2="Dep th",A1="Location"),1,#N/A))
Use Tom's method of selecting the #N/A rows:
Select the helper column
Edit=Goto=special and select Formulas and Errors

This should select all the rows to delete. Do Edit=Delete and answer
EntireRow.

--
Earl Kiosterud
www.smokeylake.com

"Earl Kiosterud" wrote in message
...
Mimmo,

Here's a non-code way. In a helper column (adjacent to the table, not
separated by any empty columns) put this formula (this one's for row 2):

=IF(AND(A2="Location",A3="Depth"),1,IF(AND(A2="Dep th",A1="Location"),1,0))

Now use AutoFilter on the helper column, filtering for 0. Select the
filtered rows (drag through the row headings at left). Delete the rows
(Ctrl -). Now set the Autofilter back to All. The helper column will
have some ref errors, but you don't need it any more. Delete it by
selecting it (click in the column header at top), and press Ctrl minus.

If you want a macro:

Sub RemoveRows()
Dim TestCell As Range
Set TestCell = Range("A2")
Do
If Not (LCase(TestCell) = "location" And LCase(TestCell.Offset(1, 0)) =
"depth") Then
Set TestCell = TestCell.Offset(1, 0)
TestCell.Offset(-1, 0).EntireRow.Delete
Set TestCell = TestCell.Offset(2, 0)

Else
Set TestCell = TestCell.Offset(2, 0)
End If
Loop While TestCell < ""
End Sub

--
Earl Kiosterud
www.smokeylake.com

"mimmo" wrote in
message ...

Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo
------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread:
http://www.excelforum.com/showthread...hreadid=399784

Your right, thanks for the heads up.

here is the correction

=if(OR(And(A1="location",A2="depth"),A1="Depth")," Keep",#N/A)

This assumes you will never have two sequential rows with depth.

--
Regards,
Tom Ogilvy


"Earl Kiosterud" wrote in message
...
Tom,

Your formula marks the Location rows that have a succeeding Depth row, but
OP needs it to also mark the succeeding Depth row.
--
Earl Kiosterud
www.smokeylake.com

"Tom Ogilvy" wrote in message
...
assume the location/depth information in column A starting in A1

in b1 put in the formula
=if(And(A1="location",A2="depth"),"Keep",#N/A)

then drag fill down the column

now select the column and do

Edit=Goto=special and select Formulas and Errors

This should select all the rows to delete. Do Edit=Delete and answer
EntireRow.

Now you can delete this dummy column.
--
Regards,
Tom Ogilvy
"mimmo" wrote in
message ...

Hi: we just started using a new system where we capture location and
depth data from a sounder, for making depth maps of lakes.
Basically the data streams continuously into a text file which we can
easily import into Excel, but we have to select certain rows first.
Each row of data has an identifier in the first column that tells us if
it's depth or location data. The problem is that there is more location
data then there is depth, so here's what we need to do:

every time a row with location data is followed by a row of depth data,
then we want to keep both of those rows

every other combination that is different from above, delete the rows

so for example, if these were the rows (row number shown):

1 location
2 location
3 location
4 depth
5 location
6 location
7 depth

then the rows we want to keep are 3,4,6 and 7

sorry for the lengthy post, this is my first, I just thought it's a
unique problem and hopefully some of you will find it interesting and
provide a solution

thanks


--
mimmo

------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread:
http://www.excelforum.com/showthread...hreadid=399784

thanks very much for all the suggestions, I will try them asap and wil
be sure to learn something in the proces

--
mimm
-----------------------------------------------------------------------
mimmo's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=39978

oops, one last question if you don't mind...
the formula worked great now I end up with something that looks lik
this:

location number number
depth number
location number number
depth number
etc etc


is it possible to select each depth and number, cut and paste them s
that I end up with:

location number number depth number
location number number depth number
etc.

in other words each row has both location and depth data

hope I explained this well enough..
thanks in advanc

--
mimm
-----------------------------------------------------------------------
mimmo's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=39978

Tom Ogilvy wrote:
Your right, thanks for the heads up.

here is the correction

=if(OR(And(A1="location",A2="depth"),A1="Depth")," Keep",#N/A)

This assumes you will never have two sequential rows with depth.

And that A1 does not contain "depth"

Alan Beban

Alan Beban wrote:
Tom Ogilvy wrote:

Your right, thanks for the heads up.

here is the correction

=if(OR(And(A1="location",A2="depth"),A1="Depth")," Keep",#N/A)

This assumes you will never have two sequential rows with depth.

And that A1 does not contain "depth"

Alan Beban

The formula I previously posted also assumes that A1 does not contain
"depth". Try instead

Sub testIt3a()
Set rng = Range("a1:a17")
For i = rng.Rows.Count To 1 Step -1
If i = 1 And rng(i) = "depth" Then rng(i).EntireRow.Delete: Exit For
If Not rng(i) = "depth" Then
rng(i).EntireRow.Delete
ElseIf Not rng(i - 1) = "location" Then
rng(i).EntireRow.Delete
Else
i = i - 1
End If
Next
End Sub

Alan Beban

Alan,

If A1 contained "Depth" it would be discarded anyway, I think, because we'll
only be taking Depth rows that immediately follow a location row.
--
Earl Kiosterud
www.smokeylake.com

"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
Your right, thanks for the heads up.

here is the correction

=if(OR(And(A1="location",A2="depth"),A1="Depth")," Keep",#N/A)

This assumes you will never have two sequential rows with depth.

And that A1 does not contain "depth"

Alan Beban

Mimmo,

Well now we have to change things slightly. This assumes your first record
is in row 1, and that there aren't two consecutive Depth records anwhere (it
will ignore all but the first).

Put the following formulas, and copy down with the Fill Handle:

D1: =IF(AND(A1="Location",A2="Depth"),A2,#N/A)
E1: =IF(AND(A1="Location",A2="Depth"),B2,#N/A)

We have to convert the formula results to hard data (since they refer to
rows that will be deleted anon).
Select column D and E (drag in column header).
Copy.
Edit - Paste Special - Values.

Now to select the rows we don't want:
Columns D and E are still selected.
Edit - Goto - Special - Constants - Errors (only, uncheck other three)
This should leave the error values selected in.

Now to delete the selected rows.

Edit - Delete - Entire Row

You now have a bona-fide single-record-per-entity table, as you should, for
many of Excel's tools (sort, subtotal, pivot table, etc etc etc) to use. I
wish more people understood that. Actually, you don't need column A
(location) or column D (depth) any more - they contain redundant data -- and
could delete them if you want to:

Click column A header
Ctrl-Click column D header
Press Ctrl - minus

--
Earl Kiosterud
www.smokeylake.com

"mimmo" wrote in message
...

oops, one last question if you don't mind...
the formula worked great now I end up with something that looks like
this:

location number number
depth number
location number number
depth number
etc etc


is it possible to select each depth and number, cut and paste them so
that I end up with:

location number number depth number
location number number depth number
etc.

in other words each row has both location and depth data

hope I explained this well enough..
thanks in advance


--
mimmo
------------------------------------------------------------------------
mimmo's Profile:
http://www.excelforum.com/member.php...o&userid=26724
View this thread: http://www.excelforum.com/showthread...hreadid=399784