combining select text from independent cells when they agree

[polyocho]

Can I combine select text from independent responses in different excel cells, similar to how independent clauses are combined in English grammar using commas and conjunctions (Bob is bad. Billy is bad.-->Bob and Billy are bad.)?

I have a spreadsheet with values populated by a survey asking about whether something happens more frequently on (A) weekdays, (B) weekends, (C) vacation, or (D) away from home.

For each question there are two possible responses ("occurs" or "does not occur". I am left with spreadsheet, for example as follows:
A1: Does not occur less frequently during weekdays
B1: Does not occur less frequently during weekends
C1: Occurs less frequently on vacation
D1: Does not occur less frequently away from home

1) Can A1 - D1 be combined conditionally when they are similar, resulting in the following (as an example)
E1: Does not occur less frequently during weekdays, weekends or away from home.
F1: Occurs less frequently on vacation

2) Can the use of AND vs OR also be specified depending on the components of the text ("does not occur" vs. "occurs") such as in the following example:

A1: Occurs less frequently during weekdays
B1: Does not occur less frequently during weekends
C1: Occurs less frequently on vacation
D1: Does not occur less frequently away from home

This would then result in the following (for example):
E1: Does not occur less frequently during weekends or away from home.
F1: Occurs less frequently on weekdays and on vacation

Thanks in advance.

 

[venkat1926]

see this
first formulas in B1 and C1
they are copied down till row 4 (till data is there)

below the data in B7 see the formula
Sheet1

*	A	B	C
Does not occur less frequently during weekdays
Does not occur less frequently during weekends	Does not occur less frequently during	weekends
Occurs less frequently on vacation	Occurs less frequently on	vacation
Does not occur less frequently away from home	Does not occur less frequently away from	home
*	*	*
*	*	*
*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:326.4px;"><col style="width:382.4px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ffff00"]Does not occur less frequently during[/TD]
[TD="bgcolor: #ffff00"]weekdays [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #ffff00"]Does not occur less frequently during weekdays weekdays ,weekends[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(LEFT(A1,LEN(A1)-LEN(C1)-1)) C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) B7 =A1&" " &C1&","&C2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[polyocho]

Thank you. This seems like a viable solution. It seems this solution parses the sentence and then inserts the parsed words in column C into a sentence. The problems with this solution might be 1) it does not utilize any conjunctions AND or OR in the sentence structure 2) if all 4 answers are that "it occurs more frequently" this solution may leave a string of text that will just be left blank "Does not occur less frequently during"

 

[venkat1926]

item 1. you can use any other word(s) in concatenation formula in B7
item 2. not clear. do you want computer to interpret "more frequently" as "not less frequently". No computer is just a super fast idiot. It cannot interpret like an educated human being. You have to tell it to interpret like that.
for e.g.

A1 "yes"

in B1 type
=if(a1="yes","")

this means if A1 is yes B1 is no and if A1 is not yes B1 is blank
that is interpret Yes and no