A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel 2013 Merge Data



 
 
Thread Tools Display Modes
  #1  
Old April 15th 14, 07:26 PM posted to microsoft.public.excel.programming
jfcby
external usenet poster
 
Posts: 33
Default Excel 2013 Merge Data

Hi,

I have a worksheet that I trying to merge data from multiple cells into one cell.

Lets say that worksheet one columns A1-E1 are labeled as Column 1 through Column 5. Each cell in column 1 is labeled row1-column1, row2-column1, etc.

My macro will put the column header and row1-column1 etc in worksheet 2 row 1 column on. The problem I'm having is when the cell text is wraped the each value is on a separate line like...

row1-column1
row2-column1
row3-column1

I need the text to side by side like row1-column1, row2-column1, row3-column1.

How can I get my macro to merge the the data so that it will be displayed like row1-column1, row2-column1, row3-column1 when the cell is merged?

Macro:

'Copy sheet1 data to sheet2
is2 = 2
For is1 = 4 To s1rowNum 'rows
Worksheets(wksName2).Range("A" & is2) = Worksheets(wksName1).Range("A" & is1)
Worksheets(wksName2).Range("B" & is2) = Worksheets(wksName1).Range("B" & is1)
Worksheets(wksName2).Range("C" & is2) = Worksheets(wksName1).Range("C" & is1)

For is3 = 1 To 3 'columns
If Worksheets(wksName1).Cells(is1, is3) <> "" Then
cTrim1 = Trim(Worksheets(wksName1).Cells(3, is3).Text)
cTrim2 = Worksheets(wksName1).Cells(is1, is3).Text
'vCell = Trim(vCell & Worksheets(wksName1).Cells(3, is3).Text & "-|-" & " (" & Worksheets(wksName1).Cells(is1, is3).Text & ") ")
vCell = vCell & cTrim1 & cTrim2
End If
Next

Thanks for your help,
Frankie
Ads
  #2  
Old April 15th 14, 08:38 PM posted to microsoft.public.excel.programming
GS[_2_]
external usenet poster
 
Posts: 3,516
Default Excel 2013 Merge Data

In a standard module...


Option Explicit

Sub XferColsToRows()
Dim vData, n&
vData = Sheets("Sheet1").UsedRange
For n = LBound(vData) To UBound(vData, 2)
With Sheets("Sheet2")
.Cells(n, 1).Value = _
Join(Application.Transpose(Application.Index(vData , 0, n)),
",")
End With 'Sheets("Sheet2")
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #3  
Old April 16th 14, 04:03 PM posted to microsoft.public.excel.programming
GS[_2_]
external usenet poster
 
Posts: 3,516
Default Excel 2013 Merge Data

Alternatively...

Sub XferColsToRows()
Dim vData, n&
vData = Sheets("Sheet1").UsedRange
For n = LBound(vData) To UBound(vData, 2)
Sheets("Sheet2").Cells(n, 1).Value = _
Join(Application.Transpose(Application.Index(vData , 0, n)), ",")
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ListObjects - VBA code that worked in previous versions of Excel doesnot work in Excel 2013 hdf Excel Programming 2 November 17th 14 02:34 AM
Macro from older version into Office 2013 on Windows 8 [email protected] Excel Programming 3 March 5th 14 08:08 PM
Help creating new functions for Excel 2013 Jschock New Users to Excel 4 August 20th 13 01:25 AM
Does Publisher 2013 have autoflow? Martin [_2_] Excel Discussion (Misc queries) 0 March 12th 13 05:22 PM
Update calendar for 2013 - 2020 with events from the database sheet ganzzu Excel Programming 0 February 15th 13 02:22 PM


All times are GMT +1. The time now is 11:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.