Annoyed i cant do this....

[dantheram]

hi all - really annoyed i can't figure this one out in my head as i know it's in there somewhere, anyhow -

I want the column "Missing Elements" to concatenate all the missing elements, so either; vision, skill, incentive, resources, plan.

if the flag cell contains a "1" this means the element is present, a "0" means it is not - so the formula needs to go to each cell and return the corresponding value for each "0" and present these, concatenated, in the "missing elements" column. so, for the below, the first row would read "None" and the second row would read "Skill - Resources"

Vision Flag	Skill Flag	Incentive Flag	Resources Flag	Plan Flag	Overall Change Score	Combined Change Profile	Missing Elements
1	1	1	1	1	5	CHANGE
1	0	1	0	1	3	- - - - ANXIETY - - - FRUSTRATION - -

thanks in advance

 

[awoohaw]

try:

=TRIM(
IF(A3=0,"Vision ","")&
IF(B3=0,"Skill ","")&
IF(C3=0,"Incentive ","")&
IF(D3=0,"Resource ","")&
IF(E3=0,"Plan",""))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 2019 or later how about

=TEXTJOIN(", ",,IF(A2:E2=0,A$1:E$1,""))

 

[dantheram]

try:

=TRIM(
IF(A3=0,"Vision ","")&
IF(B3=0,"Skill ","")&
IF(C3=0,"Incentive ","")&
IF(D3=0,"Resource ","")&
IF(E3=0,"Plan",""))

this is nearly bang on - how do i return 'none' if all cells contain "1"?

 

[awoohaw]

=if(sum(A3:E3)=5,"None",TRIM(
IF(A3=0,"Vision ","")&
IF(B3=0,"Skill ","")&
IF(C3=0,"Incentive ","")&
IF(D3=0,"Resource ","")&
IF(E3=0,"Plan","")))

or to amend @Fluff 's solution:

=IF(SUM(A3:E3)=5,"None",SUBSTITUTE(TEXTJOIN(", ",,IF(A3:E3=0,A$1:E$1,""))," Flag",""))

 

[Fluff]

@dantheram any chance you could respond to my post?

 

[dantheram]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 2019 or later how about

=TEXTJOIN(", ",,IF(A2:E2=0,A$1:E$1,""))

hi

this one returns the 0 and 1's as below >>

Vision Flag	Skill Flag	Incentive Flag	Resources Flag	Plan Flag	Overall Change Score	Combined Change Profile	Missing Elements	Fluff
1	1	1	1	1	5	CHANGE	- - - -	None
1	0	1	0	1	3	- - - - ANXIETY - - - FRUSTRATION - -	- Skill - - Resource -	0, 0

Awoohaw's returns the actual missing element text - which is what i need

thanks

 

[Peter_SSs]

What about this more compact version since you have 365? (It also presents the results as you requested)

the second row would read "Skill - Resources"

23 03 11.xlsm
	A	B	C	D	E			H
1	Vision Flag	Skill Flag	Incentive Flag	Resources Flag	Plan Flag			Missing Elements
2	1	1	1	1	1			None
3	1	0	1	0	1			Skill - Resources
Missing

Cell Formulas
Range	Formula
H2:H3	H2	=LET(t,SUBSTITUTE(TEXTJOIN(" - ",,IF(A2:E2,"",A$1:E$1))," Flag",""),IF(t="","None",t))