Concatenate If


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.
 
Ad

Advertisements

S

Shane Devenshire

Hi,

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

=IF(A1=1,B1,"")&IF(A2=1,B2,"")&....
 
T

Tom Hutchins

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
 
Ad

Advertisements

N

Novice

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top