how to merge multiple rows into one document

  • Thread starter Thread starter flyers2thecup
  • Start date Start date
F

flyers2thecup

Sorry for all the posts, but i just found this forum, and i have quite
few issued i've been "working around" rather than finding answers to.
this forum is very informative!!!


Code
-------------------


COL A COL B COL C COL D COL E
name shares date1 date2 price
ROW1 John Smith 25 01/01/04 08/01/04 25.10
ROW2 John Smith 30 01/01/04 07/10/04 26.10
ROW3 John Smith 40 01/01/04 06/10/04 25.10


-------------------


as it stands right now we have to manually copy and paste COL B - COL
for each additional row to the top row. in this case, john smith woul
have 8 additional columns in row 1. col b - e from row 1 and 2 ar
entered into row 1, and row 2 and 3 would be deleted. this is th
only way i know how to get the mail merge to work.

i need to send a letter to each employee. if i don't condense it down
then john smith would end up getting three letters. we want it t
basically be a statement of sorts. one letter, giving the breakdown o
the 12 cells (b1:e3).

am i explaining that well enough? :confused:

let me know if not and i'll try and explain more.

as usual, thanks in advance!!
 
Hi
though this could be done in Excel/Word (with vBA) I would recommend in
this case to use a real database and create a report from this database
(e.g. MS access) grouping the share transactions for each employee
 
One way (from a non-Access user!):

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim myRng As Range
Dim iRow As Long

Set wks = Worksheets("sheet1")

With wks

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "E"))

With myRng
.Sort key1:=.Columns(1), order1:=xlAscending, _
header:=xlNo, MatchCase:=False
End With

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Copy _
Destination:=.Cells(iRow - 1, "F")
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top