 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Concatenate
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 . 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 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. > No VBA is needed for that. Assuming data is A2:C2, put in > D2: ="quantity#" & A2 & "label1#" & B2 & "stlye#" & C2 [quoted text clipped - 3 lines] > Hth, > Merjet You might try this: ="Quantity#" & A2 & "Label#" & B2 & "Style#" & C2 Then copy it down. Mike > Please i need help. I am working on a large file. > I need my output to be place in another column. [quoted text clipped - 12 lines] > > . Formula like: =($A$1&"#"&A2&$B$1&"#"&B2&$C$1&"#"&C2) ??? 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 > Please i need help. I am working on a large file. > I need my output to be place in another column. [quoted text clipped - 12 lines] > > . Thx. i will try it and see if it works. > Try this: > Sub CreateData() [quoted text clipped - 35 lines] > > > > . 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 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 Thanks for information ramesh tried it. did not work. > Try this: > Sub CreateData() [quoted text clipped - 35 lines] > > > > . 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 > Try this: > Sub CreateData() [quoted text clipped - 35 lines] > > > > . |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.