Pulling corresponding cell contents from single column data export.

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.

Hi there, and welcome to the forum.

Could you post a sample workbook with some mocked up data? Click the "Go Advanced" button and you can attach it. Easier for us to see/test when we've got data to work with.

View attachment SampleROUT.xlsx

There you go. That's the first 3 "sets" of data in column A. Column B is my extract of all the lines that have ROUT from Notepad++, and column C is just pulling out "ROUT ##" to make lookups easier/cleaner. I need D2 to find C2 in A:A and return the next cell containing "ACOD".

I thought I would do this with an INDEX(MATCH()) formula, but searching for a text string in a list of cells is something I've never really done before, and I'm really banging my head on a wall here. Today I just extracted the ROUTs and ACODs in Notepad++ and luckily there were the same number of lines so that worked, but the whole reason we're doing this is to audit for missing ACODs so I really need it to check each ROUT for an ACOD before the next ROUT.

Incidentally, I did figure out how to do this; what you do is create a lookup table of all the ROUTs, with the lookup column being a cell that returns the Row# of the corresponding ROUT. Then you VLOOKUP() the ACOD row# in your ROUT table, returning the greatest value without going over; that's the ROUT cluster that the ACOD appears in.

Eugee, I'm sorry, I don't recall getting a notification about your reply. It's possible I was working on the site when you posted, so it didn't flag it or something.

Glad you got it sorted though!