looking for the next value

A

AND

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.
 
C

CurlyDave

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.
Click to expand...

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

Rick Rothstein

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?
 
J

JBeaucaire

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.
 
T

Teethless mama

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)
 
A

AND

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.
 
Top