Combine Excel rows - macro needed.

[shaneferguson]

Good Afternoon everyone,

First post - so i hope it's in the correct place. Forgive me if it's
not.

I have a list of membership details that are in an Excel workbook.

The data is split into thousands of rows, and 2 columns.

Each row represents a member defined by a number.

The first column is the membership number, and the second column is the
membership details.

eg:


01 00001 JOINED 12/07/97 LEFT 04/12/05
02 00001 INJURED HAND 02/02/04
03 00001 RETIRED 01/01/06
04 00002 WADES POTTERY
05 00002 DECEASED 17/03/99
06 00003 MATERNITY LEAVE 31/07/03
07 00004 SWAPPED JOBS - SENIOR SF PICKER
08 00005 BENEFITS CLAIMED - £100.00
09 00005 SICKNESS LEAVE AUTHORISED


As you can see - each member can have multiple entries, each on a
different row.

What I am trying/need to do, is combine each members details - all the
rows, into one (one row for each member).

eg:

01 00001 JOINED 12/07/97 LEFT 04/12/05 INJURED HAND
02/02/04
02 00002 WADES POTTERY DECEASED 17/03/99
03 00003 MATERNITY LEAVE 31/07/03
04 00004 SWAPPED JOBS - SENIOR SF PICKER
05 00005 BENEFITS CLAIMED - £100.00 SICKNESS LEAVE
AUTHORISED

At the moment I have been doing this by using the formula:

=c3365&" "&c3366&" "&c3367 etc....etc...

This works fine, but is taking a very long time.

Is there a way to use a macro to make this task easier...?

Ideally:

1) Create a macro with shortcut key.
2) Highlight rows/column to merge.
3) Press shortcut key
4) Rows merge into one.

I could then, 5) Manually delete the old rows that are no longer
needed.

I'm absolutely no good with macros, and am stuck - so any help/advice
you could offer would be most appreciated.

I have tried to attach the file I am working on, but it's above the
100KB limit - so I have uploaded it to my web storage and provided the
link below:

2.1mb excel file - Direct link:

http://www.box.net/public/static/4lg2mzm495.zip

Many thanks,
Shane

 

[colofnature]

Try this:

Sub combiner()
For Each c In Intersect(Selection.Cells, Columns(Selection.Column))
If c.Offset(1, 0).Value = c.Value And c.Value <> Empty Then
c.Offset(, 1).Value = c.Offset(, 1).Value & " " & c.Offset(1, 1).Value
c.Offset(1, 0).EntireRow.Delete
End If
Next
End Sub


Col

 

[shaneferguson]

Hi Col,

Many thanks for your reply.

However, I am pretty rubbish at Macros.

How do I create this macro and make a shortcut to run it...?

I've opened Tools ---> Macros ---> Visual Basic Editor, but I can no
find where to create a shortcut key.

Once this is done, do I select the top row of a member's details, the
run the macro - or do I select all the member's rows and run the macr
- or something else.

Sorry to ask such newbie questions, but I appreciate the help/advice.

Kindest Regards,
Shane :-