Modifying A Formula

Hi Bob. Is there a limit on the length of the cell range I am trying to
concatenate.

I found that if I enter this:

=multiconcat(C4:C75,";")

all goes well. However if I increase to:

=multiconcat(C4:C76,";")

The formula returns #VALUE!.

Am I entering the formula incorrectly ?

Thanks Again.

"Bob Phillips" wrote:

> What a prat :-). And worst of all, I deleted it so I have had to re-do it
> .... sigh!
>
> Here it is
>
> Option Explicit
>
> Function MultiConcat(rng As Range, _
> Optional separator As String = ",")
> Dim cell As Range
> Dim cSize As Long
> Dim fByRows As Boolean
> Dim fNotFirst As Boolean
> Dim aryData
> Dim vKey1, vkey2
> Dim i As Long, j As Long
> Dim stemp
>
> 'validate input
> If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
> MultiConcat = "Select a single column or row array"
> Exit Function
> ElseIf rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
> MultiConcat = "Oly one cell selected"
> ElseIf rng.Rows.Count > 1 Then
> fByRows = True
> cSize = rng.Rows.Count
> Else
> cSize = rng.Columns.Count
> End If
>
> 'initialise all the checking data
> vKey1 = rng(1, 1).Offset(0, -1).Value
> vkey2 = rng(1, 1).Offset(0, 1).Value
> 'allow an extra 2 for the check values
> ReDim aryData(1 To cSize, 1 To cSize + 2)
> aryData(1, 1) = vKey1
> aryData(1, 2) = vkey2
> i = 1: j = 3
> stemp = ""
> For Each cell In rng
> If cell.Value <> "" Then
> If cell.Offset(0, -1) = vKey1 And cell.Offset(0, 1).Value =
> vkey2 Then
> If fNotFirst Then
> stemp = stemp & separator & cell.Value
> Else
> stemp = cell.Value
> fNotFirst = True
> End If
> Else
> aryData(i, j) = stemp
> stemp = ""
> 'clear down the rest of this dimension of the array
> If j < UBound(aryData, 2) Then
> For j = j + 1 To UBound(aryData, 2)
> aryData(i, j) = ""
> Next j
> End If
> stemp = cell.Value
> aryData(i, 1) = vKey1
> aryData(i, 2) = vkey2
> vKey1 = cell.Offset(0, -1).Value
> vkey2 = cell.Offset(0, 1).Value
> i = i + 1
> j = 3
> End If
> End If
> Next cell
>
> 'pick up o/s data
> aryData(i, 1) = vKey1
> aryData(i, 2) = vkey2
> aryData(i, j) = stemp
> 'clear down the rest of this dimension of the array
> If j < UBound(aryData, 2) Then
> For j = j + 1 To UBound(aryData, 2)
> aryData(i, j) = ""
> Next j
> End If
>
> 'clear down the rest of the array
> If i < UBound(aryData, 1) Then
> For i = i + 1 To UBound(aryData, 1)
> For j = 1 To UBound(aryData, 2)
> aryData(i, j) = ""
> Next j
> Next i
> End If
>
> MultiConcat = aryData
> End Function
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:790A68D6-0B9B-417A-9AFC-BF7750FF5C52@microsoft.com...
> > Thanks. Looking for the UDF in your reply ? Regards.
> >
> > "Bob Phillips" wrote:
> >
> > > Carl,
> > >
> > >
> > > a little different that the layout that you asked for, but it does work.
> It
> > > will look like
> > >
> > > 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> > > 09:30:05 || B || S7;C2:A5
> > > 09:30:15 || B || MA;G7;A6;N4
> > > 09:30:15 || A || K0;B7;CK;SC;AK;AJ
> > >
> > > Add the UDF below to a standard code module.
> > >
> > > Then in the worksheet where you want the formula, select an array of
> cells
> > > that will be at least as many columns and rows as you want, hit F2, and
> in
> > > the formula bar, enter your formula, such as
> > > =MultiConcat(C5:C256,";")
> > > which is an array formula, so commit with Ctrl-Shift-Enter.
> > >
> > > BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
> > > unique and filtered options as well.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > > > THanks again. Was not sure how to use the link below. I sent you an
> > > example
> > > > directly. Appreciate your help.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > You could post an example at http://www.savefile.com/filehost/ and
> let
> > > me
> > > > > know your URL. But show expected results as well.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > > > Thanks again Bob. The fomating of this post makes it tough to
> explain.
> > > > > > Perhaps I could send you an example spreadsheet. If so, just let
> me
> > > know
> > > > > > where to send it.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > Carl,
> > > > > > >
> > > > > > > Sorry to keep pushing mate, but that data looks scrambled to me.
> I
> > > > > cannot
> > > > > > > tell whether Row 1 is a label in a cell, signifies a row or
> what,
> > > And is
> > > > > the
> > > > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could
> relay
> > > > > before
> > > > > > > and after I can give it a shot, but at present I am not clear
> what
> > > is
> > > > > > > required.
> > > > > > >
> > > > > > > Another thing, is this to work on a set of rows, or just one? If
> the
> > >
> > > > > former,
> > > > > > > can you give an example with more than one row of results?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > > > Hi Bob. Thank you trying to help me.
> > > > > > > >
> > > > > > > > The function concatenates a selected range. I tried to show
> the
> > > > > expected
> > > > > > > > results in the last table of my post:
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > >
> > > > > > > > For example in B2, I need a formula to look at the value in
> $A2
> > > and
> > > > > $B1,
> > > > > > > > find all values in the data table that map up to these 2
> cells,
> > > and
> > > > > > > > concatenate them with a ";" as the separator.
> > > > > > > >
> > > > > > > > Sorry I can't explain this any better.
> > > > > > > >
> > > > > > > > Thank you again.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > > I can see that ConcatUF is a UDF of yours, which you don't
> show,
> > > but
> > > > > I
> > > > > > > don't
> > > > > > > > > get what the formula currently does or what you want.
> > > > > > > > >
> > > > > > > > > Can you try re-posting with the start data (use a character
> as a
> > > > > cell
> > > > > > > > > delimiter like ||) and expected results?
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > RP
> > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > > > I would like to use this formula
> > > > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > > > in the table below.
> > > > > > > > > >
> > > > > > > > > > ColA ColB ColC
> > > > > > > > > > Row1 Time A B
> > > > > > > > > > Row2 10:30:00 AM
> > > > > > > > > > Row3 9:30:00 AM
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > My data table is setup like this:
> > > > > > > > > >
> > > > > > > > > > ColA ColB ColC
> > > > > > > > > > Row1 Time A B
> > > > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > > > >
> > > > > > > > > > Can the formula be modified to perform the concatenation
> like
> > > > > this:
> > > > > > > > > >
> > > > > > > > > > ColA ColB ColC
> > > > > > > > > > Row1 Time A B
> > > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > > >
> > > > > > > > > > Thank you in advance.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Thanks. Looking for the UDF in your reply ? Regards.

"Bob Phillips" wrote:

> Carl,
>
>
> a little different that the layout that you asked for, but it does work. It
> will look like
>
> 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> 09:30:05 || B || S7;C2:A5
> 09:30:15 || B || MA;G7;A6;N4
> 09:30:15 || A || K0;B7;CK;SC;AK;AJ
>
> Add the UDF below to a standard code module.
>
> Then in the worksheet where you want the formula, select an array of cells
> that will be at least as many columns and rows as you want, hit F2, and in
> the formula bar, enter your formula, such as
> =MultiConcat(C5:C256,";")
> which is an array formula, so commit with Ctrl-Shift-Enter.
>
> BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
> unique and filtered options as well.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > THanks again. Was not sure how to use the link below. I sent you an
> example
> > directly. Appreciate your help.
> >
> > "Bob Phillips" wrote:
> >
> > > You could post an example at http://www.savefile.com/filehost/ and let
> me
> > > know your URL. But show expected results as well.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > Thanks again Bob. The fomating of this post makes it tough to explain.
> > > > Perhaps I could send you an example spreadsheet. If so, just let me
> know
> > > > where to send it.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Carl,
> > > > >
> > > > > Sorry to keep pushing mate, but that data looks scrambled to me. I
> > > cannot
> > > > > tell whether Row 1 is a label in a cell, signifies a row or what,
> And is
> > > the
> > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
> > > before
> > > > > and after I can give it a shot, but at present I am not clear what
> is
> > > > > required.
> > > > >
> > > > > Another thing, is this to work on a set of rows, or just one? If the
>
> > > former,
> > > > > can you give an example with more than one row of results?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > Hi Bob. Thank you trying to help me.
> > > > > >
> > > > > > The function concatenates a selected range. I tried to show the
> > > expected
> > > > > > results in the last table of my post:
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > >
> > > > > > For example in B2, I need a formula to look at the value in $A2
> and
> > > $B1,
> > > > > > find all values in the data table that map up to these 2 cells,
> and
> > > > > > concatenate them with a ";" as the separator.
> > > > > >
> > > > > > Sorry I can't explain this any better.
> > > > > >
> > > > > > Thank you again.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > I can see that ConcatUF is a UDF of yours, which you don't show,
> but
> > > I
> > > > > don't
> > > > > > > get what the formula currently does or what you want.
> > > > > > >
> > > > > > > Can you try re-posting with the start data (use a character as a
> > > cell
> > > > > > > delimiter like ||) and expected results?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > I would like to use this formula
> > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > in the table below.
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 10:30:00 AM
> > > > > > > > Row3 9:30:00 AM
> > > > > > > >
> > > > > > > >
> > > > > > > > My data table is setup like this:
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > >
> > > > > > > > Can the formula be modified to perform the concatenation like
> > > this:
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > >
> > > > > > > > Thank you in advance.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Carl,


a little different that the layout that you asked for, but it does work. It
will look like

09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
09:30:05 || B || S7;C2:A5
09:30:15 || B || MA;G7;A6;N4
09:30:15 || A || K0;B7;CK;SC;AK;AJ

Add the UDF below to a standard code module.

Then in the worksheet where you want the formula, select an array of cells
that will be at least as many columns and rows as you want, hit F2, and in
the formula bar, enter your formula, such as
=MultiConcat(C5:C256,";")
which is an array formula, so commit with Ctrl-Shift-Enter.

BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
unique and filtered options as well.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> THanks again. Was not sure how to use the link below. I sent you an
example
> directly. Appreciate your help.
>
> "Bob Phillips" wrote:
>
> > You could post an example at http://www.savefile.com/filehost/ and let
me
> > know your URL. But show expected results as well.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > Thanks again Bob. The fomating of this post makes it tough to explain.
> > > Perhaps I could send you an example spreadsheet. If so, just let me
know
> > > where to send it.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Carl,
> > > >
> > > > Sorry to keep pushing mate, but that data looks scrambled to me. I
> > cannot
> > > > tell whether Row 1 is a label in a cell, signifies a row or what,
And is
> > the
> > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
> > before
> > > > and after I can give it a shot, but at present I am not clear what
is
> > > > required.
> > > >
> > > > Another thing, is this to work on a set of rows, or just one? If the

> > former,
> > > > can you give an example with more than one row of results?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > Hi Bob. Thank you trying to help me.
> > > > >
> > > > > The function concatenates a selected range. I tried to show the
> > expected
> > > > > results in the last table of my post:
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 10:30:00 AM U4 Q1
> > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > >
> > > > > For example in B2, I need a formula to look at the value in $A2
and
> > $B1,
> > > > > find all values in the data table that map up to these 2 cells,
and
> > > > > concatenate them with a ";" as the separator.
> > > > >
> > > > > Sorry I can't explain this any better.
> > > > >
> > > > > Thank you again.
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I can see that ConcatUF is a UDF of yours, which you don't show,
but
> > I
> > > > don't
> > > > > > get what the formula currently does or what you want.
> > > > > >
> > > > > > Can you try re-posting with the start data (use a character as a
> > cell
> > > > > > delimiter like ||) and expected results?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > I would like to use this formula
> > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > in the table below.
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 10:30:00 AM
> > > > > > > Row3 9:30:00 AM
> > > > > > >
> > > > > > >
> > > > > > > My data table is setup like this:
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > >
> > > > > > > Can the formula be modified to perform the concatenation like
> > this:
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > >
> > > > > > > Thank you in advance.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

THanks again. Was not sure how to use the link below. I sent you an example
directly. Appreciate your help.

"Bob Phillips" wrote:

> You could post an example at http://www.savefile.com/filehost/ and let me
> know your URL. But show expected results as well.
>
> --
> HTH
>
> Bob Phillips
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > Thanks again Bob. The fomating of this post makes it tough to explain.
> > Perhaps I could send you an example spreadsheet. If so, just let me know
> > where to send it.
> >
> > "Bob Phillips" wrote:
> >
> > > Carl,
> > >
> > > Sorry to keep pushing mate, but that data looks scrambled to me. I
> cannot
> > > tell whether Row 1 is a label in a cell, signifies a row or what, And is
> the
> > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
> before
> > > and after I can give it a shot, but at present I am not clear what is
> > > required.
> > >
> > > Another thing, is this to work on a set of rows, or just one? If the
> former,
> > > can you give an example with more than one row of results?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > Hi Bob. Thank you trying to help me.
> > > >
> > > > The function concatenates a selected range. I tried to show the
> expected
> > > > results in the last table of my post:
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 10:30:00 AM U4 Q1
> > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > >
> > > > For example in B2, I need a formula to look at the value in $A2 and
> $B1,
> > > > find all values in the data table that map up to these 2 cells, and
> > > > concatenate them with a ";" as the separator.
> > > >
> > > > Sorry I can't explain this any better.
> > > >
> > > > Thank you again.
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > I can see that ConcatUF is a UDF of yours, which you don't show, but
> I
> > > don't
> > > > > get what the formula currently does or what you want.
> > > > >
> > > > > Can you try re-posting with the start data (use a character as a
> cell
> > > > > delimiter like ||) and expected results?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > I would like to use this formula
> > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > in the table below.
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 10:30:00 AM
> > > > > > Row3 9:30:00 AM
> > > > > >
> > > > > >
> > > > > > My data table is setup like this:
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 9:30:00 AM E1 GG
> > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > Row4 10:30:00 AM U4 Q1
> > > > > >
> > > > > > Can the formula be modified to perform the concatenation like
> this:
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > >
> > > > > > Thank you in advance.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

What a prat :-). And worst of all, I deleted it so I have had to re-do it
.... sigh!

Here it is

Option Explicit

Function MultiConcat(rng As Range, _
Optional separator As String = ",")
Dim cell As Range
Dim cSize As Long
Dim fByRows As Boolean
Dim fNotFirst As Boolean
Dim aryData
Dim vKey1, vkey2
Dim i As Long, j As Long
Dim stemp

'validate input
If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
MultiConcat = "Select a single column or row array"
Exit Function
ElseIf rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
MultiConcat = "Oly one cell selected"
ElseIf rng.Rows.Count > 1 Then
fByRows = True
cSize = rng.Rows.Count
Else
cSize = rng.Columns.Count
End If

'initialise all the checking data
vKey1 = rng(1, 1).Offset(0, -1).Value
vkey2 = rng(1, 1).Offset(0, 1).Value
'allow an extra 2 for the check values
ReDim aryData(1 To cSize, 1 To cSize + 2)
aryData(1, 1) = vKey1
aryData(1, 2) = vkey2
i = 1: j = 3
stemp = ""
For Each cell In rng
If cell.Value <> "" Then
If cell.Offset(0, -1) = vKey1 And cell.Offset(0, 1).Value =
vkey2 Then
If fNotFirst Then
stemp = stemp & separator & cell.Value
Else
stemp = cell.Value
fNotFirst = True
End If
Else
aryData(i, j) = stemp
stemp = ""
'clear down the rest of this dimension of the array
If j < UBound(aryData, 2) Then
For j = j + 1 To UBound(aryData, 2)
aryData(i, j) = ""
Next j
End If
stemp = cell.Value
aryData(i, 1) = vKey1
aryData(i, 2) = vkey2
vKey1 = cell.Offset(0, -1).Value
vkey2 = cell.Offset(0, 1).Value
i = i + 1
j = 3
End If
End If
Next cell

'pick up o/s data
aryData(i, 1) = vKey1
aryData(i, 2) = vkey2
aryData(i, j) = stemp
'clear down the rest of this dimension of the array
If j < UBound(aryData, 2) Then
For j = j + 1 To UBound(aryData, 2)
aryData(i, j) = ""
Next j
End If

'clear down the rest of the array
If i < UBound(aryData, 1) Then
For i = i + 1 To UBound(aryData, 1)
For j = 1 To UBound(aryData, 2)
aryData(i, j) = ""
Next j
Next i
End If

MultiConcat = aryData
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:790A68D6-0B9B-417A-9AFC-BF7750FF5C52@microsoft.com...
> Thanks. Looking for the UDF in your reply ? Regards.
>
> "Bob Phillips" wrote:
>
> > Carl,
> >
> >
> > a little different that the layout that you asked for, but it does work.
It
> > will look like
> >
> > 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> > 09:30:05 || B || S7;C2:A5
> > 09:30:15 || B || MA;G7;A6;N4
> > 09:30:15 || A || K0;B7;CK;SC;AK;AJ
> >
> > Add the UDF below to a standard code module.
> >
> > Then in the worksheet where you want the formula, select an array of
cells
> > that will be at least as many columns and rows as you want, hit F2, and
in
> > the formula bar, enter your formula, such as
> > =MultiConcat(C5:C256,";")
> > which is an array formula, so commit with Ctrl-Shift-Enter.
> >
> > BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
> > unique and filtered options as well.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > > THanks again. Was not sure how to use the link below. I sent you an
> > example
> > > directly. Appreciate your help.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > You could post an example at http://www.savefile.com/filehost/ and
let
> > me
> > > > know your URL. But show expected results as well.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > > Thanks again Bob. The fomating of this post makes it tough to
explain.
> > > > > Perhaps I could send you an example spreadsheet. If so, just let
me
> > know
> > > > > where to send it.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Carl,
> > > > > >
> > > > > > Sorry to keep pushing mate, but that data looks scrambled to me.
I
> > > > cannot
> > > > > > tell whether Row 1 is a label in a cell, signifies a row or
what,
> > And is
> > > > the
> > > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could
relay
> > > > before
> > > > > > and after I can give it a shot, but at present I am not clear
what
> > is
> > > > > > required.
> > > > > >
> > > > > > Another thing, is this to work on a set of rows, or just one? If
the
> >
> > > > former,
> > > > > > can you give an example with more than one row of results?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > > Hi Bob. Thank you trying to help me.
> > > > > > >
> > > > > > > The function concatenates a selected range. I tried to show
the
> > > > expected
> > > > > > > results in the last table of my post:
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > >
> > > > > > > For example in B2, I need a formula to look at the value in
$A2
> > and
> > > > $B1,
> > > > > > > find all values in the data table that map up to these 2
cells,
> > and
> > > > > > > concatenate them with a ";" as the separator.
> > > > > > >
> > > > > > > Sorry I can't explain this any better.
> > > > > > >
> > > > > > > Thank you again.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > I can see that ConcatUF is a UDF of yours, which you don't
show,
> > but
> > > > I
> > > > > > don't
> > > > > > > > get what the formula currently does or what you want.
> > > > > > > >
> > > > > > > > Can you try re-posting with the start data (use a character
as a
> > > > cell
> > > > > > > > delimiter like ||) and expected results?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > > I would like to use this formula
> > > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > > in the table below.
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 10:30:00 AM
> > > > > > > > > Row3 9:30:00 AM
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > My data table is setup like this:
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > > >
> > > > > > > > > Can the formula be modified to perform the concatenation
like
> > > > this:
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > >
> > > > > > > > > Thank you in advance.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

