Search Column For Data

[PaladinTom]

Hi All,

I have a single long column that contains repeating groups of data
sets. Each set contains a fixed number of headers followed by a
variable number of data that looks like this:

ANGLE
(DEG)
56.36
10.63

ANGLE
(DEG)
56.36
10.63
45.9

ANGLE
(DEG)
56.38
10.87
2.35
1.33
1.26

The problem is that I ONLY need the second reading after the (DEG)
header. Does anyone know of a way that I can scan this column and
copy only that data to another sheet?

Thanks in advance!

Tom

 

[Roger Govier]

Hi

The following code assumes the source data is on Sheet1 in column A (1) and
the destination is Sheet2 column A.
Change references to suit

Sub ExtractDegrees()
Dim lr As Long, i As Long, j As Long
Dim wss As Worksheet, wsd As Worksheet

Set wss = Sheets("Sheet1")
Set wsd = Sheets("Sheet2")
j = 1
lr = wss.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
If wss.Cells(i, 1) = "(DEG)" Then
wsd.Cells(j, 1) = wss.Cells(i + 1, 1).Value
j = j + 1
End If
Next i
End Sub

To Install
Copy code above
Alt+F11 to invoke the VB Editor
Alt+I+M to insert a new Module
Paste the code into the white pane that appears
Alt+F11 to return to Excel

To USE
Alt+F8
Select the macro
Run

--
Regards
Roger Govier

Hi All,

I have a single long column that contains repeating groups of data
sets. Each set contains a fixed number of headers followed by a
variable number of data that looks like this:

ANGLE
(DEG)
56.36
10.63

ANGLE
(DEG)
56.36
10.63
45.9

ANGLE
(DEG)
56.38
10.87
2.35
1.33
1.26

The problem is that I ONLY need the second reading after the (DEG)
header. Does anyone know of a way that I can scan this column and
copy only that data to another sheet?

Thanks in advance!

Tom

__________ Information from ESET Smart Security, version of virus
signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Bernie Deitrick]

You could also use a formula like

=IF(A2="(DEG)",A4,"")

Then copy that down, copy all the formulas and paste values elsewhere, and get rid of the blanks by
sorting.

HTH,
Bernie
MS Excel MVP