Concatenate
 

Please i need help. I am working on a large file.
I need my output to be place in another column.
The end result should look like this.
I know that i have to use the for loop and concatenate.
quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl
until the end
Sample data
Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

..

Concatenate
 

No VBA is needed for that. Assuming data is A2:C2, put in
D2: ="quantity#" & A2 & "label1#" & B2 & "stlye#" & C2

Copy to other rows as needed.

Hth,
Merjet

Concatenate
 

You might try this:

="Quantity#" & A2 & "Label#" & B2 & "Style#" & C2

Then copy it down.

Mike

"maju" wrote:

Please i need help. I am working on a large file.
I need my output to be place in another column.
The end result should look like this.
I know that i have to use the for loop and concatenate.
quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl
until the end
Sample data
Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

.

Concatenate
 

Formula like:

=($A$1&"#"&A2&$B$1&"#"&B2&$C$1&"#"&C2) ???

Concatenate
 

Try this:
Sub CreateData()
Dim x As Double
Dim dataarray(50000, 3) As Variant
Let x = 1
Do While True
If Cells(x, 1).Value = Empty Then Exit Do
fnd = fnd + 1
dataarray(fnd, 1) = Cells(x, 1).Value
dataarray(fnd, 2) = Cells(x, 2).Value
dataarray(fnd, 3) = Cells(x, 3).Value
x = x + 1
Loop
If fnd 0 Then
Sheets("sheet2").Select
For x = 1 To fnd
Sheet2.Cells(x, 1).Value = "quantity#" & dataarray(x, 1) & "label#"
& dataarray(x, 2) & "style#" & dataarray(x, 3)
Next
End If




End Sub


"maju" wrote:

Please i need help. I am working on a large file.
I need my output to be place in another column.
The end result should look like this.
I know that i have to use the for loop and concatenate.
quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl
until the end
Sample data
Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

.

Concatenate
 

Thx but i will be dealing with a huge number of rows. so i will not be able
to do it for each row. that is why i thought that looping will be better.

"merjet" wrote:

No VBA is needed for that. Assuming data is A2:C2, put in
D2: ="quantity#" & A2 & "label1#" & B2 & "stlye#" & C2

Copy to other rows as needed.

Hth,
Merjet

Concatenate
 

Thx. i will try it and see if it works.

"Mike H." wrote:

Try this:
Sub CreateData()
Dim x As Double
Dim dataarray(50000, 3) As Variant
Let x = 1
Do While True
If Cells(x, 1).Value = Empty Then Exit Do
fnd = fnd + 1
dataarray(fnd, 1) = Cells(x, 1).Value
dataarray(fnd, 2) = Cells(x, 2).Value
dataarray(fnd, 3) = Cells(x, 3).Value
x = x + 1
Loop
If fnd 0 Then
Sheets("sheet2").Select
For x = 1 To fnd
Sheet2.Cells(x, 1).Value = "quantity#" & dataarray(x, 1) & "label#"
& dataarray(x, 2) & "style#" & dataarray(x, 3)
Next
End If




End Sub


"maju" wrote:

Please i need help. I am working on a large file.
I need my output to be place in another column.
The end result should look like this.
I know that i have to use the for loop and concatenate.
quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl
until the end
Sample data
Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

.

Concatenate
 

Mr.Mike
if the data in the excel is like:

quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl

please let us know the VBA code to get data in the following format,

Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

thanks in advance and with regards

ramesh

*** Sent via Developersdex http://www.developersdex.com ***

Concatenate
 

ramesh,

Without VBA your string could be split using the menu Data | Text to
Columns and # as the delimiter, then deleting the words. If you want
the VBA code, split it with the macro recorder on.

Hth,
Merjet

Concatenate
 

tried it. did not work.

"Mike H." wrote:

Try this:
Sub CreateData()
Dim x As Double
Dim dataarray(50000, 3) As Variant
Let x = 1
Do While True
If Cells(x, 1).Value = Empty Then Exit Do
fnd = fnd + 1
dataarray(fnd, 1) = Cells(x, 1).Value
dataarray(fnd, 2) = Cells(x, 2).Value
dataarray(fnd, 3) = Cells(x, 3).Value
x = x + 1
Loop
If fnd 0 Then
Sheets("sheet2").Select
For x = 1 To fnd
Sheet2.Cells(x, 1).Value = "quantity#" & dataarray(x, 1) & "label#"
& dataarray(x, 2) & "style#" & dataarray(x, 3)
Next
End If




End Sub


"maju" wrote:

Please i need help. I am working on a large file.
I need my output to be place in another column.
The end result should look like this.
I know that i have to use the for loop and concatenate.
quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl
until the end
Sample data
Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

.

Concatenate
 

This works but i need it to stop at the last cell with data

Sub combine_columns()
Dim town As Variant
Dim site As Variant
Range("L:L").Select
For Each site In Range("A:A").SpecialCells(xlCellTypeConstants, 2)
For Each town In Range("J:J").SpecialCells(xlCellTypeConstants, 2)
ActiveCell.FormulaR1C1 = "=CONCATENATE(""po#"", RC[-11], ""st#"",
RC[-2])"
ActiveCell.Offset(1, 0).Select
Next
Next
End Sub


"Mike H." wrote:

Try this:
Sub CreateData()
Dim x As Double
Dim dataarray(50000, 3) As Variant
Let x = 1
Do While True
If Cells(x, 1).Value = Empty Then Exit Do
fnd = fnd + 1
dataarray(fnd, 1) = Cells(x, 1).Value
dataarray(fnd, 2) = Cells(x, 2).Value
dataarray(fnd, 3) = Cells(x, 3).Value
x = x + 1
Loop
If fnd 0 Then
Sheets("sheet2").Select
For x = 1 To fnd
Sheet2.Cells(x, 1).Value = "quantity#" & dataarray(x, 1) & "label#"
& dataarray(x, 2) & "style#" & dataarray(x, 3)
Next
End If




End Sub


"maju" wrote:

Please i need help. I am working on a large file.
I need my output to be place in another column.
The end result should look like this.
I know that i have to use the for loop and concatenate.
quantity#5150225label#5150225stlye#5dgl
quantity#6000225label#5150225stlye#5apl
until the end
Sample data
Quantity Label Stlye
5150225 5150225 5dgl
6000225 5150225 5apl
5150225 5150225 5dgl
5150225 5150225 5apl
5150225 5150225 5dgl

.

Concatenate
 

Thanks for information
ramesh

*** Sent via Developersdex http://www.developersdex.com ***