You could post an example at http://www.savefile.com/filehost/ and let me
know your URL. But show expected results as well.

--
HTH

Bob Phillips

"carl" <carl@discussions.microsoft.com> wrote in message
news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> Thanks again Bob. The fomating of this post makes it tough to explain.
> Perhaps I could send you an example spreadsheet. If so, just let me know
> where to send it.
>
> "Bob Phillips" wrote:
>
> > Carl,
> >
> > Sorry to keep pushing mate, but that data looks scrambled to me. I
cannot
> > tell whether Row 1 is a label in a cell, signifies a row or what, And is
the
> > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
before
> > and after I can give it a shot, but at present I am not clear what is
> > required.
> >
> > Another thing, is this to work on a set of rows, or just one? If the
former,
> > can you give an example with more than one row of results?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > Hi Bob. Thank you trying to help me.
> > >
> > > The function concatenates a selected range. I tried to show the
expected
> > > results in the last table of my post:
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 10:30:00 AM U4 Q1
> > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > >
> > > For example in B2, I need a formula to look at the value in $A2 and
$B1,
> > > find all values in the data table that map up to these 2 cells, and
> > > concatenate them with a ";" as the separator.
> > >
> > > Sorry I can't explain this any better.
> > >
> > > Thank you again.
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I can see that ConcatUF is a UDF of yours, which you don't show, but
I
> > don't
> > > > get what the formula currently does or what you want.
> > > >
> > > > Can you try re-posting with the start data (use a character as a
cell
> > > > delimiter like ||) and expected results?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > I would like to use this formula
> > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > in the table below.
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 10:30:00 AM
> > > > > Row3 9:30:00 AM
> > > > >
> > > > >
> > > > > My data table is setup like this:
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 9:30:00 AM E1 GG
> > > > > Row3 9:30:00 AM Y0 D1
> > > > > Row4 10:30:00 AM U4 Q1
> > > > >
> > > > > Can the formula be modified to perform the concatenation like
this:
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 10:30:00 AM U4 Q1
> > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > >
> > > > > Thank you in advance.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Thanks again Bob. The fomating of this post makes it tough to explain.
Perhaps I could send you an example spreadsheet. If so, just let me know
where to send it.

"Bob Phillips" wrote:

> Carl,
>
> Sorry to keep pushing mate, but that data looks scrambled to me. I cannot
> tell whether Row 1 is a label in a cell, signifies a row or what, And is the
> 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay before
> and after I can give it a shot, but at present I am not clear what is
> required.
>
> Another thing, is this to work on a set of rows, or just one? If the former,
> can you give an example with more than one row of results?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > Hi Bob. Thank you trying to help me.
> >
> > The function concatenates a selected range. I tried to show the expected
> > results in the last table of my post:
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 10:30:00 AM U4 Q1
> > Row3 9:30:00 AM E1;Y0 D1;Q1
> >
> > For example in B2, I need a formula to look at the value in $A2 and $B1,
> > find all values in the data table that map up to these 2 cells, and
> > concatenate them with a ";" as the separator.
> >
> > Sorry I can't explain this any better.
> >
> > Thank you again.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > I can see that ConcatUF is a UDF of yours, which you don't show, but I
> don't
> > > get what the formula currently does or what you want.
> > >
> > > Can you try re-posting with the start data (use a character as a cell
> > > delimiter like ||) and expected results?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > I would like to use this formula
> "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > in the table below.
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 10:30:00 AM
> > > > Row3 9:30:00 AM
> > > >
> > > >
> > > > My data table is setup like this:
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 9:30:00 AM E1 GG
> > > > Row3 9:30:00 AM Y0 D1
> > > > Row4 10:30:00 AM U4 Q1
> > > >
> > > > Can the formula be modified to perform the concatenation like this:
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 10:30:00 AM U4 Q1
> > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > >
> > > > Thank you in advance.
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Carl,

Sorry to keep pushing mate, but that data looks scrambled to me. I cannot
tell whether Row 1 is a label in a cell, signifies a row or what, And is the
10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay before
and after I can give it a shot, but at present I am not clear what is
required.

Another thing, is this to work on a set of rows, or just one? If the former,
can you give an example with more than one row of results?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> Hi Bob. Thank you trying to help me.
>
> The function concatenates a selected range. I tried to show the expected
> results in the last table of my post:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM U4 Q1
> Row3 9:30:00 AM E1;Y0 D1;Q1
>
> For example in B2, I need a formula to look at the value in $A2 and $B1,
> find all values in the data table that map up to these 2 cells, and
> concatenate them with a ";" as the separator.
>
> Sorry I can't explain this any better.
>
> Thank you again.
>
>
>
> "Bob Phillips" wrote:
>
> > I can see that ConcatUF is a UDF of yours, which you don't show, but I
don't
> > get what the formula currently does or what you want.
> >
> > Can you try re-posting with the start data (use a character as a cell
> > delimiter like ||) and expected results?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > I would like to use this formula
"=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > in the table below.
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 10:30:00 AM
> > > Row3 9:30:00 AM
> > >
> > >
> > > My data table is setup like this:
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 9:30:00 AM E1 GG
> > > Row3 9:30:00 AM Y0 D1
> > > Row4 10:30:00 AM U4 Q1
> > >
> > > Can the formula be modified to perform the concatenation like this:
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 10:30:00 AM U4 Q1
> > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > >
> > > Thank you in advance.
> > >
> > >
> > >
> >
> >
> >

Hi Bob. Thank you trying to help me.

The function concatenates a selected range. I tried to show the expected
results in the last table of my post:

ColA ColB ColC
Row1 Time A B
Row2 10:30:00 AM U4 Q1
Row3 9:30:00 AM E1;Y0 D1;Q1

For example in B2, I need a formula to look at the value in $A2 and $B1,
find all values in the data table that map up to these 2 cells, and
concatenate them with a ";" as the separator.

Sorry I can't explain this any better.

Thank you again.



"Bob Phillips" wrote:

> I can see that ConcatUF is a UDF of yours, which you don't show, but I don't
> get what the formula currently does or what you want.
>
> Can you try re-posting with the start data (use a character as a cell
> delimiter like ||) and expected results?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > in the table below.
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 10:30:00 AM
> > Row3 9:30:00 AM
> >
> >
> > My data table is setup like this:
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 9:30:00 AM E1 GG
> > Row3 9:30:00 AM Y0 D1
> > Row4 10:30:00 AM U4 Q1
> >
> > Can the formula be modified to perform the concatenation like this:
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 10:30:00 AM U4 Q1
> > Row3 9:30:00 AM E1;Y0 D1;Q1
> >
> > Thank you in advance.
> >
> >
> >
>
>
>

Hi Carl,

Yeah, it must be the size of the array that is the problem.

I have dimensioned the array both ways at maximum size, this is overkill and
a problem here. Try changing this line


ReDim aryData(1 To cSize, 1 To cSize + 2)

to

ReDim aryData(1 To cSize, 1 To 12)

