Concatenate and IF?

[Hannah]

Good Afternoon

I have an excel spreadsheet which shows all the active vacancies for each
department.

December January
Detail (inc date) Forecast Detail (inc date) Forecast
Engineering Portfolio Lead 1 Mechanical Engineer (FRS) 1
Mechanical Engineer (G&A) 0
C&I Engineer 1


Total 1 2


Those that are active have a number in the column next to them. Those that
have been filled have a zero in the column next to them



Please can you tell me if there is any way to create a list of all those
vacancies that are active in another cell? (bearing in mind that we could
have as many as 15/20 vacancies in one department at any one time)

Many thanks

 

[JBeaucaire]

Concatenating all those values into a single cell will require you to install
some sort of additional functionality since string concatenation (concat
multiple cells based on matching criteria in other cells) is not a native
Excel function...yet.

But if you're OK creating a "list" of active jobs, then you can accomplish
this with a common (though hard to read) array formula.

As an example...let's say your setup is as follows:

Column A - list of job names
Column B - status (1 or 0 values)

D1 = the word "Active Jobs"
E1 =SUM(B:B)

In D2 we put the following Array-entered formula:

=IF(ROWS($1:1)>$E$1, "", INDEX($A$1:$A$30, SMALL(IF($B$1:$B$30=1,
ROW($B$1:$B$30), ""), ROWS($1:1))))

You press CTRL-SHIFT-ENTER to confirm that formula, not just ENTER. This
will activate the array and you will see curly braces { } appear around your
formula in the formula bar. Also, the first Job name from column A should
appear.

Now copy D2 down about 20 rows to insure you have the array active in enough
cells.

Does this get you in the right direction?

 

[Hannah]

Good Morning

Many thanks for your assistance.

The list formula is great, but i'm not sure how to adapt it so that it works
in my spreadsheet as i have two columns for each month (and i'm looking at a
years worth of vacancies) as opposed to the vacancies being listed under one
another.

Any further suggestions gratefully received.

Thank you

Hannah