Copy multiple cell contents to another cell, IF a condition is met

[sted999]

Hi everyone.

Sorry if this is a simple question, but this formula has defeated a colleague and I....and we have no idea what to try next!

Basically, I have a table of employment circumstances. From the responses, I have added two columns on the end which performs some basic validation e.g flag if the respondent has said if they employed and unemployed. A simplified version of my data is below -

ID	Employment	Unemployed	Further study	Unemployed and working,	Unemployed and further study,	Errors
Person 1	Yes	Yes	No	TRUE	FALSE
Person 2	No	Yes	Yes	FALSE	TRUE
Person 3	No	Yes	No	FALSE	FALSE
Person 4	Yes	Yes	Yes	TRUE	TRUE

What I am now wanting to do is, where there is a TRUE in the validation columns, is to copy the column heading into the 'Errors' column - simply to provided a summary of the errors within the data. For example I need "Unemployed and working, Unemployed and further study," in the 'Errors' column for Person 4. As seen below.....

Desired outcome

ID	Employment	Unemployed	Further study	Unemployed and working,	Unemployed and further study,	Errors
Person 4	Yes	Yes	Yes	TRUE	TRUE	Unemployed and working, Unemployed and further study

Any help would be greatly appreciated!

Many thanks

[Norie]

Is any TRUE considered an error?

Or is it only when TRUE causes a contradiction?

[Kaper]

in G2:

Please Login or Register  to view this content.

or better formatting (space if both conditions met):

Please Login or Register  to view this content.

and the version without showing columns E and F (E1 and F1 could be moved somewhere else):

Please Login or Register  to view this content.

[sted999]

Hi Norie. Yes, any TRUE is considered an error. The data comes in externally so this is the only way I can add validation (or the only way I've thought of!). Thanks for your help but Kaper's solution has cracked it!

Thanks so much for your help, it works an absolute treat. My colleague also passes on his thanks. We were amazed it only took 14 minutes for a solution, we've been working on it for hours!

Thanks again, all the best.


SOLVED