Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Record Merge Macro
Hello, I am trying to merge 3 columns to 2 in the following way: itemID title details 00-10114 Three Love Songs Nelson Keyes page 20 00-10114 Prelude and Fugue II (C Minor) David Diamond page 36 C 00-10114 Mobile Leslie Bassett page 18 00-10114 Homage to Milhaud Lou Harrison page 17 G 00-10114 Medley (Campfire on the Ice) Ross Lee Finney page 7 G Major 00-10114 Four Piano Pieces Bruce Wise page 10 00-10145 Motions Paul Sheftel page 3 A Minor 00-10145 Position Shift Rock Paul Sheftel page 13 C Major 00-10145 Mad for That Triad Paul Sheftel page 5 C Major You'll notice that the database has several titles for each line, eve though they are all part of the same itemID. I'd like to run a macro that combines the rows to columns using "," a a separator, so the result looks like: itemID title 00-10114 Three Love Songs (Nelson Keyes page 20), Prelude and Fugue I (C Minor) (David Diamond page 36 C), etc. with 3 columns converting to 2 columns (title & details being combine with ", ") for every unique itemID. I posted a similar request several years ago, actually, and kept th macro which worked great then, but I believe the database was slightl different -- it's not working now ;) Here's what I tried: ----------- Sub MergeRecords() Dim nRecords As Integer Dim X As Integer Range("A1").Select nRecords = ActiveCell.CurrentRegion.Rows.Count 'First pass will combine cells in cols B & C For X = 1 To nRecords - 1 ActiveCell.Offset(X, 1) _ = ActiveCell.Offset(X, 1) _ & "[" & ActiveCell.Offset(X, 2) _ & "]" ActiveCell.Offset(X, 2) = "" Next X 'Then combine Rows For X = nRecords - 1 To 1 Step -1 If ActiveCell.Offset(X, 0) _ = ActiveCell.Offset(X - 1, 0) Then ActiveCell.Offset(X - 1, 1) _ = ActiveCell.Offset(X - 1, 1) _ & ", " & _ ActiveCell.Offset(X, 1) ActiveCell.Offset(X, 0).EntireRow.Delete End If Next X 'Then combine Cols A & B nRecords = ActiveCell.CurrentRegion.Rows.Count For X = 1 To nRecords - 1 ActiveCell.Offset(X, 0) _ = ActiveCell.Offset(X, 0) _ & " " _ & ActiveCell.Offset(X, 1) ActiveCell.Offset(X, 1) = "" Next X End Sub ---------------------- Any suggestions (or an entirely new macro) would be greatl appreciated! Thanks so much!! Joh -- johngoodel ----------------------------------------------------------------------- johngoodell's Profile: http://www.excelforum.com/member.php...fo&userid=2653 View this thread: http://www.excelforum.com/showthread.php?threadid=39800 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find double record and merge | Excel Worksheet Functions | |||
Mail Merge <<Next Record | Excel Discussion (Misc queries) | |||
How to merge records into one record by customer's name? | New Users to Excel | |||
Different graphic for each record in mail merge document | Charts and Charting in Excel | |||
Record Macro - Record custom user actions | Excel Programming |