# Thread: Help with IFERROR formula

1. ## Help with IFERROR formula

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. 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.

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. Can anyone help with this formula. I still don't know how to do it. Thanks.

5. 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```

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. 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.

#### Posting Permissions

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