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: