Merging Rows that contain the same word

[marcidee]

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

7801	T9	15/06/2018	Cleaning	Bags, Bleach, Sponges	6.00
7402	T9	05/06/2018	Accommodation/ meeting room	Brighton accommodation	49.99
7402	T9	11/06/2018	Accommodation/ meeting room	Brighton room hire	12.00
7406	T9	06/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	18/05/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	04/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	11/06/2018	Refreshment-milk/sugar/etc	Milk	2.58
7406	T9	15/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	22/05/2018	Refreshment-milk/sugar/etc	Milk, Sugar	1.99

<tbody>
</tbody>

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

 

[nemmi69]

Does this help?

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

 

[marcidee]

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

Does this help?

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

 

[marcidee]

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:

EXPENSE CLAIM FORM
Employee Name:		Sian Watts
Expense Date:		May-June 18
Name		Division (select)	Dept	Nom	Nom2	TC	Expense Date	Description	Details	Amount	Total
Sian Watts	SW	Head Office	16	7050	7402	T9	11/06/2018	Accommodation/ meeting room	Brighton room hire	12.00	77.72
		7402	T9	05/06/2018	Accommodation/ meeting room	Brighton accommodation	49.99
	7406	T9	22/05/2018	Refreshment-milk/sugar/etc	Milk, Sugar	1.99
	7406	T9	06/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
	7406	T9	18/05/2018	Refreshment-milk/sugar/etc	Milk	1.29
	7406	T9	04/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
	7406	T9	11/06/2018	Refreshment-milk/sugar/etc	Milk	2.58
	7406	T9	15/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
	7801	T9	15/06/2018	Cleaning	Bags, Bleach, Sponges	6.00
	#N/A	###
	#N/A	###
	#N/A	###
	#N/A	###
	#N/A	###
	#N/A	###
	#N/A	###
	#N/A	###
	#N/A	###

<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> </tbody>

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

7801	T9	15/06/2018	Cleaning	Bags, Bleach, Sponges	6.00
7402	T9	05/06/2018	Accommodation/ meeting room	Brighton accommodation	49.99
7402	T9	11/06/2018	Accommodation/ meeting room	Brighton room hire	12.00
7406	T9	06/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	18/05/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	04/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	11/06/2018	Refreshment-milk/sugar/etc	Milk	2.58
7406	T9	15/06/2018	Refreshment-milk/sugar/etc	Milk	1.29
7406	T9	22/05/2018	Refreshment-milk/sugar/etc	Milk, Sugar	1.99

<tbody>
</tbody>

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