Concatenate

[maju]

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

..

 

[merjet]

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

 

[mikebres]

You might try this:

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

Then copy it down.

Mike

 

[Gojavid]

Formula like:

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

 

[Mike H.]

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]

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.

 

[maju]

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

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

 

[ramesh]

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

 

[merjet]

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

 

[maju]

tried it. did not work.

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]

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

 

[ramesh]

Thanks for information
ramesh