Concatenate If

  • Thread starter Thread starter Novice
  • Start date Start date
N

Novice

Can I concatenate cells in a coumn if cells in another column return a
particular value?

e.g.

A B
1 1 Text A
2 1 Text B
3 2 Text C
4 3 Text C
5 4 Text C
4 Text C

I would like the formula to find all instances of the value 1 (one) in
Column A and then concatenate only those Rows in Column B

result: Text A; Text B

I will need to repeat this exercise for 40 particular values.

I cannot use macros, addons, or VBA editor.
 
Hi,

You can do it, but its a rather tedious formuls:

=IF(A1=1,B1,"")&IF(A2=1,B2,"")&....
 
Here is one way....

In C1, enter
=IF(A1=1,B1,"")

In C2, enter
=IF(A2=1,IF(LEN(C1)>0,C1&"; "&B2,B2),C1)

Copy C2 down through all rows of data. The last row will have the final
result.

Hope this helps,

Hutch
 
Great. That worked for me. Next questions would be how do I eliminate
repeated text

My actual Formula is:
=IF($A$1=$O1,$D$1,"")&IF($A$2=$O1,$D$2,"")&IF($A$3=$O1,$D$3,"")&IF($A$4=$O1,$D$4,"")&IF($A$5=$O1,$D$5,"")&IF($A$6=$O1,$D$6,"")&IF($A$7=$O1,$D$7,"")

O1 is the reference number for me to look against.D:D is the text that I am
capturing, and A:A is the column I am basing my logic test off of.

I need to do two things. 1st, remove any duplicate names...
2nd, I need to place a hard return between them. I think that the new line
function is CHAR(10), but not sure where it goes in the formula, and I have
no clue as to how to eliminate or pass over duplicate text strings...

Thanks
 
Back
Top