+ Reply to Thread
Results 1 to 7 of 7

Check if value is available in range, if yes, copy the corresponding data to a list

  1. #1
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Check if value is available in range, if yes, copy the corresponding data to a list

    Hello

    I am desperately looking for a formula that checks:
    "if a text string is available in a specific range of cells, copy the corresponding data to a list"

    I am stuck on the fact that I don't have one cell to check, there are a couple of cells in one row on which the requirement is applied.

    Example and intended end-result on my Drive:

    https://drive.google.com/file/d/1yA5...ew?usp=sharing

    Due to initial file upload problems, xlsx file is uploaded in a post below.

    Thank you in advance

    Ebru
    Last edited by Ebru; 01-14-2019 at 04:21 PM. Reason: clearer explanation

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,995

    Re: Check if value is available in range, if yes, copy the corresponding data to a list

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: Check if value is available in range, if yes, copy the corresponding data to a list

    with attachment
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: "If value is present in a range, if yes, copy the corresponding data to a list"

    Dear
    My goal is to apply a formula on the above attached example which filters a list on mentioning of a specific number
    I am really stuck at it.
    Do I need INDEX MATCH or others?

    Any clue is appreciated

    Ebru

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,995

    Re: Check if value is available in range, if yes, copy the corresponding data to a list

    Not sure I fully understand how you want the results shown, but see if this will get you started>
    F2=IF(LEFT(A2,2)="AB",A2,"")
    copied down and across as needed

  6. #6
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: Check if value is available in range, if yes, copy the corresponding data to a list

    Thanks for the reply FDibbins!

    The formula is more complex:
    it should go as:
    check for each row
    in columns A to E
    if there is one value on that row which starts with "AB" (businesswise there can only be 0 or 1 cell "AB" label in that range)
    copy that value and the corresponding data in columns F:I of that row.

    This is a very complex formula, but it helps in giving all relevant data linked to "AB", which is relevant for me.

    Ebru

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,688

    Re: Check if value is available in range, if yes, copy the corresponding data to a list

    This proposal employs helper columns (J2:N13) which may be moved and/or hidden for aesthetic purposes.
    J2:J13 are populated using*: =IFERROR(INDEX(A2:E2,MATCH("AB",LEFT(A2:E2,2),0)),"")
    *denotes an array entered formula which needs to be activate by simultaneously pressing the Ctrl, Shift and Enter keys before being copied down the column.
    K2:N13 are populated using: =IF(J2="","",F2)
    J19:J25 are populated using: =IFERROR(INDEX(J$2:J$13,AGGREGATE(15,6,(ROW($2:$13)-1)/(J$2:J$13<>""),ROW(1:1))),"")
    K19:N25 are populated using: =INDEX(K$2:K$13,MATCH($J19,$J$2:$J$13,0))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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