27 sets of data. Need to find zero in each set

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

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" <Cygnusx1@discussions.microsoft.com> wrote in message
news:F812D1B4-CB72-4AF6-AAAF-B96C75CFFE69@microsoft.com...
>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

On Fri, 9 Sep 2005 07:53:05 -0700, "Cygnusx1"
<Cygnusx1@discussions.microsoft.com> wrote:

>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

I'm not sure exactly what you mean.

To find the number in column B closest to zero, where 'rng' is the range (e.g.
B2:B30):

=MIN(ABS(rng))

entered as an **array** formula by holding down <ctrl><shift> when you hit
<enter>.

To find the row number of this value:

=MATCH(MIN(ABS(rng)),rng,0)

also entered as an **array** formula.

I'm not sure what you mean by "grab the numbers on each side of it".

The number in the preceding row would be:

=INDEX(rng,MATCH(MIN(ABS(rng)),rng,0)-1)

and you can see HELP on the INDEX function to modify it for the number in the
next row; or for the number in the same row but on the columns "on each side".

Finally, so far as "copy to another location", that cannot be done with a
worksheet function. You would need to put one of the above INDEX/MATCH
constructs into the cell in which you want the result to appear, or use a VBA
solution.


--ron

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" <Cygnusx1@discussions.microsoft.com> wrote in message
> news:F812D1B4-CB72-4AF6-AAAF-B96C75CFFE69@microsoft.com...
> >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
>
>
>

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" <Cygnusx1@discussions.microsoft.com> wrote in message
news:0D800DA6-8204-4B6B-96F0-B4FED69682FD@microsoft.com...
> 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" <Cygnusx1@discussions.microsoft.com> wrote in message
>> news:F812D1B4-CB72-4AF6-AAAF-B96C75CFFE69@microsoft.com...
>> >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
>>
>>
>>