+ Reply to Thread
Results 1 to 6 of 6

Find cells containing text, but group them at top of adjacent column

  1. #1
    Registered User
    Join Date
    07-24-2008
    Location
    Australia
    Posts
    10

    Find cells containing text, but group them at top of adjacent column

    Hi all,

    I have a single column of text values in column A.

    In another column (let's say, column B) I want to see all of values that contained the string "_testresult_"... but I want those to appear collected together in column B.

    So for example...
    excel_cells_collected.PNG

    I know how to find the cells that contain "_testresult_" but what I don't know how to do is have them all appear one after the other in another column (without many cell "gaps" between each found).

    FYI... if to make this work some "interim columns" have to be created, that's OK.

    Any help is VERY much appreciated!
    (PS: I've searched around but can't find a specific example of this... happy to be pointed at another post if this has been asked)


    Cheers,

    Stanbridge

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find cells containing text, but group them at top of adjacent column

    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    07-24-2008
    Location
    Australia
    Posts
    10

    Re: Find cells containing text, but group them at top of adjacent column

    Thanks Tony

    FYI... thinking about this some more (and using an interim column, I think the steps are:
    1) Create a column that only displays the cells containing my string
    2) Create another column that looks at the column created in step 1 and removes blanks.

    So I've attached an example of my Spreadsheet (although it's not working). The formula I used and range concept comes form here: http://www.cpearson.com/excel/noblanks.aspx

    Below is the screenshot I included above in JPG format (note the result column doesn't have to be in the absolute adjacent column as per this screenshot.

    excel_cells_collected.JPG


    Cheers
    Attached Files Attached Files

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find cells containing text, but group them at top of adjacent column

    Try this...

    Data Range
    A
    B
    1
    some text
    some _testresult_
    2
    some more text
    blah _testresult_ blah blah
    3
    some _testresult_
    _testresult_
    4
    and another string
    5
    blah _testresult_ blah blah
    6
    _testresult_
    7
    another value
    8
    something


    This array formula** entered in B1:

    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("_testresult_",A$1:A$8)),ROW(A$1:A$8)),ROWS(B$1:B1))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

  5. #5
    Registered User
    Join Date
    07-24-2008
    Location
    Australia
    Posts
    10

    Re: Find cells containing text, but group them at top of adjacent column

    That's got it mate, legendary!

    Thanks very much!


    Stanbridge

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find cells containing text, but group them at top of adjacent column

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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] Find text in column and return adjacent cell
    By frostii in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-27-2015, 07:44 PM
  2. Replies: 2
    Last Post: 04-17-2014, 03:30 PM
  3. [SOLVED] Find Text and copy adjacent cells
    By wdwinterfang in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-21-2013, 09:27 PM
  4. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  5. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM
  6. Replies: 4
    Last Post: 05-12-2011, 04:37 PM
  7. Find value in one column, display the text from adjacent colomn
    By todley1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2010, 04:48 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