or a number 2 greater that the maximum number of values in C for any date,
letter combination.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:0F2D34EC-6655-4F61-A8FA-A3774A166A63@microsoft.com...
> Hi Bob. Is there a limit on the length of the cell range I am trying to
> concatenate.
>
> I found that if I enter this:
>
> =multiconcat(C4:C75,";")
>
> all goes well. However if I increase to:
>
> =multiconcat(C4:C76,";")
>
> The formula returns #VALUE!.
>
> Am I entering the formula incorrectly ?
>
> Thanks Again.
>
> "Bob Phillips" wrote:
>
> > What a prat :-). And worst of all, I deleted it so I have had to re-do
it
> > .... sigh!
> >
> > Here it is
> >
> > Option Explicit
> >
> > Function MultiConcat(rng As Range, _
> > Optional separator As String = ",")
> > Dim cell As Range
> > Dim cSize As Long
> > Dim fByRows As Boolean
> > Dim fNotFirst As Boolean
> > Dim aryData
> > Dim vKey1, vkey2
> > Dim i As Long, j As Long
> > Dim stemp
> >
> > 'validate input
> > If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
> > MultiConcat = "Select a single column or row array"
> > Exit Function
> > ElseIf rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
> > MultiConcat = "Oly one cell selected"
> > ElseIf rng.Rows.Count > 1 Then
> > fByRows = True
> > cSize = rng.Rows.Count
> > Else
> > cSize = rng.Columns.Count
> > End If
> >
> > 'initialise all the checking data
> > vKey1 = rng(1, 1).Offset(0, -1).Value
> > vkey2 = rng(1, 1).Offset(0, 1).Value
> > 'allow an extra 2 for the check values
> > ReDim aryData(1 To cSize, 1 To cSize + 2)
> > aryData(1, 1) = vKey1
> > aryData(1, 2) = vkey2
> > i = 1: j = 3
> > stemp = ""
> > For Each cell In rng
> > If cell.Value <> "" Then
> > If cell.Offset(0, -1) = vKey1 And cell.Offset(0, 1).Value =
> > vkey2 Then
> > If fNotFirst Then
> > stemp = stemp & separator & cell.Value
> > Else
> > stemp = cell.Value
> > fNotFirst = True
> > End If
> > Else
> > aryData(i, j) = stemp
> > stemp = ""
> > 'clear down the rest of this dimension of the array
> > If j < UBound(aryData, 2) Then
> > For j = j + 1 To UBound(aryData, 2)
> > aryData(i, j) = ""
> > Next j
> > End If
> > stemp = cell.Value
> > aryData(i, 1) = vKey1
> > aryData(i, 2) = vkey2
> > vKey1 = cell.Offset(0, -1).Value
> > vkey2 = cell.Offset(0, 1).Value
> > i = i + 1
> > j = 3
> > End If
> > End If
> > Next cell
> >
> > 'pick up o/s data
> > aryData(i, 1) = vKey1
> > aryData(i, 2) = vkey2
> > aryData(i, j) = stemp
> > 'clear down the rest of this dimension of the array
> > If j < UBound(aryData, 2) Then
> > For j = j + 1 To UBound(aryData, 2)
> > aryData(i, j) = ""
> > Next j
> > End If
> >
> > 'clear down the rest of the array
> > If i < UBound(aryData, 1) Then
> > For i = i + 1 To UBound(aryData, 1)
> > For j = 1 To UBound(aryData, 2)
> > aryData(i, j) = ""
> > Next j
> > Next i
> > End If
> >
> > MultiConcat = aryData
> > End Function
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:790A68D6-0B9B-417A-9AFC-BF7750FF5C52@microsoft.com...
> > > Thanks. Looking for the UDF in your reply ? Regards.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Carl,
> > > >
> > > >
> > > > a little different that the layout that you asked for, but it does
work.
> > It
> > > > will look like
> > > >
> > > > 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> > > > 09:30:05 || B || S7;C2:A5
> > > > 09:30:15 || B || MA;G7;A6;N4
> > > > 09:30:15 || A || K0;B7;CK;SC;AK;AJ
> > > >
> > > > Add the UDF below to a standard code module.
> > > >
> > > > Then in the worksheet where you want the formula, select an array of
> > cells
> > > > that will be at least as many columns and rows as you want, hit F2,
and
> > in
> > > > the formula bar, enter your formula, such as
> > > > =MultiConcat(C5:C256,";")
> > > > which is an array formula, so commit with Ctrl-Shift-Enter.
> > > >
> > > > BTW I found the original UDF, a gem from Bernie Dietrick. I removed
the
> > > > unique and filtered options as well.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > > > > THanks again. Was not sure how to use the link below. I sent you
an
> > > > example
> > > > > directly. Appreciate your help.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > You could post an example at http://www.savefile.com/filehost/
and
> > let
> > > > me
> > > > > > know your URL. But show expected results as well.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > > > > Thanks again Bob. The fomating of this post makes it tough to
> > explain.
> > > > > > > Perhaps I could send you an example spreadsheet. If so, just
let
> > me
> > > > know
> > > > > > > where to send it.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > Carl,
> > > > > > > >
> > > > > > > > Sorry to keep pushing mate, but that data looks scrambled to
me.
> > I
> > > > > > cannot
> > > > > > > > tell whether Row 1 is a label in a cell, signifies a row or
> > what,
> > > > And is
> > > > > > the
> > > > > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could
> > relay
> > > > > > before
> > > > > > > > and after I can give it a shot, but at present I am not
clear
> > what
> > > > is
> > > > > > > > required.
> > > > > > > >
> > > > > > > > Another thing, is this to work on a set of rows, or just
one? If
> > the
> > > >
> > > > > > former,
> > > > > > > > can you give an example with more than one row of results?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > > > > Hi Bob. Thank you trying to help me.
> > > > > > > > >
> > > > > > > > > The function concatenates a selected range. I tried to
show
> > the
> > > > > > expected
> > > > > > > > > results in the last table of my post:
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > >
> > > > > > > > > For example in B2, I need a formula to look at the value
in
> > $A2
> > > > and
> > > > > > $B1,
> > > > > > > > > find all values in the data table that map up to these 2
> > cells,
> > > > and
> > > > > > > > > concatenate them with a ";" as the separator.
> > > > > > > > >
> > > > > > > > > Sorry I can't explain this any better.
> > > > > > > > >
> > > > > > > > > Thank you again.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > >
> > > > > > > > > > I can see that ConcatUF is a UDF of yours, which you
don't
> > show,
> > > > but
> > > > > > I
> > > > > > > > don't
> > > > > > > > > > get what the formula currently does or what you want.
> > > > > > > > > >
> > > > > > > > > > Can you try re-posting with the start data (use a
character
> > as a
> > > > > > cell
> > > > > > > > > > delimiter like ||) and expected results?
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > RP
> > > > > > > > > > (remove nothere from the email address if mailing
direct)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > > >
news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > > > > I would like to use this formula
> > > > > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > > > > in the table below.
> > > > > > > > > > >
> > > > > > > > > > > ColA ColB ColC
> > > > > > > > > > > Row1 Time A B
> > > > > > > > > > > Row2 10:30:00 AM
> > > > > > > > > > > Row3 9:30:00 AM
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > My data table is setup like this:
> > > > > > > > > > >
> > > > > > > > > > > ColA ColB ColC
> > > > > > > > > > > Row1 Time A B
> > > > > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > > > > >
> > > > > > > > > > > Can the formula be modified to perform the
concatenation
> > like
> > > > > > this:
> > > > > > > > > > >
> > > > > > > > > > > ColA ColB ColC
> > > > > > > > > > > Row1 Time A B
> > > > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > > > >
> > > > > > > > > > > Thank you in advance.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

I can see that ConcatUF is a UDF of yours, which you don't show, but I don't
get what the formula currently does or what you want.

Can you try re-posting with the start data (use a character as a cell
delimiter like ||) and expected results?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> in the table below.
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM
> Row3 9:30:00 AM
>
>
> My data table is setup like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 9:30:00 AM E1 GG
> Row3 9:30:00 AM Y0 D1
> Row4 10:30:00 AM U4 Q1
>
> Can the formula be modified to perform the concatenation like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM U4 Q1
> Row3 9:30:00 AM E1;Y0 D1;Q1
>
> Thank you in advance.
>
>
>

Its a User Defined Function. You need to provide the code for it. The UF in
the name of the function says that its a user function. Press Alt F11 to
view the code. Check the standard modules on the left and see where the code
is and post it.

Mangesh



"carl" <carl@discussions.microsoft.com> wrote in message
news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> in the table below.
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM
> Row3 9:30:00 AM
>
>
> My data table is setup like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 9:30:00 AM E1 GG
> Row3 9:30:00 AM Y0 D1
> Row4 10:30:00 AM U4 Q1
>
> Can the formula be modified to perform the concatenation like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM U4 Q1
> Row3 9:30:00 AM E1;Y0 D1;Q1
>
> Thank you in advance.
>
>
>

Hi Carl,

Yeah, it must be the size of the array that is the problem.

I have dimensioned the array both ways at maximum size, this is overkill and
a problem here. Try changing this line


ReDim aryData(1 To cSize, 1 To cSize + 2)

to

ReDim aryData(1 To cSize, 1 To 12)

