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