Merging Rows that contain the same word

M

marcidee

Board Regular
Joined
May 23, 2016
Messages
196
Office Version
  1. 2019
Please can you help with a script - I have a spreadsheet used for expenses - if they have purchased the same item several times I would like to merge those rows and the totals so the item only appears i one row

[TABLE="width: 460"]
<tbody>[TR]
[TD="width: 50, bgcolor: transparent"]7801
[/TD]
[TD="width: 32, bgcolor: transparent"]T9
[/TD]
[TD="width: 119, bgcolor: transparent"]15/06/2018
[/TD]
[TD="width: 158, bgcolor: transparent"]Cleaning
[/TD]
[TD="width: 142, bgcolor: transparent"]Bags, Bleach, Sponges
[/TD]
[TD="width: 112, bgcolor: transparent"]6.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7402
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]05/06/2018
[/TD]
[TD="bgcolor: transparent"]Accommodation/ meeting room
[/TD]
[TD="bgcolor: transparent"]Brighton accommodation
[/TD]
[TD="bgcolor: transparent"]49.99
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7402
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]11/06/2018
[/TD]
[TD="bgcolor: transparent"]Accommodation/ meeting room
[/TD]
[TD="bgcolor: transparent"]Brighton room hire
[/TD]
[TD="bgcolor: transparent"]12.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]06/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]18/05/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]04/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]11/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]2.58
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]15/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]22/05/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk, Sugar
[/TD]
[TD="bgcolor: transparent"]1.99
[/TD]
[/TR]
</tbody>[/TABLE]

So in the example above- the details that are in column 'F' Milk would merge to one row and the total of all those rows would appear in column K - the row that says 'Milk, Sugar' would not merge with the others.

I would appreciate any help

Thanks

Marc
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Does this help?

Code: 
Dim Ast As Long
Dim AEnd  As Long
Dim ACnt  As Long
Dim BCnt  As Long
Dim PrimeCol As String
Dim MergeCol As String


Sub MergeDataTest()
'Fixed range at the moment. Ast needs to be set with 1st row, AEnd last row
Ast = 1
AEnd = 9
ACnt = Ast
BCnt = Ast + 1
PrimeCol = "E"
MergeCol = "F"


Do While ACnt <= AEnd
    Do While BCnt <= AEnd
        If Range(PrimeCol & ACnt).Value = Range(PrimeCol & BCnt).Value Then
            Range(MergeCol & ACnt).Value = Range(MergeCol & ACnt).Value + Range(MergeCol & BCnt).Value
            Range("A" & BCnt & ":F" & BCnt).Delete Shift:=xlUp
            AEnd = AEnd - 1
        Else
            BCnt = BCnt + 1
        End If
    Loop
    ACnt = ACnt + 1
    BCnt = ACnt + 1
Loop
End Sub
 
Upvote 0
Thank you for this - is there any way I can send the spreadsheet - as it has some merged rows it is crashing - if I in merge the rows strange things happen

nemmi69 said:
Does this help?

Code: 
Dim Ast As Long
Dim AEnd  As Long
Dim ACnt  As Long
Dim BCnt  As Long
Dim PrimeCol As String
Dim MergeCol As String


Sub MergeDataTest()
'Fixed range at the moment. Ast needs to be set with 1st row, AEnd last row
Ast = 1
AEnd = 9
ACnt = Ast
BCnt = Ast + 1
PrimeCol = "E"
MergeCol = "F"


Do While ACnt <= AEnd
    Do While BCnt <= AEnd
        If Range(PrimeCol & ACnt).Value = Range(PrimeCol & BCnt).Value Then
            Range(MergeCol & ACnt).Value = Range(MergeCol & ACnt).Value + Range(MergeCol & BCnt).Value
            Range("A" & BCnt & ":F" & BCnt).Delete Shift:=xlUp
            AEnd = AEnd - 1
        Else
            BCnt = BCnt + 1
        End If
    Loop
    ACnt = ACnt + 1
    BCnt = ACnt + 1
Loop
End Sub
Click to expand...
 
Upvote 0
I have just realised that I have given you the wrong column headings (apologies) the description (milk etc) is column 'J' and the cost is column 'K'

The spreadsheet look like this:

