Help with IFERROR formula

thumperstrauss

thumperstrauss

New member
Joined
Aug 25, 2017
Messages
3
Reaction score
0
Points
0
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.

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

View 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")
 
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.
 
I have this data:

12345
BobXX
JimXXX
NedXXX

I need it to look like this:

Value
Bob2,3
Jim1,2,4
Ned1,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")
 
Can anyone help with this formula. I still don't know how to do it. Thanks.
 
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
 
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.
 

Attachments

  • ExcelGuru8276.xlsm
    17.3 KB · Views: 8
You must log in or register to reply here.
Back
Top