Results 1 to 6 of 6

Thread: Pulling corresponding cell contents from single column data export.

  1. #1

    Pulling corresponding cell contents from single column data export.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Last edited by Eugee; 2011-10-26 at 03:36 PM.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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".
    Last edited by Eugee; 2011-10-26 at 08:29 PM.

  4. #4
    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.

  5. #5
    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.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •