Concatenate data from mutiple cells only if they contain certain values

[SKooLZ]

Hi Gurus,

I'm new to this community/forum and I am hoping you can help me with the most easiest and efficient way (Formula or VB code) to get an excel spreadsheet to Concatenate data from mutiple cells only if those individual contain certain values as shown in the example table below. Example explained.

Concatenate all comments from A - F into column H only if the statuses = Bad or OK and ignore if the statuses = Good. These controlling values with be in text form as intimated above. Also, I would like each of the coment pairs to be listed on each row/line via &CHAR(10)&.

You urgent help is much appreciated.

Thanks in advance

Skoolz

[TABLE="width: 600"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]Reason
[/TD]
[TD]Status
[/TD]
[TD]Reason
[/TD]
[TD]Status
[/TD]
[TD]Reason
[/TD]
[TD]Status
[/TD]
[TD][/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]No issues
[/TD]
[TD]Good
[/TD]
[TD]Major issues
[/TD]
[TD]Bad
[/TD]
[TD]Minor issues
[/TD]
[TD]OK
[/TD]
[TD][/TD]
[TD]Bad - Major issues
OK - Minor issues
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Good
[/TD]
[TD][/TD]
[TD]Good
[/TD]
[TD][/TD]
[TD]Good
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Minor issues
[/TD]
[TD]OK
[/TD]
[TD][/TD]
[TD]Good
[/TD]
[TD][/TD]
[TD]Good
[/TD]
[TD][/TD]
[TD]OK - Minor issues
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Asala42]

Based on your specific layout I'd probably just brute force it.

=IF(OR(B3="Bad",B3="Ok"),B3&" - "&A3&CHAR(10),"")&
IF(OR(D3="Bad",D3="Ok"),D3&" - "&C3&CHAR(10),"")&
IF(OR(F3="Bad",F3="Ok"),F3&" - "&E3,"")

edit - removed the CHAR(10) following the E3.

 

[SKooLZ]

Thanks for your speedy and helful response Asala42, however I have about 12 statuses which are about 4 words long so you can imagine how long that formula becomes, whereas I only have two status text strings that I want to instruct excel to do nothing.

Is there anyway to switch the formula round to do nothing if cells contain the text else concatenate all other statuses?

Thanks again..

 

[SKooLZ]

Just to report back, I have manged to switch the fomula round sucessfully to do what I wanted to thanks again Asala for you help! Kudos!!!

See below

=IF(OR(B3="Good",B3=""),"",B3&" - "&A3&CHAR(10)&IF(OR(D3="Good",D3=""),"",D3&" - "&C3&CHAR(10)&
IF(OR(F3="Good",F3=""),"",F3&" - "&E3)
</PRE>

 

[zack_]

Hi Gurus,

I'm new to this community/forum and I am hoping you can help me with the most easiest and efficient way (Formula or VB code) to get an excel spreadsheet to Concatenate data from mutiple cells only if those individual contain certain values as shown in the example table below. Example explained.

Concatenate all comments from A - F into column H only if the statuses = Bad or OK and ignore if the statuses = Good. These controlling values with be in text form as intimated above. Also, I would like each of the coment pairs to be listed on each row/line via &CHAR(10)&.

You urgent help is much appreciated.

Thanks in advance

Skoolz

[TABLE="width: 600"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Reason[/TD]
[TD]Status[/TD]
[TD]Reason[/TD]
[TD]Status[/TD]
[TD]Reason[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]No issues[/TD]
[TD]Good[/TD]
[TD]Major issues[/TD]
[TD]Bad[/TD]
[TD]Minor issues[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]Bad - Major issues
OK - Minor issues[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Good[/TD]
[TD][/TD]
[TD]Good[/TD]
[TD][/TD]
[TD]Good[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Minor issues[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]Good[/TD]
[TD][/TD]
[TD]Good[/TD]
[TD][/TD]
[TD]OK - Minor issues[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As a disclaimer I am no expert... just trying to get better at excel by trying to work other people's issues. I have this really long formula that I believe does what you would like...

=IF(OR(B56:G56 = "bad"),INDEX(B56:G56,,MATCH("bad",B56:G56,0))&"-" &INDEX(B56:G56,,MATCH("bad",B56:G56,0)-1),0) = Bad - Major Issues

In order to also encompass the "ok" you would need to basically copy that entire formula and put in "ok" in the false statement. Its a lot, and I have no doubt that there is a far more eloquent solution.