or a number 2 greater that the maximum number of values in C for any date,
letter combination.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:0F2D34EC-6655-4F61-A8FA-A3774A166A63@microsoft.com...
> Hi Bob. Is there a limit on the length of the cell range I am trying to
> concatenate.
>
> I found that if I enter this:
>
> =multiconcat(C4:C75,";")
>
> all goes well. However if I increase to:
>
> =multiconcat(C4:C76,";")
>
> The formula returns #VALUE!.
>
> Am I entering the formula incorrectly ?
>
> Thanks Again.
>
> "Bob Phillips" wrote:
>
> > What a prat :-). And worst of all, I deleted it so I have had to re-do
it
> > .... sigh!
> >
> > Here it is
> >
> > Option Explicit
> >
> > Function MultiConcat(rng As Range, _
> > Optional separator As String = ",")
> > Dim cell As Range
> > Dim cSize As Long
> > Dim fByRows As Boolean
> > Dim fNotFirst As Boolean
> > Dim aryData
> > Dim vKey1, vkey2
> > Dim i As Long, j As Long
> > Dim stemp
> >
> > 'validate input
> > If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
> > MultiConcat = "Select a single column or row array"
> > Exit Function
> > ElseIf rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
> > MultiConcat = "Oly one cell selected"
> > ElseIf rng.Rows.Count > 1 Then
> > fByRows = True
> > cSize = rng.Rows.Count
> > Else
> > cSize = rng.Columns.Count
> > End If
> >
> > 'initialise all the checking data
> > vKey1 = rng(1, 1).Offset(0, -1).Value
> > vkey2 = rng(1, 1).Offset(0, 1).Value
> > 'allow an extra 2 for the check values
> > ReDim aryData(1 To cSize, 1 To cSize + 2)
> > aryData(1, 1) = vKey1
> > aryData(1, 2) = vkey2
> > i = 1: j = 3
> > stemp = ""
> > For Each cell In rng
> > If cell.Value <> "" Then
> > If cell.Offset(0, -1) = vKey1 And cell.Offset(0, 1).Value =
> > vkey2 Then
> > If fNotFirst Then
> > stemp = stemp & separator & cell.Value
> > Else
> > stemp = cell.Value
> > fNotFirst = True
> > End If
> > Else
> > aryData(i, j) = stemp
> > stemp = ""
> > 'clear down the rest of this dimension of the array
> > If j < UBound(aryData, 2) Then
> > For j = j + 1 To UBound(aryData, 2)
> > aryData(i, j) = ""
> > Next j
> > End If
> > stemp = cell.Value
> > aryData(i, 1) = vKey1
> > aryData(i, 2) = vkey2
> > vKey1 = cell.Offset(0, -1).Value
> > vkey2 = cell.Offset(0, 1).Value
> > i = i + 1
> > j = 3
> > End If
> > End If
> > Next cell
> >
> > 'pick up o/s data
> > aryData(i, 1) = vKey1
> > aryData(i, 2) = vkey2
> > aryData(i, j) = stemp
> > 'clear down the rest of this dimension of the array
> > If j < UBound(aryData, 2) Then
> > For j = j + 1 To UBound(aryData, 2)
> > aryData(i, j) = ""
> > Next j
> > End If
> >
> > 'clear down the rest of the array
> > If i < UBound(aryData, 1) Then
> > For i = i + 1 To UBound(aryData, 1)
> > For j = 1 To UBound(aryData, 2)
> > aryData(i, j) = ""
> > Next j
> > Next i
> > End If
> >
> > MultiConcat = aryData
> > End Function
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:790A68D6-0B9B-417A-9AFC-BF7750FF5C52@microsoft.com...
> > > Thanks. Looking for the UDF in your reply ? Regards.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Carl,
> > > >
> > > >
> > > > a little different that the layout that you asked for, but it does
work.
> > It
> > > > will look like
> > > >
> > > > 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> > > > 09:30:05 || B || S7;C2:A5
> > > > 09:30:15 || B || MA;G7;A6;N4
> > > > 09:30:15 || A || K0;B7;CK;SC;AK;AJ
> > > >
> > > > Add the UDF below to a standard code module.
> > > >
> > > > Then in the worksheet where you want the formula, select an array of
> > cells
> > > > that will be at least as many columns and rows as you want, hit F2,
and
> > in
> > > > the formula bar, enter your formula, such as
> > > > =MultiConcat(C5:C256,";")
> > > > which is an array formula, so commit with Ctrl-Shift-Enter.
> > > >
> > > > BTW I found the original UDF, a gem from Bernie Dietrick. I removed
the
> > > > unique and filtered options as well.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > > > > THanks again. Was not sure how to use the link below. I sent you
an
> > > > example
> > > > > directly. Appreciate your help.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > You could post an example at http://www.savefile.com/filehost/
and
> > let
> > > > me
> > > > > > know your URL. But show expected results as well.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > > > > Thanks again Bob. The fomating of this post makes it tough to
> > explain.
> > > > > > > Perhaps I could send you an example spreadsheet. If so, just
let
> > me
> > > > know
> > > > > > > where to send it.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > Carl,
> > > > > > > >
> > > > > > > > Sorry to keep pushing mate, but that data looks scrambled to
me.
> > I
> > > > > > cannot
> > > > > > > > tell whether Row 1 is a label in a cell, signifies a row or
> > what,
> > > > And is
> > > > > > the
> > > > > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could
> > relay
> > > > > > before
> > > > > > > > and after I can give it a shot, but at present I am not
clear
> > what
> > > > is
> > > > > > > > required.
> > > > > > > >
> > > > > > > > Another thing, is this to work on a set of rows, or just
one? If
> > the
> > > >
> > > > > > former,
> > > > > > > > can you give an example with more than one row of results?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > > > > Hi Bob. Thank you trying to help me.
> > > > > > > > >
> > > > > > > > > The function concatenates a selected range. I tried to
show
> > the
> > > > > > expected
> > > > > > > > > results in the last table of my post:
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > >
> > > > > > > > > For example in B2, I need a formula to look at the value
in
> > $A2
> > > > and
> > > > > > $B1,
> > > > > > > > > find all values in the data table that map up to these 2
> > cells,
> > > > and
> > > > > > > > > concatenate them with a ";" as the separator.
> > > > > > > > >
> > > > > > > > > Sorry I can't explain this any better.
> > > > > > > > >
> > > > > > > > > Thank you again.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > >
> > > > > > > > > > I can see that ConcatUF is a UDF of yours, which you
don't
> > show,
> > > > but
> > > > > > I
> > > > > > > > don't
> > > > > > > > > > get what the formula currently does or what you want.
> > > > > > > > > >
> > > > > > > > > > Can you try re-posting with the start data (use a
character
> > as a
> > > > > > cell
> > > > > > > > > > delimiter like ||) and expected results?
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > RP
> > > > > > > > > > (remove nothere from the email address if mailing
direct)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > > >
news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > > > > I would like to use this formula
> > > > > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > > > > in the table below.
> > > > > > > > > > >
> > > > > > > > > > > ColA ColB ColC
> > > > > > > > > > > Row1 Time A B
> > > > > > > > > > > Row2 10:30:00 AM
> > > > > > > > > > > Row3 9:30:00 AM
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > My data table is setup like this:
> > > > > > > > > > >
> > > > > > > > > > > ColA ColB ColC
> > > > > > > > > > > Row1 Time A B
> > > > > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > > > > >
> > > > > > > > > > > Can the formula be modified to perform the
concatenation
> > like
> > > > > > this:
> > > > > > > > > > >
> > > > > > > > > > > ColA ColB ColC
> > > > > > > > > > > Row1 Time A B
> > > > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > > > >
> > > > > > > > > > > Thank you in advance.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

What a prat :-). And worst of all, I deleted it so I have had to re-do it
.... sigh!

Here it is

Option Explicit

Function MultiConcat(rng As Range, _
Optional separator As String = ",")
Dim cell As Range
Dim cSize As Long
Dim fByRows As Boolean
Dim fNotFirst As Boolean
Dim aryData
Dim vKey1, vkey2
Dim i As Long, j As Long
Dim stemp

'validate input
If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
MultiConcat = "Select a single column or row array"
Exit Function
ElseIf rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
MultiConcat = "Oly one cell selected"
ElseIf rng.Rows.Count > 1 Then
fByRows = True
cSize = rng.Rows.Count
Else
cSize = rng.Columns.Count
End If

'initialise all the checking data
vKey1 = rng(1, 1).Offset(0, -1).Value
vkey2 = rng(1, 1).Offset(0, 1).Value
'allow an extra 2 for the check values
ReDim aryData(1 To cSize, 1 To cSize + 2)
aryData(1, 1) = vKey1
aryData(1, 2) = vkey2
i = 1: j = 3
stemp = ""
For Each cell In rng
If cell.Value <> "" Then
If cell.Offset(0, -1) = vKey1 And cell.Offset(0, 1).Value =
vkey2 Then
If fNotFirst Then
stemp = stemp & separator & cell.Value
Else
stemp = cell.Value
fNotFirst = True
End If
Else
aryData(i, j) = stemp
stemp = ""
'clear down the rest of this dimension of the array
If j < UBound(aryData, 2) Then
For j = j + 1 To UBound(aryData, 2)
aryData(i, j) = ""
Next j
End If
stemp = cell.Value
aryData(i, 1) = vKey1
aryData(i, 2) = vkey2
vKey1 = cell.Offset(0, -1).Value
vkey2 = cell.Offset(0, 1).Value
i = i + 1
j = 3
End If
End If
Next cell

'pick up o/s data
aryData(i, 1) = vKey1
aryData(i, 2) = vkey2
aryData(i, j) = stemp
'clear down the rest of this dimension of the array
If j < UBound(aryData, 2) Then
For j = j + 1 To UBound(aryData, 2)
aryData(i, j) = ""
Next j
End If

'clear down the rest of the array
If i < UBound(aryData, 1) Then
For i = i + 1 To UBound(aryData, 1)
For j = 1 To UBound(aryData, 2)
aryData(i, j) = ""
Next j
Next i
End If

