Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 ? |
#3
|
|||
|
|||
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 ? |
#4
|
|||
|
|||
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 ? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
It looks simple but it can't get it to work either. -- #VALUE!
-- Message posted via http://www.officekb.com |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to Columns / Importing Data | Excel Discussion (Misc queries) | |||
spliting a column of data into multiple columns | Excel Discussion (Misc queries) | |||
Exported Data to excel appearing wrong in Columns | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) |