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?
[TABLE="width: 529"]
<tbody>[TR]
[TD][/TD]
[TD]Offshore Cash
[/TD]
[TD]Offshore Assets
[/TD]
[TD]Offshore Futures
[/TD]
[TD]Offshore Sup Tasks
[/TD]
[TD]Onshore Cash
[/TD]
[TD]Onshore Assets
[/TD]
[TD]Onshore Sup Tasks
[/TD]
[/TR]
[TR]
[TD]Person 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 3
[/TD]
[TD]L
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD][/TD]
[TD]O
[/TD]
[TD]O
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 4
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD][/TD]
[TD]O
[/TD]
[TD]C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 5
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD][/TD]
[TD]L
[/TD]
[TD]I
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 6
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD]C
[/TD]
[TD]L
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]Person 7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]O
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]Person 8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]O
[/TD]
[TD][/TD]
[TD][/TD]
[TD]O
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Assuming your data in A1:H9 try


[Table="class: grid"][tr][td] [/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td]

List​

[/td][/tr]


[tr][td]

2​

[/td][td]

Person 4​

[/td][/tr]


[tr][td]

3​

[/td][td]

Person 5​

[/td][/tr]


[tr][td]

4​

[/td][td]

Person 6​

[/td][/tr]
[/table]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

List​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Person 4,Person 5,Person 6​

[/TD]
[/TR]
</tbody>[/TABLE]


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.