Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate
Formula like:
=($A$1&"#"&A2&$B$1&"#"&B2&$C$1&"#"&C2) ??? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate If | Excel Worksheet Functions | |||
Concatenate help | Excel Discussion (Misc queries) | |||
Concatenate | Excel Discussion (Misc queries) | |||
IF, IF, Concatenate? | Excel Worksheet Functions | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |