Excel concat function

Question

I need a little bit of help. I'm trying to create a macro. It's for hotel vacancies.

Column F2:F8 lists vacancy or occupied. Column A2:A8 lists the room numbers.

I'm trying to create a macro that will list the room numbers in a different cell, if their corresponding room is vacant. I'm truly grateful for all your help!

=concat((a2:a8)If(f2:f8,"vacant"))???

CONCAT is not a macro, It is a built in function available with Office 365 Excel. Nor is it VBA — Scott Craner
– Scott Craner, Commented Aug 25, 2017 at 19:34

Scott Craner · Accepted Answer · 2017-08-25 19:31:56Z

1

use TEXTJOIN as it will allow the addition of a delimeter in an array form:

=TEXTJOIN(", ",TRUE,IF(F2:F8 = "vacant",A2:A8,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

answered Aug 25, 2017 at 19:31

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel concat function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related