Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Excel rows - macro needed.
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 :-) -- shaneferguson ------------------------------------------------------------------------ shaneferguson's Profile: http://www.excelforum.com/member.php...o&userid=36288 View this thread: http://www.excelforum.com/showthread...hreadid=560717 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Excel rows - macro needed.
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 -- colofnature ------------------------------------------------------------------------ colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356 View this thread: http://www.excelforum.com/showthread...hreadid=560717 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Excel rows - macro needed.
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 :- -- shaneferguso ----------------------------------------------------------------------- shaneferguson's Profile: http://www.excelforum.com/member.php...fo&userid=3628 View this thread: http://www.excelforum.com/showthread.php?threadid=56071 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to combine 'like' rows | Excel Discussion (Misc queries) | |||
Macro needed for inserting blank rows | Excel Worksheet Functions | |||
Macro Help Needed: Comparing cell values and deleting rows | Excel Discussion (Misc queries) | |||
Macro needed to delete rows | Excel Programming | |||
Combing and Adding Data in Multiple Rows (Macro Needed) | Excel Programming |