IF and Concactenate Problem

[Helen Pippard]

Hello,

I was wondering if somone could help me with a puzzling problem

I have exported data from MS Access into MS Excel. Each time I export the data I may get variations in the number of rows which are populated.

1st Export

NAME VALUE
MACKIE 2
DERRY 2

Another time I may get

NAME VALUE
MACKIE 2
DERRY 2
TARRABT 2

They will all have the same values

In a completely different cell I want to get the text to read:

Mackie & Derry

or if i get 3 names I want it to read as:

Mackie, Derry & Tarrabt

I will be using this text by creating a named range so I can use the text as a bookmark in a word document

In my cell I currently trying to get the text to show properly is:

=IF(A3="",A2,CONCATENATE(A2 & " & ", A3)),IF(A4<>"", CONCACTENATE(A2 & " , ", A3 & " & ", A4)), CONCACTENATE(A2 & " & ",A3)

My aim is to only show the names which have appeared in the extract

From knowing my data, I know that I will only ever get a maximum of 5 names ever showing

Does anyone have any ideas?

Many Thanks
Helen

 

[Geoff Taylor]

=IF(A6<>0,(A2&", "&A3&", "&", "&A4&", "&A5&" and "&A6),IF(A5<>0,(A2&", "&A3&", "&", "&A4&" and "&A5),IF(A4<>0,(A2&", "&A3&", "&" and "&A4),IF(A3<>0,(A2&" and "&A3),A2))))

Try that. You don't actually need CONCATENATE

 

[Helen Pippard]

Amazing!!! , It works a treat!

Thank you so much....I could not see the wood through the trees at the end