[TABLE="width: 836"]
<colgroup><col width="156" style="width: 117pt; mso-width-source: userset; mso-width-alt: 5546;"> <col width="58" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5233;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1365;" span="2"> <col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1137;"> <col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4238;"> <col width="158" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5632;"> <col width="142" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5034;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 361, bgcolor: white, colspan: 3"]EXPENSE CLAIM FORM[/TD]
[TD="width: 753, bgcolor: white, colspan: 9"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Employee Name:[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: #D9E1F2"]Sian Watts[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Expense Date:[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: #D9E1F2"]May-June 18[/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Name[/TD]
[TD="bgcolor: #D9E1F2"] [/TD]
[TD="bgcolor: #D9E1F2"]Division (select)[/TD]
[TD="bgcolor: #D9E1F2"]Dept[/TD]
[TD="bgcolor: #D9E1F2"]Nom[/TD]
[TD="bgcolor: #D9E1F2"]Nom2[/TD]
[TD="bgcolor: #D9E1F2"]TC[/TD]
[TD="bgcolor: #D9E1F2"]Expense Date[/TD]
[TD="bgcolor: #D9E1F2"]Description[/TD]
[TD="bgcolor: #D9E1F2"]Details[/TD]
[TD="bgcolor: #D9E1F2"]Amount[/TD]
[TD="bgcolor: #D9E1F2"]Total[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sian Watts[/TD]
[TD="bgcolor: transparent"]SW[/TD]
[TD="bgcolor: transparent"]Head Office[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]7050[/TD]
[TD="bgcolor: transparent"]7402[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]11/06/2018[/TD]
[TD="bgcolor: transparent"]Accommodation/ meeting room[/TD]
[TD="bgcolor: transparent"]Brighton room hire[/TD]
[TD="bgcolor: transparent"]12.00[/TD]
[TD="bgcolor: transparent, align: right"]77.72[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 4"] [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7402[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]05/06/2018[/TD]
[TD="bgcolor: transparent"]Accommodation/ meeting room[/TD]
[TD="bgcolor: transparent"]Brighton accommodation[/TD]
[TD="bgcolor: transparent"]49.99[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7406[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]22/05/2018[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc[/TD]
[TD="bgcolor: transparent"]Milk, Sugar[/TD]
[TD="bgcolor: transparent"]1.99[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7406[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]06/06/2018[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc[/TD]
[TD="bgcolor: transparent"]Milk[/TD]
[TD="bgcolor: transparent"]1.29[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7406[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]18/05/2018[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc[/TD]
[TD="bgcolor: transparent"]Milk[/TD]
[TD="bgcolor: transparent"]1.29[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7406[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]04/06/2018[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc[/TD]
[TD="bgcolor: transparent"]Milk[/TD]
[TD="bgcolor: transparent"]1.29[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7406[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]11/06/2018[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc[/TD]
[TD="bgcolor: transparent"]Milk[/TD]
[TD="bgcolor: transparent"]2.58[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7406[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]15/06/2018[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc[/TD]
[TD="bgcolor: transparent"]Milk[/TD]
[TD="bgcolor: transparent"]1.29[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"]7801[/TD]
[TD="bgcolor: transparent"]T9[/TD]
[TD="bgcolor: transparent"]15/06/2018[/TD]
[TD="bgcolor: transparent"]Cleaning[/TD]
[TD="bgcolor: transparent"]Bags, Bleach, Sponges[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]###[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]



marcidee said:
Please can you help with a script - I have a spreadsheet used for expenses - if they have purchased the same item several times I would like to merge those rows and the totals so the item only appears i one row

[TABLE="width: 460"]
<tbody>[TR]
[TD="width: 50, bgcolor: transparent"]7801
[/TD]
[TD="width: 32, bgcolor: transparent"]T9
[/TD]
[TD="width: 119, bgcolor: transparent"]15/06/2018
[/TD]
[TD="width: 158, bgcolor: transparent"]Cleaning
[/TD]
[TD="width: 142, bgcolor: transparent"]Bags, Bleach, Sponges
[/TD]
[TD="width: 112, bgcolor: transparent"]6.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7402
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]05/06/2018
[/TD]
[TD="bgcolor: transparent"]Accommodation/ meeting room
[/TD]
[TD="bgcolor: transparent"]Brighton accommodation
[/TD]
[TD="bgcolor: transparent"]49.99
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7402
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]11/06/2018
[/TD]
[TD="bgcolor: transparent"]Accommodation/ meeting room
[/TD]
[TD="bgcolor: transparent"]Brighton room hire
[/TD]
[TD="bgcolor: transparent"]12.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]06/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]18/05/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]04/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]11/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]2.58
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]15/06/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk
[/TD]
[TD="bgcolor: transparent"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7406
[/TD]
[TD="bgcolor: transparent"]T9
[/TD]
[TD="bgcolor: transparent"]22/05/2018
[/TD]
[TD="bgcolor: transparent"]Refreshment-milk/sugar/etc
[/TD]
[TD="bgcolor: transparent"]Milk, Sugar
[/TD]
[TD="bgcolor: transparent"]1.99
[/TD]
[/TR]
</tbody>[/TABLE]

So in the example above- the details that are in column 'F' Milk would merge to one row and the total of all those rows would appear in column K - the row that says 'Milk, Sugar' would not merge with the others.

I would appreciate any help

Thanks

Marc
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
INDEX/AGGREGATE/ROW Formula
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top