Tricky data merge question...

  • Thread starter Thread starter jgoodell
  • Start date Start date
J

jgoodell

Hello!

I'm so happy to have found this forum. I have been struggling with the
following problem for some time:

I have a data set with the following column headers:

HN Number
Piece
Reference

The data looks like:

HN_Number Piece Reference
HN0001 Sonata in C K. 279 (189e)
HN0001 Sonata in d K. 342
HN0001 Sonata in Eb K. 365 (453d)
HN0001 Sonata in B K. 323
HN0002 Quartet in D K. 345
HN0002 Sonatas Book I editorial dfd22k
HN0002 Simple Song KV. 4534
etc.

What I would like to do is merge the data so that all rows with the
same HN Number (unique key) are combined into one field called
contents, so the data appears with only 2 fields:

HN_Number
Contents

And the "Contents" field is a combination of the Piece and Reference
fields from above:

HN_Number Contents
HN0001 Sonata in C (K. 279 (189e)), Sonata in d (K. 342),
Sonata in Eb (K. 365 (453d)), etc.

so all the rows for HN0001, for example, are combined into one field
called contents; the Reference field is placed next to the Piece field
for that same row in () and each entry is separated by a COMMA.

Is this possible?

Thanks in advance!

John
 
Hi John

I think this macro will do what you want. Make sure you save the
records before you run it. You should be able to copy and paste it
into your VBA module and run

I have assumed you statr at cell A1 and use cols A, B and C

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
 
David,

Thanks so much for the reply!

I copied that code and ran the macro and got the following result (in
one field):

HN0001 1. Piano Sonata in C[K. 279 (189d)], 2. Piano Sonata in F[K. 280
(189e)], 3. Piano Sonata in B-flat[K. 281 (189f)], 4. Piano Sonata in
E-flat[K. 282 (189h0], 5. Piano Sonata in G[K. 283 (189h)], 6. Piano
Sonata in D[K. 284 (205b)], 7. Piano Sonata in C[K. 309 (284b)], 8.
Piano Sonata in a[K. 310 (300d)], 9. Piano Sonata in D[K. 311 (284c)]


It's fantastic except that I'd like the first part (HN0001) to be in a
column by itself (A) and all the other stuff to be in the next column
(B).

Does that make sense? Perhaps I can fun a formula on this since the
first part (HN0001) is a standard length - I'll play around with it,
but if you can tweak the macro, that would be great! All this Visual
Basic stuff is foreign to me :)

Thanks again,

John
 
David,

I think I got it. I just removed that last section of the macro to
combine A & B :)

I think everything works now. Thanks again for your help - what a
fantastic forum.

John
 
Back
Top