Combining duplicate rows into one

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

toadflax@australia.edu - 05 Sep 2005 07:44 GMT

Hi,

I am trying to combine three separate indexes (authors in the first
column, and volume & page number/s in the following columns) and really
only have Word and Excel 2000 to work with. The data is all as text,
because otherwise it messes up the number format.

In comma separated format, my data would currently look like this:
Author1,2: 1,13: 2-3,23: 4
Author1,31: 5,40: 45,52: 1
Author1,65: 4,66: 1-2,70: 4
Author2,1: 5,12: 45,15: 6
Author2,31: 29,35: 5,45: 2
Author2,53: 7,55: 2

What I want is all of an author's references combined, with the second
and third rows appended into the columns of the first, e.g.:
Author1,2: 1,13: 2-3,23: 4,31: 5,40: 45,52: 1,65: 4,66: 1-2,70: 4
Author 2,1: 5,12: 45,15: 6,31: 29,35: 5,45: 2,53: 7,55: 2

Each author's name can appear three times but may only be in once or
twice. In theory they could each have up to 70 columns following, as
I'm combining three separate indexes to 70 volumes (vols 1-30, 31-50
and 51-70).

Is there some nifty way to do this in Excel (or even Word)? I've done
some searching around help files and on the web without success so far,
so I'd be very grateful for any advice.

Thanks very much,
Michelle

Reply to this Message

Dave Peterson - 05 Sep 2005 14:06 GMT

How about a little macro?

Option Explicit
Sub testme()

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

Set wks = ActiveSheet
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

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, .Columns.Count) _
.End(xlToLeft).Offset(0, 1)
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

This does assume that your data is nicely sorted. And it doesn't check to see
if there's any room to paste. (But you said you have a maximum of 3 rows by 70
columns--210 column, so it should be ok. If you added a fourth row with 70,
you'd could have trouble since you only get 256 columns in excel.)

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

> Hi,
>
[quoted text clipped - 27 lines]
> Thanks very much,
> Michelle

Signature

Dave Peterson

Reply to this Message

toadflax@australia.edu - 06 Sep 2005 00:34 GMT

Woohoo! I do believe that worked. Thank you so much, Dave. I definitely
didn't fancy doing it manually with so many thousands of records. Now
we have to do the fiddly stuff, like deciding whether "Presley, E.
(Elvis)" is the same author as "Presley, E.A." but you saved us a whole
lot of work combining the exact duplicates.

I wasn't very clear about the volume numbers--we'd actually only have a
maximum of 70 columns *when combined* and even then only if one author
had written for or been mentioned in every volume. We have a set of
unindexed volumes to add though, so it's good to know I have some room
to move!

Thanks again,
Michelle

Reply to this Message

Dave Peterson - 06 Sep 2005 01:04 GMT

Glad it worked!

> Woohoo! I do believe that worked. Thank you so much, Dave. I definitely
> didn't fancy doing it manually with so many thousands of records. Now
[quoted text clipped - 10 lines]
> Thanks again,
> Michelle

Signature

Dave Peterson

Reply to this Message