Concatenate data from mutiple cells only if they contain certain values

SKooLZ

New Member
Joined
Oct 7, 2014
Messages
30
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

A
B
C
D
E
F
G
H
Reason
Status
Reason
Status
Reason
Status
Total
No issues
Good
Major issues
Bad
Minor issues
OK
Bad - Major issues
OK - Minor issues
Good
Good
Good
Minor issues
OK
Good
Good
OK - Minor issues

<TBODY>
</TBODY>
 
Last edited:

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Based on your specific layout I'd probably just brute force it.

Code:
=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.
 
Last edited:

SKooLZ

New Member
Joined
Oct 7, 2014
Messages
30
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

New Member
Joined
Oct 7, 2014
Messages
30
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_

Board Regular
Joined
Apr 18, 2014
Messages
79
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

ABCDEFGH
ReasonStatusReasonStatusReasonStatusTotal
No issuesGoodMajor issuesBadMinor issuesOKBad - Major issues
OK - Minor issues
GoodGoodGood
Minor issuesOKGoodGoodOK - Minor issues

<tbody>
</tbody>
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.
 

Forum statistics

Threads
1,082,151
Messages
5,363,449
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top