Pulling corresponding cell contents from single column data export.

[Eugee]

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.

[Ken Puls]

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.

[Eugee]

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

[Eugee]

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.

[Eugee]

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.

[Ken Puls]

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!