Transferred from one sheet to another sheet

[KlausW]

Hi Excel helpers

I have a sheet with some member numbers (in column A, from A3 to A18) of an association.
Where those who do not check with X as a participant (in column D, from D3 to D18) will be transferred to Sheet 2 cell U2.
Is it possible for the member numbers to be placed in the same cell separated by a comma in cell U2?
Whit a formel.
All help will be appreciated.

Regards Klaus W

 

[JGordon11]

Array formula:

=TEXTJOIN(", ",TRUE,IF(Sheet1!D3:D18<>"X",Sheet1!A3:A18,""))

Confirm with Ctrl Shift Enter (not just Enter)

 

[Fluff]

As 2016 does not have the TextJoin function, you could do it like

=MID(IF(D3<>"X",", "&A3,"")&IF(D4<>"X",", "&A4,"")&IF(D5<>"X",", "&A5,""),3,500)

 

[Fluff]

Another option would be a UDF

Function KlausW(RngA As Range, RngB As Range) As String
      
   KlausW = Join(Filter(Evaluate("transpose(if(" & RngB.Address(, , , 1) & "<>""X""," & RngA.Address(, , , 1) & ",""|""))"), "|", False), ", ")
End Function

Used like

=KlausW(Sheet1!A3:A18,Sheet1!D3:D18)

 

[KlausW]

As 2016 does not have the TextJoin function, you could do it like

=MID(IF(D3<>"X",", "&A3,"")&IF(D4<>"X",", "&A4,"")&IF(D5<>"X",", "&A5,""),3,500)

Thank Fluff just what I neded. KW

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JGordon11]

Fluff - ?Application.Version in the immediate pane returns 16.0 for me. And this version includes TEXTJOIN. Is 16.0 different from 2016?

 

[Fluff]

Is 16.0 different from 2016?

Yes everything from 2016 onwards shows 16.0, you need to go to file, account & it will show what your using

 

[JGordon11]

Thanks