hello,
I have these data:
LVSNR AKNUS AKAVZ LVCOD LVARR
CJ03304442 872620080 16 VI SV
CJ03304442 872620080 41 VI SV
CJ03304442 872620080 41 VI SV
IU00789017 873050400 16 BZ MI11
IU00789017 873050400 16 BZ CO
MT21880207 872465980 07 MEL CZ
MT21880207 872465980 05 MEL MO
MT21880239 872466300 16 MEL CZ
MT21880239 872466300 16 MEL CZ

they are thousands rows, so I don't need of filter on, becouse they are too
much to be check one to one.

I need know
1.what's the line next a value. AND
2.what's the line before a value.
for example:
If I know this line:
IU00789017 873050400 16 BZ CO
1. I need to know the next line, tha's:
MT21880207 872465980 07 MEL CZ

and if I know this line:
MT21880239 872466300 16 MEL CZ
2. if I need to know the previous line, what's the way?:
MT21880207 872465980 07 MEL CZ

I know it is not easy, but I hope u can help me, becouse it's really
important for my job!.
thanks a lot to everyone wants help me.
--
Andrew

On Feb 17, 5:54*pm, AND wrote:
hello,
I have these data:
LVSNR * AKNUS * AKAVZ * LVCOD * LVARR
CJ03304442 * * *872620080 * * * 16 * * *VI * * *SV
CJ03304442 * * *872620080 * * * 41 * * *VI * * *SV
CJ03304442 * * *872620080 * * * 41 * * *VI * * *SV
IU00789017 * * *873050400 * * * 16 * * *BZ * * *MI11
IU00789017 * * *873050400 * * * 16 * * *BZ * * *CO
MT21880207 * * *872465980 * * * 07 * * *MEL * * CZ
MT21880207 * * *872465980 * * * 05 * * *MEL * * MO
MT21880239 * * *872466300 * * * 16 * * *MEL * * CZ
MT21880239 * * *872466300 * * * 16 * * *MEL * * CZ

they are thousands rows, so I don't need of *filter on, becouse they are too
much to be check one to one.

I need know
1.what's the line next a value. * AND
2.what's the line before a value.
for example:
If I know this line:
IU00789017 * * *873050400 * * * 16 * * *BZ * * *CO
1. * * * * * * * * * * * I need to know the next line, tha's:
MT21880207 * * *872465980 * * * 07 * * *MEL * * CZ

and if I know this line:
MT21880239 * * *872466300 * * * 16 * * *MEL * * CZ
2. * * * * * * * * * * * if I need to know the previous line, what's the way?:
MT21880207 * * *872465980 * * * 07 * * *MEL * * CZ

I know it is not easy, but I hope u can help me, becouse it's really
important for my job!.
thanks a lot to everyone wants help me.
--
Andrew

Why do you need to do this? What are you trying to do?

A couple of questions about your data...

Is each line in a single cell (that is, is CJ03304442 872620080 16 VI SV in
cell A2)? Or are these spread into different cell in the row (that is, does
A2 contain CJ03304442 and B2 contain 872620080, etc.)?

Also, what do you mean by "I know this line"? In what way do you know it? Is
the value or values (depending on how you answered the above question) in a
cell or cells somewhere else on your worksheet?

Where do you want the "answers" to be located at?

Can there be more than one match for the data that you know? If so, how is
that to be handled?

--
Rick (MVP - Excel)


"AND" wrote in message
...
hello,
I have these data:
LVSNR AKNUS AKAVZ LVCOD LVARR
CJ03304442 872620080 16 VI SV
CJ03304442 872620080 41 VI SV
CJ03304442 872620080 41 VI SV
IU00789017 873050400 16 BZ MI11
IU00789017 873050400 16 BZ CO
MT21880207 872465980 07 MEL CZ
MT21880207 872465980 05 MEL MO
MT21880239 872466300 16 MEL CZ
MT21880239 872466300 16 MEL CZ

they are thousands rows, so I don't need of filter on, becouse they are
too
much to be check one to one.

I need know
1.what's the line next a value. AND
2.what's the line before a value.
for example:
If I know this line:
IU00789017 873050400 16 BZ CO
1. I need to know the next line, tha's:
MT21880207 872465980 07 MEL CZ

and if I know this line:
MT21880239 872466300 16 MEL CZ
2. if I need to know the previous line, what's the
way?:
MT21880207 872465980 07 MEL CZ

I know it is not easy, but I hope u can help me, becouse it's really
important for my job!.
thanks a lot to everyone wants help me.
--
Andrew

You can use a match() function to return the position of a particular set of
data.
For instance, if A2 had that value in it:
MT21880207 872465980 07 MEL CZ

Then this formula could search for it in column B.
=MATCH(A2,B100:B1000,0)

That returns a value of 6 from your sample. Once you have that number, you
can add or subtract from that number.

=MATCH(A2,B100:B1000,0)+1
Results are now 7

Lastly, you can feed that adjusted number BACK into the original range and
get the offset value.

=INDEX(B100:B1000,MATCH(A2,B100:B1000,0)+1))