MultiConcat = aryData
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:790A68D6-0B9B-417A-9AFC-BF7750FF5C52@microsoft.com...
> Thanks. Looking for the UDF in your reply ? Regards.
>
> "Bob Phillips" wrote:
>
> > Carl,
> >
> >
> > a little different that the layout that you asked for, but it does work.
It
> > will look like
> >
> > 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> > 09:30:05 || B || S7;C2:A5
> > 09:30:15 || B || MA;G7;A6;N4
> > 09:30:15 || A || K0;B7;CK;SC;AK;AJ
> >
> > Add the UDF below to a standard code module.
> >
> > Then in the worksheet where you want the formula, select an array of
cells
> > that will be at least as many columns and rows as you want, hit F2, and
in
> > the formula bar, enter your formula, such as
> > =MultiConcat(C5:C256,";")
> > which is an array formula, so commit with Ctrl-Shift-Enter.
> >
> > BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
> > unique and filtered options as well.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > > THanks again. Was not sure how to use the link below. I sent you an
> > example
> > > directly. Appreciate your help.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > You could post an example at http://www.savefile.com/filehost/ and
let
> > me
> > > > know your URL. But show expected results as well.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > > Thanks again Bob. The fomating of this post makes it tough to
explain.
> > > > > Perhaps I could send you an example spreadsheet. If so, just let
me
> > know
> > > > > where to send it.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Carl,
> > > > > >
> > > > > > Sorry to keep pushing mate, but that data looks scrambled to me.
I
> > > > cannot
> > > > > > tell whether Row 1 is a label in a cell, signifies a row or
what,
> > And is
> > > > the
> > > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could
relay
> > > > before
> > > > > > and after I can give it a shot, but at present I am not clear
what
> > is
> > > > > > required.
> > > > > >
> > > > > > Another thing, is this to work on a set of rows, or just one? If
the
> >
> > > > former,
> > > > > > can you give an example with more than one row of results?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > > Hi Bob. Thank you trying to help me.
> > > > > > >
> > > > > > > The function concatenates a selected range. I tried to show
the
> > > > expected
> > > > > > > results in the last table of my post:
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > >
> > > > > > > For example in B2, I need a formula to look at the value in
$A2
> > and
> > > > $B1,
> > > > > > > find all values in the data table that map up to these 2
cells,
> > and
> > > > > > > concatenate them with a ";" as the separator.
> > > > > > >
> > > > > > > Sorry I can't explain this any better.
> > > > > > >
> > > > > > > Thank you again.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > I can see that ConcatUF is a UDF of yours, which you don't
show,
> > but
> > > > I
> > > > > > don't
> > > > > > > > get what the formula currently does or what you want.
> > > > > > > >
> > > > > > > > Can you try re-posting with the start data (use a character
as a
> > > > cell
> > > > > > > > delimiter like ||) and expected results?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > > I would like to use this formula
> > > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > > in the table below.
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 10:30:00 AM
> > > > > > > > > Row3 9:30:00 AM
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > My data table is setup like this:
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > > >
> > > > > > > > > Can the formula be modified to perform the concatenation
like
> > > > this:
> > > > > > > > >
> > > > > > > > > ColA ColB ColC
> > > > > > > > > Row1 Time A B
> > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > >
> > > > > > > > > Thank you in advance.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Bob. Is there a limit on the length of the cell range I am trying to
concatenate.

I found that if I enter this:

=multiconcat(C4:C75,";")

all goes well. However if I increase to:

=multiconcat(C4:C76,";")

The formula returns #VALUE!.

Am I entering the formula incorrectly ?

Thanks Again.

"Bob Phillips" wrote:

> What a prat :-). And worst of all, I deleted it so I have had to re-do it
> .... sigh!
>
> Here it is
>
> Option Explicit
>
> Function MultiConcat(rng As Range, _
> Optional separator As String = ",")
> Dim cell As Range
> Dim cSize As Long
> Dim fByRows As Boolean
> Dim fNotFirst As Boolean
> Dim aryData
> Dim vKey1, vkey2
> Dim i As Long, j As Long
> Dim stemp
>
> 'validate input
> If rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
> MultiConcat = "Select a single column or row array"
> Exit Function
> ElseIf rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
> MultiConcat = "Oly one cell selected"
> ElseIf rng.Rows.Count > 1 Then
> fByRows = True
> cSize = rng.Rows.Count
> Else
> cSize = rng.Columns.Count
> End If
>
> 'initialise all the checking data
> vKey1 = rng(1, 1).Offset(0, -1).Value
> vkey2 = rng(1, 1).Offset(0, 1).Value
> 'allow an extra 2 for the check values
> ReDim aryData(1 To cSize, 1 To cSize + 2)
> aryData(1, 1) = vKey1
> aryData(1, 2) = vkey2
> i = 1: j = 3
> stemp = ""
> For Each cell In rng
> If cell.Value <> "" Then
> If cell.Offset(0, -1) = vKey1 And cell.Offset(0, 1).Value =
> vkey2 Then
> If fNotFirst Then
> stemp = stemp & separator & cell.Value
> Else
> stemp = cell.Value
> fNotFirst = True
> End If
> Else
> aryData(i, j) = stemp
> stemp = ""
> 'clear down the rest of this dimension of the array
> If j < UBound(aryData, 2) Then
> For j = j + 1 To UBound(aryData, 2)
> aryData(i, j) = ""
> Next j
> End If
> stemp = cell.Value
> aryData(i, 1) = vKey1
> aryData(i, 2) = vkey2
> vKey1 = cell.Offset(0, -1).Value
> vkey2 = cell.Offset(0, 1).Value
> i = i + 1
> j = 3
> End If
> End If
> Next cell
>
> 'pick up o/s data
> aryData(i, 1) = vKey1
> aryData(i, 2) = vkey2
> aryData(i, j) = stemp
> 'clear down the rest of this dimension of the array
> If j < UBound(aryData, 2) Then
> For j = j + 1 To UBound(aryData, 2)
> aryData(i, j) = ""
> Next j
> End If
>
> 'clear down the rest of the array
> If i < UBound(aryData, 1) Then
> For i = i + 1 To UBound(aryData, 1)
> For j = 1 To UBound(aryData, 2)
> aryData(i, j) = ""
> Next j
> Next i
> End If
>
> MultiConcat = aryData
> End Function
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:790A68D6-0B9B-417A-9AFC-BF7750FF5C52@microsoft.com...
> > Thanks. Looking for the UDF in your reply ? Regards.
> >
> > "Bob Phillips" wrote:
> >
> > > Carl,
> > >
> > >
> > > a little different that the layout that you asked for, but it does work.
> It
> > > will look like
> > >
> > > 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> > > 09:30:05 || B || S7;C2:A5
> > > 09:30:15 || B || MA;G7;A6;N4
> > > 09:30:15 || A || K0;B7;CK;SC;AK;AJ
> > >
> > > Add the UDF below to a standard code module.
> > >
> > > Then in the worksheet where you want the formula, select an array of
> cells
> > > that will be at least as many columns and rows as you want, hit F2, and
> in
> > > the formula bar, enter your formula, such as
> > > =MultiConcat(C5:C256,";")
> > > which is an array formula, so commit with Ctrl-Shift-Enter.
> > >
> > > BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
> > > unique and filtered options as well.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > > > THanks again. Was not sure how to use the link below. I sent you an
> > > example
> > > > directly. Appreciate your help.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > You could post an example at http://www.savefile.com/filehost/ and
> let
> > > me
> > > > > know your URL. But show expected results as well.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > > > Thanks again Bob. The fomating of this post makes it tough to
> explain.
> > > > > > Perhaps I could send you an example spreadsheet. If so, just let
> me
> > > know
> > > > > > where to send it.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > Carl,
> > > > > > >
> > > > > > > Sorry to keep pushing mate, but that data looks scrambled to me.
> I
> > > > > cannot
> > > > > > > tell whether Row 1 is a label in a cell, signifies a row or
> what,
> > > And is
> > > > > the
> > > > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could
> relay
> > > > > before
> > > > > > > and after I can give it a shot, but at present I am not clear
> what
> > > is
> > > > > > > required.
> > > > > > >
> > > > > > > Another thing, is this to work on a set of rows, or just one? If
> the
> > >
> > > > > former,
> > > > > > > can you give an example with more than one row of results?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > > > Hi Bob. Thank you trying to help me.
> > > > > > > >
> > > > > > > > The function concatenates a selected range. I tried to show
> the
> > > > > expected
> > > > > > > > results in the last table of my post:
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > >
> > > > > > > > For example in B2, I need a formula to look at the value in
> $A2
> > > and
> > > > > $B1,
> > > > > > > > find all values in the data table that map up to these 2
> cells,
> > > and
> > > > > > > > concatenate them with a ";" as the separator.
> > > > > > > >
> > > > > > > > Sorry I can't explain this any better.
> > > > > > > >
> > > > > > > > Thank you again.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > > I can see that ConcatUF is a UDF of yours, which you don't
> show,
> > > but
> > > > > I
> > > > > > > don't
> > > > > > > > > get what the formula currently does or what you want.
> > > > > > > > >
> > > > > > > > > Can you try re-posting with the start data (use a character
> as a
> > > > > cell
> > > > > > > > > delimiter like ||) and expected results?
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > RP
> > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > > > I would like to use this formula
> > > > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > > > in the table below.
> > > > > > > > > >
> > > > > > > > > > ColA ColB ColC
> > > > > > > > > > Row1 Time A B
> > > > > > > > > > Row2 10:30:00 AM
> > > > > > > > > > Row3 9:30:00 AM
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > My data table is setup like this:
> > > > > > > > > >
> > > > > > > > > > ColA ColB ColC
> > > > > > > > > > Row1 Time A B
> > > > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > > > >
> > > > > > > > > > Can the formula be modified to perform the concatenation
> like
> > > > > this:
> > > > > > > > > >
> > > > > > > > > > ColA ColB ColC
> > > > > > > > > > Row1 Time A B
> > > > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > > > >
> > > > > > > > > > Thank you in advance.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Thanks. Looking for the UDF in your reply ? Regards.

