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