Results 1 to 6 of 6

Thread: Help with IFERROR formula

  1. #1

    Help with IFERROR formula



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

    I would like to create a cell with some or all of these following six values, which should be separate by commas: WD1, WD2, WD3, WD4, WD5, WD6. Here's an example how a spreadsheet might look in the end.

    Attachment 7293


    Whether of not one or more of these values appears in the cell is dependent on whether there is an X in one or more of the adjacent columns.

    Attachment 7292


    I found a following formula, which works well for the first column. But I don't know how to customize to add the possibilty of more than one item in the list.
    =IFERROR(IF(SEARCH("*X*",H2,1),"WD1"),"No")

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,469
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    you seem to have problems wit the attachments
    Please post a sample sheet with some data and expected results. To do this click " go advanced" and follow the " manage attachments" wizard.
    Thank you Ken for this secure forum.

  3. #3
    I have this data:

    1 2 3 4 5
    Bob X X
    Jim X X X
    Ned X X X

    I need it to look like this:

    Value
    Bob 2,3
    Jim 1,2,4
    Ned 1,2,5


    I found a following formula, which works well for the first column. But I don't know how to customize to add the possibilty of more than one item in the list.
    =IFERROR(IF(SEARCH("*X*",H2,1),"1"),"No")

  4. #4
    Can anyone help with this formula. I still don't know how to do it. Thanks.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try this:

    We will need to add a user defined function first...

    Insert a new VBA module (Alt+F11, Insert >> Module)

    Paste this code:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    Then back in the spreadsheet...

    assuming your data is in A1:F4, and your list of names to match are in A8:A10, then in B8 enter this Array* formula:

    =SUBSTITUTE(TRIM(aconcat(IF(INDEX($B$2:$F$4,MATCH(A8,$A$2:$A$4,0),0)="X",$B$1:$F$1,"")," "))," ",",")

    copied down

    * Array formula needs you to confirm it with CTRL+SHIFT+ENTER not just ENTER


  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    You could try a formula such as:
    =blah(D3:H3,$D$2:$H$2,"X")
    copied down, supported by the udf:
    Code:
    Function blah(rng, rng2, myStr)
    blah = Replace(Application.Trim(Join(Evaluate("if(" & rng.Address(external:=True) & "=""" & myStr & """," & rng2.Address(external:=True) & ","""")"))), " ", ",")
    End Function
    See attached.
    Attached Files Attached Files

Posting Permissions

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