Assuming that there are only numbers and text in column B, with a header in row 1, try the macro
below.

HTH,
Bernie
MS Excel MVP

Sub Macro2()
Dim myRow As Long

myRow = Range("B65536").End(xlUp).Row
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Range("C2").FormulaR1C1 = _
"=IF(ISNUMBER(R[-1]C[-1]),R[-1]C,IF(ISNUMBER(RC[-1]),R[-1]C+1,R[-1]C))"
Range("D2").FormulaArray = _
"=MIN(IF(ISNUMBER(R2C2:R" & myRow & "C2)*(R2C3:R" & _
myRow & "C3=RC[-1]),ABS(R2C2:R" & myRow & "C2),MAX(C[-2])))"
Range("E2").FormulaR1C1 = "=IF(ISNUMBER(RC[-3]),RC[-1]=ABS(RC[-3]),FALSE)"
Range("F2").FormulaR1C1 = _
"=IF(OR(R[-1]C[-1],R[1]C[-1]),IF(ISNUMBER(RC[-4]),RC[-4],""""),"""")"
Range("G2").FormulaR1C1 = "=IF(RC[-1]<"""",RC[-4],"""")"
Range("C2:G2").Select
Selection.AutoFill Destination:=Range("C2:G" & myRow)
Columns("F:G").Copy
With Columns("H:I")
.PasteSpecial Paste:=xlPasteValues
.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlNo, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Columns("C:G").Delete
Columns("C:C").Cut Columns("E:E")
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub



"Cygnusx1" wrote in message
...
Your last line, otherwise you could you a macro to do it. Let me say that
with macros I use the record button and can tweak a little bit of code. I did
this with my first set of data and it did work. However because the data is
not in the same place everytime it does not work with a next data set. Is
what I would need to do to put this into a macro be to complacted to explain
here? or is this just way over my head?

Thank you

"Bernie Deitrick" wrote:

Cygnusx1,

In cell C2, enter the formula
=IF(ISNUMBER(B1),C1,IF(ISNUMBER(B2),C1+1,C1))

In D2, Array-enter the formula (enter using ctrl-shift-enter) replace the ??? with your last row
number
=MIN(IF(ISNUMBER($B$2:$B$???)*($C$2:$C$???=C2),ABS ($B$2:$B$???),MAX(B:B)))

In E2, enter the formula
=D2=B2

In F2, enter the formula
=IF(OR(E1,E3),IF(ISNUMBER(B2),B2,""),"")

In G2, enter the formula
=IF(F2<"",C2,"")

Copy C2:G2 down to match your data set, then copy columns F:G, pastespecial as values somewhere
else, and then sort based on the second column of your copied values, and you will have your data
set.

Otherwise, you could use a macro to do it.

HTH,
Bernie
MS Excel MVP


"Cygnusx1" wrote in message
...
I have 27 different sets of data with each seperated by a few lines of text.
I need to be able to find the closest number to zero in comumn "B" for each
set(I have both negative and positive numbers) and then grab the numbers on
each side of it to copy to another location.
Thank you