Cobining more than 30 columns of data with concatenate.

I have about 50 columns of data I would like to combined to one column, if
Text, if cell is equal to 0 or blank than I do not want to pull into the
cell. How can this be done ?
Also how do I combine mulitple rows of data, if criteria is met in function?
I have qualifiers, to determine if rows should combine

One way, using a UDF:

Public Function ConCatNonZero(rng As Range) As String
Dim rCell As Range
Dim sTemp As String
Dim sAdd As String
For Each rCell In rng
sAdd = rCell.Text
If Len(sAdd) > 0 Then _
If IsNumeric(sAdd) Then _
If CLng(sAdd) = 0 Then sAdd = ""
sTemp = sTemp & sAdd
Next rCell
ConCatNonZero = sTemp
End Function

Call as

=ConCatNonZero(A1:AX1)


In article <[email protected]>,
"JT" <[email protected]> wrote:

> I have about 50 columns of data I would like to combined to one column, if
> Text, if cell is equal to 0 or blank than I do not want to pull into the
> cell. How can this be done ?

Not sure how to do what you describe below? Do I go into the view code
window and copy paste?

"JE McGimpsey" wrote:

> One way, using a UDF:
>
> Public Function ConCatNonZero(rng As Range) As String
> Dim rCell As Range
> Dim sTemp As String
> Dim sAdd As String
> For Each rCell In rng
> sAdd = rCell.Text
> If Len(sAdd) > 0 Then _
> If IsNumeric(sAdd) Then _
> If CLng(sAdd) = 0 Then sAdd = ""
> sTemp = sTemp & sAdd
> Next rCell
> ConCatNonZero = sTemp
> End Function
>
> Call as
>
> =ConCatNonZero(A1:AX1)
>
>
> In article <[email protected]>,
> "JT" <[email protected]> wrote:
>
> > I have about 50 columns of data I would like to combined to one column, if
> > Text, if cell is equal to 0 or blank than I do not want to pull into the
> > cell. How can this be done ?
>

See

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

The function needs to go in a regular code module, not a sheet or
ThisWorkbook module.

In article <[email protected]>,
"JT" <[email protected]> wrote:

> Not sure how to do what you describe below? Do I go into the view code
> window and copy paste?
>
> "JE McGimpsey" wrote:
>
> > One way, using a UDF:
> >
> > Public Function ConCatNonZero(rng As Range) As String
> > Dim rCell As Range
> > Dim sTemp As String
> > Dim sAdd As String
> > For Each rCell In rng
> > sAdd = rCell.Text
> > If Len(sAdd) > 0 Then _
> > If IsNumeric(sAdd) Then _
> > If CLng(sAdd) = 0 Then sAdd = ""
> > sTemp = sTemp & sAdd
> > Next rCell
> > ConCatNonZero = sTemp
> > End Function
> >
> > Call as
> >
> > =ConCatNonZero(A1:AX1)
> >
> >
> > In article <[email protected]>,
> > "JT" <[email protected]> wrote:
> >
> > > I have about 50 columns of data I would like to combined to one column,
> > > if
> > > Text, if cell is equal to 0 or blank than I do not want to pull into the
> > > cell. How can this be done ?
> >

Do like this,
1. Press Alt+F11 to get to the VBA Window
2. Goto to menu: Insert>Module
3. Copy and paste the code (you might need to fix some loose row breakes or
spaces)
4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where it
should be
Done

Ola Sandström

I recive "Name #" when I done this

"Ola" wrote:

> Do like this,
> 1. Press Alt+F11 to get to the VBA Window
> 2. Goto to menu: Insert>Module
> 3. Copy and paste the code (you might need to fix some loose row breakes or
> spaces)
> 4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where it
> should be
> Done
>
> Ola Sandström

It looks simple but it can't get it to work either. --> #VALUE!

--
Message posted via http://www.officekb.com

Make sure you insert the module in the right workbook, works fine for me

--
Regards,

Peo Sjoblom


"JT" <[email protected]> wrote in message
news:[email protected]...
>I recive "Name #" when I done this
>
> "Ola" wrote:
>
>> Do like this,
>> 1. Press Alt+F11 to get to the VBA Window
>> 2. Goto to menu: Insert>Module
>> 3. Copy and paste the code (you might need to fix some loose row breakes
>> or
>> spaces)
>> 4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where it
>> should be
>> Done
>>
>> Ola Sandström

Is there a way to send me an attachment to see one you did ? I cannot get it
to work for me:[email protected]

"Peo Sjoblom" wrote:

> Make sure you insert the module in the right workbook, works fine for me
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "JT" <[email protected]> wrote in message
> news:[email protected]...
> >I recive "Name #" when I done this
> >
> > "Ola" wrote:
> >
> >> Do like this,
> >> 1. Press Alt+F11 to get to the VBA Window
> >> 2. Goto to menu: Insert>Module
> >> 3. Copy and paste the code (you might need to fix some loose row breakes
> >> or
> >> spaces)
> >> 4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where it
> >> should be
> >> Done
> >>
> >> Ola Sandström
>
>

Is there a way you can send me an attachment to see how you did this on your
worksheet ? Email: [email protected]

"Peo Sjoblom" wrote:

> Make sure you insert the module in the right workbook, works fine for me
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "JT" <[email protected]> wrote in message
> news:[email protected]...
> >I recive "Name #" when I done this
> >
> > "Ola" wrote:
> >
> >> Do like this,
> >> 1. Press Alt+F11 to get to the VBA Window
> >> 2. Goto to menu: Insert>Module
> >> 3. Copy and paste the code (you might need to fix some loose row breakes
> >> or
> >> spaces)
> >> 4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where it
> >> should be
> >> Done
> >>
> >> Ola Sandström
>
>

Ok, example sent

--
Regards,

Peo Sjoblom


"JT" <[email protected]> wrote in message
news:[email protected]...
> Is there a way you can send me an attachment to see how you did this on
> your
> worksheet ? Email: [email protected]
>
> "Peo Sjoblom" wrote:
>
>> Make sure you insert the module in the right workbook, works fine for me
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "JT" <[email protected]> wrote in message
>> news:[email protected]...
>> >I recive "Name #" when I done this
>> >
>> > "Ola" wrote:
>> >
>> >> Do like this,
>> >> 1. Press Alt+F11 to get to the VBA Window
>> >> 2. Goto to menu: Insert>Module
>> >> 3. Copy and paste the code (you might need to fix some loose row
>> >> breakes
>> >> or
>> >> spaces)
>> >> 4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where
>> >> it
>> >> should be
>> >> Done
>> >>
>> >> Ola Sandström
>>
>>

Then you're either putting it in another workbook, or you're putting it
in the ThisWorkbook module, a worksheet module or a class module.

In article <[email protected]>,
"JT" <[email protected]> wrote:

> I recive "Name #" when I done this