Concantenation formula

[MIZDBLACK]

Hi,
I have a table of information containing names in column A and addresses in column B. Up to 4 names may reside at the same address. Can anybody give me a formula to put in col C to concantenate the names eg M Smith and J Smith and B Robson, if the addresses are the same?
Thanks.

 

[jasonb75]

Assuming same addresses in consecutive rows, something like

=IF(MATCH(B1,B:B,0)=ROW(),A1&IF(B2=B1," "&A2,"")&IF(B3=B1," "&A3,"")&IF(B4=B1," "&A4,""),"")

is probably the simplest way, only concatenates in the first row for the address.

The only other way I can think of if this doesn't suit, would be with an array concatenate UDF.

 

[MIZDBLACK]

Hey - that seems to work great. I just need the word 'and' or a comma inbetween the names - how can I achieve this please?

 

[jasonb75]

=IF(MATCH(B1,B:B,0)=ROW(),A1&IF(B2=B1," "&A2,"")&IF(B3=B1," "&A3,"")&IF(B4=B1," "&A4,""),"")

Just insert the word 'and' or a comme between each pair of red double quotes, remember also to include any spaces you require between the comma or word 'and' and the names.

 

[MIZDBLACK]

Thats fantastic!
Having done that, is there any way to include in the formula somewhere that- if there is only one name for the address, copy that - then I will have a complete column of data that I can paste back in .
Am I asking too much?
Yours hopefully

 

[jasonb75]

It should do that anyway, is it not doing working that way?

Have you edited the ranges to match your data?

 

[MIZDBLACK]

Sorry yes - you are right - I couldn't see the wood for the trees.
Thank you so much for your patient help!