Concatenating data

[Gary Thomson]

Hi,

I also have a range of cells, A1 through A100, which could
look like the following:

A1 abc
A2 e
A3
A4 mfg
..
..
A98 zc
A99
A100 md

i.e. each cell can contain up to 20 characters, but could
also be blank (i.e. cells A3 and A99).

Now, I want a another range of cells, say A101:A200, which
maps (and corresponds to the) data from the cells A1:A100
in such a way that:

for each cell that is blank, I want the CONCATENATION (I
think this is the correct word!) of all the cells that
have data.

So cells B1, B2, B4, B98 and B100 would display the same
data that A1, A2, A4, A98 and A100 did (as these original
cells contained data), but cells B3 and B99 would (because
cells A3 and A99 were blank) display "abcemfgzcmd".

Is there either (a) a function that can do this; or
(b) a VBA code I could use?

 

[JE McGimpsey]

one way:

B1: =IF(A1<>"",A1,MultiCat($A$1:$A$100))

where MultiCat is a User defined function:

Public Function MultiCat(ByRef rRng As Excel.Range, _
Optional ByVal sDelimiter As String = "") As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelimiter & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, 1 + Len(sDelimiter))
End Function

If you're not familiar with UDFs, see David McRitchie's Getting Started
with Macros and User Defined Functions:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[drabbacs]

in b1 =if(a1="","abcemfgzcmd",a1) copy down to b100.
Basically this says if a is blank put in the string other
wise copy what is in a.

Drabbacs