+ Reply to Thread
Results 1 to 5 of 5

Extract text from cell in correlation with indexing

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Extract text from cell in correlation with indexing

    I have a spreadsheet that goes something like this:

    Document Name Document References Document Referenced In
    Doc1 Doc 4
    Doc 2 Doc 1
    Doc 3 Doc 2
    Doc 4 Doc 2
    Doc 5 Doc 1, Doc 2

    I want the third column to return the docs that the document is referenced in. For example the third column for row Doc 1 should return "Doc 2, Doc 5", the third column for row Doc 2 should return "Doc 3, Doc 4, Doc 5" and the third column for row Doc 4 should return " Doc 1"

    Can anyone help me find a way to do this without macros?

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Extract text from cell in correlation with indexing

    Since Concatenate is not array enabled there will be a bunch of helper columns. I threw in a UDF there anyway for comparison.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Extract text from cell in correlation with indexing

    OK, so now, what if there are blanks in the primary column?
    Last edited by Yakov on Excel; 09-28-2015 at 04:28 PM.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Extract text from cell in correlation with indexing

    To take care of blanks in column A use this formula that just adds IF(A2<>"","" to the front of the formula already given to you.
    Enter in C2 and fill down and across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    A
    B
    C
    D
    E
    F
    G
    1
    Document Name Document References
    helper1
    helper2
    helper3
    helper4
    helper5
    2
    Doc 1 Doc 4
    Doc 2
    Doc 5
    3
    Doc 2 Doc 1
    Doc 3
    Doc 4
    Doc 5
    4
    Doc 3 Doc 2
    5
    Doc 4 Doc 2, Doc 6
    Doc 1
    doc 6
    6
    Doc 5 Doc 1, Doc 2
    7
    8
    doc 6 doc 4
    Doc 4
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Extract text from cell in correlation with indexing

    Fixed empty cells in left column.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to open saved web html pages - extract text - paste text within individual cell
    By EddieRubi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-23-2015, 01:43 PM
  2. Replies: 4
    Last Post: 08-13-2014, 11:03 PM
  3. [SOLVED] Extract Text in 2 cell
    By hkbhansali in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2014, 09:32 AM
  4. [SOLVED] Indexing not working on copy/paste text
    By sperrysperry in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2014, 02:33 PM
  5. [SOLVED] Extract/Copy Text from Cell, and insert that text in a new row below
    By jamesoneill in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-07-2013, 09:06 AM
  6. Replies: 12
    Last Post: 09-15-2012, 10:20 PM
  7. Replies: 3
    Last Post: 07-25-2012, 07:54 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1