"Bob Phillips" wrote:

> Carl,
>
>
> a little different that the layout that you asked for, but it does work. It
> will look like
>
> 09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
> 09:30:05 || B || S7;C2:A5
> 09:30:15 || B || MA;G7;A6;N4
> 09:30:15 || A || K0;B7;CK;SC;AK;AJ
>
> Add the UDF below to a standard code module.
>
> Then in the worksheet where you want the formula, select an array of cells
> that will be at least as many columns and rows as you want, hit F2, and in
> the formula bar, enter your formula, such as
> =MultiConcat(C5:C256,";")
> which is an array formula, so commit with Ctrl-Shift-Enter.
>
> BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
> unique and filtered options as well.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> > THanks again. Was not sure how to use the link below. I sent you an
> example
> > directly. Appreciate your help.
> >
> > "Bob Phillips" wrote:
> >
> > > You could post an example at http://www.savefile.com/filehost/ and let
> me
> > > know your URL. But show expected results as well.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > > Thanks again Bob. The fomating of this post makes it tough to explain.
> > > > Perhaps I could send you an example spreadsheet. If so, just let me
> know
> > > > where to send it.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Carl,
> > > > >
> > > > > Sorry to keep pushing mate, but that data looks scrambled to me. I
> > > cannot
> > > > > tell whether Row 1 is a label in a cell, signifies a row or what,
> And is
> > > the
> > > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
> > > before
> > > > > and after I can give it a shot, but at present I am not clear what
> is
> > > > > required.
> > > > >
> > > > > Another thing, is this to work on a set of rows, or just one? If the
>
> > > former,
> > > > > can you give an example with more than one row of results?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > > Hi Bob. Thank you trying to help me.
> > > > > >
> > > > > > The function concatenates a selected range. I tried to show the
> > > expected
> > > > > > results in the last table of my post:
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > >
> > > > > > For example in B2, I need a formula to look at the value in $A2
> and
> > > $B1,
> > > > > > find all values in the data table that map up to these 2 cells,
> and
> > > > > > concatenate them with a ";" as the separator.
> > > > > >
> > > > > > Sorry I can't explain this any better.
> > > > > >
> > > > > > Thank you again.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > I can see that ConcatUF is a UDF of yours, which you don't show,
> but
> > > I
> > > > > don't
> > > > > > > get what the formula currently does or what you want.
> > > > > > >
> > > > > > > Can you try re-posting with the start data (use a character as a
> > > cell
> > > > > > > delimiter like ||) and expected results?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > > I would like to use this formula
> > > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > > in the table below.
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 10:30:00 AM
> > > > > > > > Row3 9:30:00 AM
> > > > > > > >
> > > > > > > >
> > > > > > > > My data table is setup like this:
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > > >
> > > > > > > > Can the formula be modified to perform the concatenation like
> > > this:
> > > > > > > >
> > > > > > > > ColA ColB ColC
> > > > > > > > Row1 Time A B
> > > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > > >
> > > > > > > > Thank you in advance.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
in the table below.

ColA ColB ColC
Row1 Time A B
Row2 10:30:00 AM
Row3 9:30:00 AM


My data table is setup like this:

ColA ColB ColC
Row1 Time A B
Row2 9:30:00 AM E1 GG
Row3 9:30:00 AM Y0 D1
Row4 10:30:00 AM U4 Q1

Can the formula be modified to perform the concatenation like this:

ColA ColB ColC
Row1 Time A B
Row2 10:30:00 AM U4 Q1
Row3 9:30:00 AM E1;Y0 D1;Q1

Thank you in advance.

Carl,


a little different that the layout that you asked for, but it does work. It
will look like

09:30:05 || A || E1;Y0;U4;S2;AG;Q0;B5
09:30:05 || B || S7;C2:A5
09:30:15 || B || MA;G7;A6;N4
09:30:15 || A || K0;B7;CK;SC;AK;AJ

Add the UDF below to a standard code module.

Then in the worksheet where you want the formula, select an array of cells
that will be at least as many columns and rows as you want, hit F2, and in
the formula bar, enter your formula, such as
=MultiConcat(C5:C256,";")
which is an array formula, so commit with Ctrl-Shift-Enter.

BTW I found the original UDF, a gem from Bernie Dietrick. I removed the
unique and filtered options as well.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:BE7604BB-3DD8-4F68-8EE8-6FD4ECE4260A@microsoft.com...
> THanks again. Was not sure how to use the link below. I sent you an
example
> directly. Appreciate your help.
>
> "Bob Phillips" wrote:
>
> > You could post an example at http://www.savefile.com/filehost/ and let
me
> > know your URL. But show expected results as well.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > > Thanks again Bob. The fomating of this post makes it tough to explain.
> > > Perhaps I could send you an example spreadsheet. If so, just let me
know
> > > where to send it.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Carl,
> > > >
> > > > Sorry to keep pushing mate, but that data looks scrambled to me. I
> > cannot
> > > > tell whether Row 1 is a label in a cell, signifies a row or what,
And is
> > the
> > > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
> > before
> > > > and after I can give it a shot, but at present I am not clear what
is
> > > > required.
> > > >
> > > > Another thing, is this to work on a set of rows, or just one? If the

> > former,
> > > > can you give an example with more than one row of results?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > > Hi Bob. Thank you trying to help me.
> > > > >
> > > > > The function concatenates a selected range. I tried to show the
> > expected
> > > > > results in the last table of my post:
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 10:30:00 AM U4 Q1
> > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > >
> > > > > For example in B2, I need a formula to look at the value in $A2
and
> > $B1,
> > > > > find all values in the data table that map up to these 2 cells,
and
> > > > > concatenate them with a ";" as the separator.
> > > > >
> > > > > Sorry I can't explain this any better.
> > > > >
> > > > > Thank you again.
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I can see that ConcatUF is a UDF of yours, which you don't show,
but
> > I
> > > > don't
> > > > > > get what the formula currently does or what you want.
> > > > > >
> > > > > > Can you try re-posting with the start data (use a character as a
> > cell
> > > > > > delimiter like ||) and expected results?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > > I would like to use this formula
> > > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > > in the table below.
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 10:30:00 AM
> > > > > > > Row3 9:30:00 AM
> > > > > > >
> > > > > > >
> > > > > > > My data table is setup like this:
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 9:30:00 AM E1 GG
> > > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > > Row4 10:30:00 AM U4 Q1
> > > > > > >
> > > > > > > Can the formula be modified to perform the concatenation like
> > this:
> > > > > > >
> > > > > > > ColA ColB ColC
> > > > > > > Row1 Time A B
> > > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > > >
> > > > > > > Thank you in advance.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

THanks again. Was not sure how to use the link below. I sent you an example
directly. Appreciate your help.

"Bob Phillips" wrote:

> You could post an example at http://www.savefile.com/filehost/ and let me
> know your URL. But show expected results as well.
>
> --
> HTH
>
> Bob Phillips
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> > Thanks again Bob. The fomating of this post makes it tough to explain.
> > Perhaps I could send you an example spreadsheet. If so, just let me know
> > where to send it.
> >
> > "Bob Phillips" wrote:
> >
> > > Carl,
> > >
> > > Sorry to keep pushing mate, but that data looks scrambled to me. I
> cannot
> > > tell whether Row 1 is a label in a cell, signifies a row or what, And is
> the
> > > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
> before
> > > and after I can give it a shot, but at present I am not clear what is
> > > required.
> > >
> > > Another thing, is this to work on a set of rows, or just one? If the
> former,
> > > can you give an example with more than one row of results?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > > Hi Bob. Thank you trying to help me.
> > > >
> > > > The function concatenates a selected range. I tried to show the
> expected
> > > > results in the last table of my post:
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 10:30:00 AM U4 Q1
> > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > >
> > > > For example in B2, I need a formula to look at the value in $A2 and
> $B1,
> > > > find all values in the data table that map up to these 2 cells, and
> > > > concatenate them with a ";" as the separator.
> > > >
> > > > Sorry I can't explain this any better.
> > > >
> > > > Thank you again.
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > I can see that ConcatUF is a UDF of yours, which you don't show, but
> I
> > > don't
> > > > > get what the formula currently does or what you want.
> > > > >
> > > > > Can you try re-posting with the start data (use a character as a
> cell
> > > > > delimiter like ||) and expected results?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > > I would like to use this formula
> > > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > > in the table below.
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 10:30:00 AM
> > > > > > Row3 9:30:00 AM
> > > > > >
> > > > > >
> > > > > > My data table is setup like this:
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 9:30:00 AM E1 GG
> > > > > > Row3 9:30:00 AM Y0 D1
> > > > > > Row4 10:30:00 AM U4 Q1
> > > > > >
> > > > > > Can the formula be modified to perform the concatenation like
> this:
> > > > > >
> > > > > > ColA ColB ColC
> > > > > > Row1 Time A B
> > > > > > Row2 10:30:00 AM U4 Q1
> > > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > > >
> > > > > > Thank you in advance.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

You could post an example at http://www.savefile.com/filehost/ and let me
know your URL. But show expected results as well.

--
HTH

Bob Phillips

"carl" <carl@discussions.microsoft.com> wrote in message
news:60BE74CA-FAAD-4C01-B936-A5202680AA34@microsoft.com...
> Thanks again Bob. The fomating of this post makes it tough to explain.
> Perhaps I could send you an example spreadsheet. If so, just let me know
> where to send it.
>
> "Bob Phillips" wrote:
>
> > Carl,
> >
> > Sorry to keep pushing mate, but that data looks scrambled to me. I
cannot
> > tell whether Row 1 is a label in a cell, signifies a row or what, And is
the
> > 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay
before
> > and after I can give it a shot, but at present I am not clear what is
> > required.
> >
> > Another thing, is this to work on a set of rows, or just one? If the
former,
> > can you give an example with more than one row of results?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > > Hi Bob. Thank you trying to help me.
> > >
> > > The function concatenates a selected range. I tried to show the
expected
> > > results in the last table of my post:
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 10:30:00 AM U4 Q1
> > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > >
> > > For example in B2, I need a formula to look at the value in $A2 and
$B1,
> > > find all values in the data table that map up to these 2 cells, and
> > > concatenate them with a ";" as the separator.
> > >
> > > Sorry I can't explain this any better.
> > >
> > > Thank you again.
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I can see that ConcatUF is a UDF of yours, which you don't show, but
I
> > don't
> > > > get what the formula currently does or what you want.
> > > >
> > > > Can you try re-posting with the start data (use a character as a
cell
> > > > delimiter like ||) and expected results?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > > I would like to use this formula
> > "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > > in the table below.
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 10:30:00 AM
> > > > > Row3 9:30:00 AM
> > > > >
> > > > >
> > > > > My data table is setup like this:
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 9:30:00 AM E1 GG
> > > > > Row3 9:30:00 AM Y0 D1
> > > > > Row4 10:30:00 AM U4 Q1
> > > > >
> > > > > Can the formula be modified to perform the concatenation like
this:
> > > > >
> > > > > ColA ColB ColC
> > > > > Row1 Time A B
> > > > > Row2 10:30:00 AM U4 Q1
> > > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > > >
> > > > > Thank you in advance.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Thanks again Bob. The fomating of this post makes it tough to explain.
Perhaps I could send you an example spreadsheet. If so, just let me know
where to send it.

"Bob Phillips" wrote:

> Carl,
>
> Sorry to keep pushing mate, but that data looks scrambled to me. I cannot
> tell whether Row 1 is a label in a cell, signifies a row or what, And is the
> 10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay before
> and after I can give it a shot, but at present I am not clear what is
> required.
>
> Another thing, is this to work on a set of rows, or just one? If the former,
> can you give an example with more than one row of results?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> > Hi Bob. Thank you trying to help me.
> >
> > The function concatenates a selected range. I tried to show the expected
> > results in the last table of my post:
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 10:30:00 AM U4 Q1
> > Row3 9:30:00 AM E1;Y0 D1;Q1
> >
> > For example in B2, I need a formula to look at the value in $A2 and $B1,
> > find all values in the data table that map up to these 2 cells, and
> > concatenate them with a ";" as the separator.
> >
> > Sorry I can't explain this any better.
> >
> > Thank you again.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > I can see that ConcatUF is a UDF of yours, which you don't show, but I
> don't
> > > get what the formula currently does or what you want.
> > >
> > > Can you try re-posting with the start data (use a character as a cell
> > > delimiter like ||) and expected results?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "carl" <carl@discussions.microsoft.com> wrote in message
> > > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > > I would like to use this formula
> "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > > in the table below.
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 10:30:00 AM
> > > > Row3 9:30:00 AM
> > > >
> > > >
> > > > My data table is setup like this:
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 9:30:00 AM E1 GG
> > > > Row3 9:30:00 AM Y0 D1
> > > > Row4 10:30:00 AM U4 Q1
> > > >
> > > > Can the formula be modified to perform the concatenation like this:
> > > >
> > > > ColA ColB ColC
> > > > Row1 Time A B
> > > > Row2 10:30:00 AM U4 Q1
> > > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > > >
> > > > Thank you in advance.
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Carl,

Sorry to keep pushing mate, but that data looks scrambled to me. I cannot
tell whether Row 1 is a label in a cell, signifies a row or what, And is the
10:30:00 in B, C.? Is AM U4 Q1 all in one cell. If you could relay before
and after I can give it a shot, but at present I am not clear what is
required.

Another thing, is this to work on a set of rows, or just one? If the former,
can you give an example with more than one row of results?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:2960DDFD-4B3E-4AFD-8B85-ACE9F70FE449@microsoft.com...
> Hi Bob. Thank you trying to help me.
>
> The function concatenates a selected range. I tried to show the expected
> results in the last table of my post:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM U4 Q1
> Row3 9:30:00 AM E1;Y0 D1;Q1
>
> For example in B2, I need a formula to look at the value in $A2 and $B1,
> find all values in the data table that map up to these 2 cells, and
> concatenate them with a ";" as the separator.
>
> Sorry I can't explain this any better.
>
> Thank you again.
>
>
>
> "Bob Phillips" wrote:
>
> > I can see that ConcatUF is a UDF of yours, which you don't show, but I
don't
> > get what the formula currently does or what you want.
> >
> > Can you try re-posting with the start data (use a character as a cell
> > delimiter like ||) and expected results?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "carl" <carl@discussions.microsoft.com> wrote in message
> > news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > > I would like to use this formula
"=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > > in the table below.
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 10:30:00 AM
> > > Row3 9:30:00 AM
> > >
> > >
> > > My data table is setup like this:
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 9:30:00 AM E1 GG
> > > Row3 9:30:00 AM Y0 D1
> > > Row4 10:30:00 AM U4 Q1
> > >
> > > Can the formula be modified to perform the concatenation like this:
> > >
> > > ColA ColB ColC
> > > Row1 Time A B
> > > Row2 10:30:00 AM U4 Q1
> > > Row3 9:30:00 AM E1;Y0 D1;Q1
> > >
> > > Thank you in advance.
> > >
> > >
> > >
> >
> >
> >

Hi Bob. Thank you trying to help me.

The function concatenates a selected range. I tried to show the expected
results in the last table of my post:

ColA ColB ColC
Row1 Time A B
Row2 10:30:00 AM U4 Q1
Row3 9:30:00 AM E1;Y0 D1;Q1

For example in B2, I need a formula to look at the value in $A2 and $B1,
find all values in the data table that map up to these 2 cells, and
concatenate them with a ";" as the separator.

Sorry I can't explain this any better.

Thank you again.



"Bob Phillips" wrote:

> I can see that ConcatUF is a UDF of yours, which you don't show, but I don't
> get what the formula currently does or what you want.
>
> Can you try re-posting with the start data (use a character as a cell
> delimiter like ||) and expected results?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "carl" <carl@discussions.microsoft.com> wrote in message
> news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> > I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> > in the table below.
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 10:30:00 AM
> > Row3 9:30:00 AM
> >
> >
> > My data table is setup like this:
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 9:30:00 AM E1 GG
> > Row3 9:30:00 AM Y0 D1
> > Row4 10:30:00 AM U4 Q1
> >
> > Can the formula be modified to perform the concatenation like this:
> >
> > ColA ColB ColC
> > Row1 Time A B
> > Row2 10:30:00 AM U4 Q1
> > Row3 9:30:00 AM E1;Y0 D1;Q1
> >
> > Thank you in advance.
> >
> >
> >
>
>
>

I can see that ConcatUF is a UDF of yours, which you don't show, but I don't
get what the formula currently does or what you want.

Can you try re-posting with the start data (use a character as a cell
delimiter like ||) and expected results?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <carl@discussions.microsoft.com> wrote in message
news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> in the table below.
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM
> Row3 9:30:00 AM
>
>
> My data table is setup like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 9:30:00 AM E1 GG
> Row3 9:30:00 AM Y0 D1
> Row4 10:30:00 AM U4 Q1
>
> Can the formula be modified to perform the concatenation like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM U4 Q1
> Row3 9:30:00 AM E1;Y0 D1;Q1
>
> Thank you in advance.
>
>
>

Its a User Defined Function. You need to provide the code for it. The UF in
the name of the function says that its a user function. Press Alt F11 to
view the code. Check the standard modules on the left and see where the code
is and post it.

Mangesh



"carl" <carl@discussions.microsoft.com> wrote in message
news:E3B489E9-D257-4E7D-A9C9-66A706AD5A5C@microsoft.com...
> I would like to use this formula "=ConcatUF(times!B7:B16,";",FALSE,FALSE)"
> in the table below.
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM
> Row3 9:30:00 AM
>
>
> My data table is setup like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 9:30:00 AM E1 GG
> Row3 9:30:00 AM Y0 D1
> Row4 10:30:00 AM U4 Q1
>
> Can the formula be modified to perform the concatenation like this:
>
> ColA ColB ColC
> Row1 Time A B
> Row2 10:30:00 AM U4 Q1
> Row3 9:30:00 AM E1;Y0 D1;Q1
>
> Thank you in advance.
>
>
>