1

Sorry for the possibly bad explanation of my problem.

Essentially, I need to populate ONE cell with a list of column headers, wherever the column contains the value "Required" on one row. Then, for the next row I need to do the same for the next row down.

I can get the cell to populate one value simply with an if statement:

=IF(Review!E3="Required","Leavers","")

However, there's a possibility that for each of the 16 columns the value could be "Required". Is there a way to do this simply? I'm trying to avoid making multiple columns on the current worksheet with a Y/N for each column header.

If there's any clarification you need please get in touch!

woodnotwoods
  • 21
  • 2
  • I gather there are too many columns to efficiently concatenate each in turn..? A VBA User Defined Function handles conditional string concatenation better than worksheet functions. There are examples [here](http://stackoverflow.com/questions/27833805/joining-a-range-of-cells-in-excel-using/27834636#27834636) and [here](http://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1/28680713#28680713). –  Nov 21 '15 at 13:16
  • I've had a look at those and they look great, thanks! The only question is (sorry if it's a simple one) I think I understand the syntax, but how would I get it to populate based on where the value is "Required*"? Cheers – woodnotwoods Nov 21 '15 at 13:54
  • For the second one ([here](http://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1/28680713#28680713)) simply change `If CBool(rCRITs(c).Value2) Then _` to `If LCase(rCRITs(c).Value2) = "required" Then _`. –  Nov 21 '15 at 13:59
  • This worked a treat - thanks! – woodnotwoods Nov 21 '15 at 14:05

1 Answers1

0

Conditionally concatenating sixteen column labels depending on row content is not a completely unreasonable situation. The following formula may look complicated but it is very simple; just a CONCATENATE function nesting 16 repeated conditions with SUBSTITUTE and TRIM providing cleanup.

   required16

The standard formula in Q2 is,

=SUBSTITUTE(TRIM(CONCATENATE(IF(Review!A2="required", A$1&" ", ""),
                             IF(Review!B2="required", B$1&"  ", ""),
                             IF(Review!C2="required", C$1&"  ", ""),
                             IF(Review!D2="required", D$1&"  ", ""),
                             IF(Review!E2="required", E$1&"  ", ""),
                             IF(Review!F2="required", F$1&"  ", ""),
                             IF(Review!G2="required", G$1&"  ", ""),
                             IF(Review!H2="required", H$1&"  ", ""),
                             IF(Review!I2="required", I$1&"  ", ""),
                             IF(Review!J2="required", J$1&"  ", ""),
                             IF(Review!K2="required", K$1&"  ", ""),
                             IF(Review!L2="required", L$1&"  ", ""),
                             IF(Review!M2="required", M$1&"  ", ""),
                             IF(Review!N2="required", N$1&"  ", ""),
                             IF(Review!O2="required", O$1&"  ", ""),
                             IF(Review!P2="required", P$1&"  ", ""))), " ", ", ")

The line feeds used to organize the formula can be left in without detriment. Fill down as necessary.