Give it a try. This is dependent up the ability find that unique string
value at position 6 in column B, or whatever column that data is in.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"CurlyDave" wrote:

On Feb 17, 5:54 pm, AND wrote:
hello,
I have these data:
LVSNR AKNUS AKAVZ LVCOD LVARR
CJ03304442 872620080 16 VI SV
CJ03304442 872620080 41 VI SV
CJ03304442 872620080 41 VI SV
IU00789017 873050400 16 BZ MI11
IU00789017 873050400 16 BZ CO
MT21880207 872465980 07 MEL CZ
MT21880207 872465980 05 MEL MO
MT21880239 872466300 16 MEL CZ
MT21880239 872466300 16 MEL CZ

they are thousands rows, so I don't need of filter on, becouse they are too
much to be check one to one.

I need know
1.what's the line next a value. AND
2.what's the line before a value.
for example:
If I know this line:
IU00789017 873050400 16 BZ CO
1. I need to know the next line, tha's:
MT21880207 872465980 07 MEL CZ

and if I know this line:
MT21880239 872466300 16 MEL CZ
2. if I need to know the previous line, what's the way?:
MT21880207 872465980 07 MEL CZ

I know it is not easy, but I hope u can help me, becouse it's really
important for my job!.
thanks a lot to everyone wants help me.
--
Andrew

Why do you need to do this? What are you trying to do?

Try this:

For the next line value:
assuming criteria in F2
=INDEX(A:A,MATCH(F2,A:A,)+COUNTIF(A:A,F2))

For the previous line value:
assuming criteria in F5
=INDEX(A:A,MATCH(F5,A:A,)-1)


"AND" wrote:

hello,
I have these data:
LVSNR AKNUS AKAVZ LVCOD LVARR
CJ03304442 872620080 16 VI SV
CJ03304442 872620080 41 VI SV
CJ03304442 872620080 41 VI SV
IU00789017 873050400 16 BZ MI11
IU00789017 873050400 16 BZ CO
MT21880207 872465980 07 MEL CZ
MT21880207 872465980 05 MEL MO
MT21880239 872466300 16 MEL CZ
MT21880239 872466300 16 MEL CZ

they are thousands rows, so I don't need of filter on, becouse they are too
much to be check one to one.

I need know
1.what's the line next a value. AND
2.what's the line before a value.
for example:
If I know this line:
IU00789017 873050400 16 BZ CO
1. I need to know the next line, tha's:
MT21880207 872465980 07 MEL CZ

and if I know this line:
MT21880239 872466300 16 MEL CZ
2. if I need to know the previous line, what's the way?:
MT21880207 872465980 07 MEL CZ

I know it is not easy, but I hope u can help me, becouse it's really
important for my job!.
thanks a lot to everyone wants help me.
--
Andrew

hello guys,
thanks a lot for ur precious support.
sorry for my late answer. I solved in this way:

the values was each in a different cell:
lvsr in A
aknus in B
etc.....

So I 've joined each line by "&" and I've used te "Teethless mama"'s
worksheet.

Thank you a lot again ones.

--
BBB


"JBeaucaire" wrote:

You can use a match() function to return the position of a particular set of
data.
For instance, if A2 had that value in it:
MT21880207 872465980 07 MEL CZ

Then this formula could search for it in column B.
=MATCH(A2,B100:B1000,0)

That returns a value of 6 from your sample. Once you have that number, you
can add or subtract from that number.

=MATCH(A2,B100:B1000,0)+1
Results are now 7

Lastly, you can feed that adjusted number BACK into the original range and
get the offset value.

=INDEX(B100:B1000,MATCH(A2,B100:B1000,0)+1))

Give it a try. This is dependent up the ability find that unique string
value at position 6 in column B, or whatever column that data is in.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"CurlyDave" wrote:

On Feb 17, 5:54 pm, AND wrote:
hello,
I have these data:
LVSNR AKNUS AKAVZ LVCOD LVARR
CJ03304442 872620080 16 VI SV
CJ03304442 872620080 41 VI SV
CJ03304442 872620080 41 VI SV
IU00789017 873050400 16 BZ MI11
IU00789017 873050400 16 BZ CO
MT21880207 872465980 07 MEL CZ
MT21880207 872465980 05 MEL MO
MT21880239 872466300 16 MEL CZ
MT21880239 872466300 16 MEL CZ

they are thousands rows, so I don't need of filter on, becouse they are too
much to be check one to one.

I need know
1.what's the line next a value. AND
2.what's the line before a value.
for example:
If I know this line:
IU00789017 873050400 16 BZ CO
1. I need to know the next line, tha's:
MT21880207 872465980 07 MEL CZ

and if I know this line:
MT21880239 872466300 16 MEL CZ
2. if I need to know the previous line, what's the way?:
MT21880207 872465980 07 MEL CZ

I know it is not easy, but I hope u can help me, becouse it's really
important for my job!.
thanks a lot to everyone wants help me.
--
Andrew

Why do you need to do this? What are you trying to do?