+ Reply to Thread
Results 1 to 10 of 10

formula to search for text and display all in single cell

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    16

    formula to search for text and display all in single cell

    Hi,

    As i'm new to this, i am currently working on small traceability project.

    Consider the following table:
    Feature ID TS ID
    F1 TS 1.1
    TS 1.2
    TS 1.3
    F2 TS 2.1
    TS 2.2
    TS 2.3
    TS 2.4
    F3 TS 3.1
    TS 3.2

    I want to create a traceability for the TS ID's covered under a feature, like


    Feature ID TS covered
    F1 TS 1.1, TS 1.2, TS 1.3
    F2 TS 2.1, TS 2.2, TS 2.3
    F3 TS 3.1, TS 3.2


    To fill each cell for "TS covered" column, i tried using the partial text of "TS 1.?" for F1, "TS 2.?" for F2" and so on. However this is not resolving my issue. Please help

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: formula to search for text and display all in single cell

    Hello and welcome.
    You need this:
    http://www.xl-central.com/concatenat...n-a-range.html

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: formula to search for text and display all in single cell

    Whether the output result can be in separate cells in the right side?

    If you want the result to be shown in single cell then we have to go for VBA solution

  4. #4
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: formula to search for text and display all in single cell

    Quote Originally Posted by RobertMika View Post
    Thanks RobertMika. The function is doing as intended, but it is referencing another cell value before concatenating. Can it be done this way- find all entries with partial text "TS1.?" and concatenate those in a single cell?

  5. #5
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: formula to search for text and display all in single cell

    Quote Originally Posted by :) Sixthsense :) View Post
    Whether the output result can be in separate cells in the right side?

    If you want the result to be shown in single cell then we have to go for VBA solution
    Hi Sixthsense. Yes i want the result to be shown in one cell. And what kind of VBA solution would you recommend?

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: formula to search for text and display all in single cell

    Check my earlier post for solution in the below link Post # 2

    http://www.excelforum.com/excel-prog...on-is-met.html

  7. #7
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: formula to search for text and display all in single cell

    Quote Originally Posted by :) Sixthsense :) View Post
    Check my earlier post for solution in the below link Post # 2

    http://www.excelforum.com/excel-prog...on-is-met.html
    Just so im sure, if the LookUpValue can be a partial string, say "TS 2.?", LookUpRange be the column TS ID, what would be the ResultRange?

    P.S: Pardon my silly questions, I'm still a newbie

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: formula to search for text and display all in single cell

    Refer the attached file for detail
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: formula to search for text and display all in single cell

    Quote Originally Posted by :) Sixthsense :) View Post
    Refer the attached file for detail
    Well yes, in the file you attached it works like a charm, but what if the Feature ID column does not repeat the entries? Like this,

    Feature ID TS ID
    F1 TS 1.1
    TS 1.2
    TS 1.3

    As you can observe, the left cells dont repeat once stated. Can that be done?

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: formula to search for text and display all in single cell


+ 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. [SOLVED] Userform text box to display and edit a single cell only
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 11-25-2013, 08:50 AM
  2. Replies: 11
    Last Post: 11-03-2013, 09:16 PM
  3. [SOLVED] Formula to display cell if the text content partially matches a search term
    By Avestron in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2013, 04:07 AM
  4. Replies: 2
    Last Post: 12-18-2009, 10:59 AM
  5. display a single cell text from a range
    By royal blue in forum Excel - New Users/Basics
    Replies: 15
    Last Post: 04-06-2009, 02:26 AM

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