I frequently have to parse through a bunch of data from our phone switch, which is a text capture that is sent to me, which I then dig through to pull values out of. The data will appear like this in column A:

TYPE RDB

CUST 01

ROUT 7

DES MIRAN2

TKTP RAN

NPID_TBL_NUM 5

ACOD 8510

TARG

CDR YES

ETC...

TYPE RDB

CUST 01

ROUT 13

DES

TKTP MUS

ACOD 8925

NPID_TBL_NUM 6

VTRK NO

...

Note this is several thousand lines.

Now I used Notepad++ to pull all the ROUT lines out into a single list. I've put them into a single column in B:

B1 = ROUT 4

B2 = ROUT 7

B3 = ROUT 15

B4 = ROUT 23

...

What I need to return in Column C:

In C1 I need to find the value of B1 in the column A:A, and return the next cell containing ACOD. Now I was originally using:

C1 =INDEX(A:A,MATCH(B1,A:A,0)+10)

Which looks in column A, returning the Row# of the match + 10. But in these new exports, the position of the ACOD is not fixed relative to ROUT.

I tried tinkering around with finding a cell containing ACOD in a range defined from the ROUT in B# to B#+1, but it's getting a little over my head and I'm starting to think there must be an easier way.

Any help out there? Thanks in advance.

TYPE RDB

CUST 01

ROUT 7

DES MIRAN2

TKTP RAN

NPID_TBL_NUM 5

ACOD 8510

TARG

CDR YES

ETC...

TYPE RDB

CUST 01

ROUT 13

DES

TKTP MUS

ACOD 8925

NPID_TBL_NUM 6

VTRK NO

...

Note this is several thousand lines.

Now I used Notepad++ to pull all the ROUT lines out into a single list. I've put them into a single column in B:

B1 = ROUT 4

B2 = ROUT 7

B3 = ROUT 15

B4 = ROUT 23

...

What I need to return in Column C:

In C1 I need to find the value of B1 in the column A:A, and return the next cell containing ACOD. Now I was originally using:

C1 =INDEX(A:A,MATCH(B1,A:A,0)+10)

Which looks in column A, returning the Row# of the match + 10. But in these new exports, the position of the ACOD is not fixed relative to ROUT.

I tried tinkering around with finding a cell containing ACOD in a range defined from the ROUT in B# to B#+1, but it's getting a little over my head and I'm starting to think there must be an easier way.

Any help out there? Thanks in advance.

Last edited: