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 ,
"JT" 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 ,
"JT" 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 ,
"JT" 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 ,
"JT" 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: InsertModule
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: InsertModule
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" wrote in message
...
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: InsertModule
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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"JT" wrote in message
...
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: InsertModule
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:

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"JT" wrote in message
...
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: InsertModule
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" wrote in message
...
Is there a way you can send me an attachment to see how you did this on
your
worksheet ? Email:

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"JT" wrote in message
...
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: InsertModule
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 ,
"JT" wrote:

I recive "Name #" when I done this