Consolidating Rows with same Resource Name

OTPM · #1 05-30-2017, 03:10 AM

Hi Folks
I am in the process of writing an Excel Capacity Management Tool and am looking for help in combining rows where the Resource Name is the same as the previous row.
For example in the sample attached is row 8 has the same Resource Name as row 7 then I want to merge the values from Columns F to NL into row 7 and do the same for each row on the worksheet.
Note that columns F to NL are calculated based on the dates in columns "C

". I intend to remove all formulas from columns F:NL before I run the routine to consolidate the rows.
I am struggling with the code for this so any help/advice would be appreciated.
Thanks in anticipation.
Tony

NoSparks · #2 05-30-2017, 07:38 AM

Start at the bottom of your data and work your way up the rows one at a time looking to see if the Resource Name matches the row above. If it does, add the F:NL values of the row you're looking at to the row above. Something along the lines of this

Code:

With Sheet1
    For i = LastRow To 8 Step -1
        If .Cells(i, 2).Value = .Cells(i - 1, 2).Value Then
            Set rng = .Range("F" & i & ":NL" & i)
            For Each cel In rng
                cel.Offset(-1).Value = cel.Offset(-1).Value & cel.Value
            Next cel
        End If
    Next i
End With

OTPM · #3 05-30-2017, 08:30 AM

Hi
Many thanks for your prompt response.
I will give that a go tomorrow.
Kind regards
Tony

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Consolidating various word docs in one	Max Downham	Word	6	11-23-2015 05:07 PM
Need help consolidating data from separate sheets	tiwas	Excel	1	10-07-2014 04:57 AM
Consolidating Sentences into One Paragraph	ctsolar	Word	4	12-16-2013 04:50 PM
Consolidating data using Macro	mrjamez	Excel Programming	2	05-22-2012 06:50 AM
Help with consolidating multiple records into one	wbiggs2	Excel	0	11-30-2006 01:02 PM