Arrary If formula not giving expected result

[mcfclax]

Hi

I have a sheet which shows the training needs of a team of people. I want to be able to use a formaula that will show who has an "I" against each skill. So against Offshore Cash it would read "Person 4 Person 5 Person 6"

The formula I have used for this is

{=IF(B2:B9="I",A2:A9,"None")}

And it gives the result of none.

If I put an "I" against person 1 the result changes to "Person 1"

Any thoughts where this is going wrong?

	Offshore Cash	Offshore Assets	Offshore Futures	Offshore Sup Tasks	Onshore Cash	Onshore Assets	Onshore Sup Tasks
Person 1					I	I
Person 2					I	I
Person 3	L	I	I		O	O
Person 4	I	I	I		O	C
Person 5	I	I	I		L	I
Person 6	I	I	I	I	C	L	O
Person 7				O	I	I	I
Person 8				O			O

<tbody>
</tbody>

 

[Marcelo Branco]

Assuming your data in A1:H9 try

	J​
1​	List​
2​	Person 4​
3​	Person 5​
4​	Person 6​

Array formula in J2
=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$9="I",ROW($A$2:$A$9)),ROWS(J$2:J2))),"")

confirmed with Ctrl+Shift+Enter

copy down

M.

 

[mcfclax]

Thanks Marcelo Branco

That sort of works. It does give the correct people in a list going down in column J. Is is possible to have this as a combined list, with one name after the other all in the same cell (J2)?

 

[Marcelo Branco]

To put all values in the same cell you need VBA.

Paste the UDF (User Defined Function) below on a Standard Module

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

Copy (Ctrl+C) the code
Alt+F11 to open the VBEditor
Insert > Module
Paste (Ctrl+V) on the right panel

back to Excel

	J​
1​	List​
2​	Person 4,Person 5,Person 6​

<tbody>
</tbody>

Array formula in J2
=SUBSTITUTE(aconcat(IF($B$2:$B$9="I",","&$A$2:$A$9,"")),",","",1)

confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[mcfclax]

Thanks very much Marcelo. It was clearly way more complicated than I thought it would be.

I've done a little bit of VBA before but I have no idea what any of that code means.

However it does work a treat so thanks very much for all your help.

 

[Marcelo Branco]

Thanks very much Marcelo. It was clearly way more complicated than I thought it would be.

I've done a little bit of VBA before but I have no idea what any of that code means.

However it does work a treat so thanks very much for all your help.

You are welcome. Glad for helping.

M.