Multiple field with data compiled into 1 field?

[dan.l.smith]

I have 120 field in the row, some have data some do not. I want to compile all the data to a single field on a seperate page excluding any field that is blank. I used this one
=d4&","&e4&","&f4
But with this one I ended up with alot of commas in between the data. I want to eliminate all the commas. Any help would be appriciated thanks for taking the time to look at this.

 

[XLGibbs]

your formula put the commas in there

replace the commas with & " " & to insert a space instead or

do this

=d4&e4&f4

The below code will take columns 1 to 120 and concatonate with spaces in between each cells, as well as eliminate any trailing spaces from the end of the cell. The resulting concatonation will be placed in the same row in column 121. You can paste this into a module by hitting Alt F11 to open the VB editor. Find your workbook name on the left and right click > insert module, and paste this code in the window on the right. You can run it by pressing the play button on the top toolbar, or from the worksheet by hitting Alt F8 and selecting Run. if you don't want a space between each cell eliminate the & " " from the strA expressions. The code also handles blank cells and inserts a space. you can eliminate the " " & from the second StrA to do away with that.

Sub CombinedRowData()
Dim LastRow As Long
Dim LastCol As Long
Dim Wks1 As Worksheet  'Wks1 will be the tab where data is imported from change name as needed
Dim strA As String
Set Wks1 = Sheets("Sheet1")   'assumes Sheet1 is sheetname
With Wks1
 LastRow = Range("A65536").End(xlUp).Row  
'change to your first column if  ' 'data does not start in  A  adjust last column 1 per column over from A as needed to get 120 columns
 
LastCol = 120    ' assumes 120 columns, change as needed

 For x = 1 To LastRow
       For Y = 1 To LastCol
        If Y = 1 Then
         strA = strA & Trim(Cells(x, Y)) & " "
         End If
        If Y <> 1 Then
          strA = " " & strA & Trim(Cells(x, Y)) & " "
          End If
        Next Y
Cells(x, 121) = Trim(strA)   'this puts it in column 121 put ' before it to disable

' Sheets("Sheet2").Cells(x,1) = Trim (strA)
'the above line will place the data on the Sheet2 in column A row by row remove the ' to enable it

strProduct = ""
 Next x
End With

End Sub
[/quote]

 

[barry houdini]

I have 120 field in the row, some have data some do not. I want to compile all the data to a single field on a seperate page excluding any field that is blank. I used this one
=d4&","&e4&","&f4
But with this one I ended up with alot of commas in between the data. I want to eliminate all the commas. Any help would be appriciated thanks for taking the time to look at this.

I presume you want a single comma between values but not multiple commas where you have blanks.

You can use a formula solution using the MCONCAT function from the MOREFUNC addin available here

http://www.mrexcel.com/board2/viewtopic.php?t=131224

then you can do something like this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	hello		barry		how	are				you
3
4
5	hello, barry, how, are, you
6
Sheet1

formula in A5

=SUBSTITUTE(MCONCAT(IF(A2:J2<>"",", "&A2:J2,"")),", ","",1)

which is an array formula which must be confirmed with CTRL+SHIFT+ENTER

you could also do a search on this board for aconcat, a UDF which would do something similar

 

[barry houdini]

... two other approaches you could use, closer to your original formula

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	hello		barry		how	are				you
3
4
5	hello barry how are you
6	hello, barry, how, are, you
7
Sheet1

formula in A5 to give you just single spaces between "words"

=TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)

or to give you commas as before

in A6

=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)," ",", ")

although, clearly these formulas would not be recommended for concatenating large numbers of cells.