Most of the time we deal with duplicate values in excel, same situation we have here. In our below sales dataset, we have Product Category, Products, and sales amount info in column A,B and C.
What we are looking for is the total of sales amount in “Column C” based on multiple records by product category. Also capture all the products as comma separated in one cell in column B. For reference check below screenshot
Dim lastrow As Long
Dim i As Integer
lastrow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
‘Sort data by Product Category before merging
.Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes
‘Backward loop to delete merged rows
For i = lastrow To 2 Step -1
‘Product category conditional check
If .Cells(i, 1) = .Cells(i – 1, 1) Then
.Cells(i – 1, 2) = .Cells(i – 1, 2) & “, ” & .Cells(i, 2)
.Cells(i – 1, 3) = .Cells(i – 1, 3) + .Cells(i, 3)
When using VBA to scan a text for number, the basic approach is like this:
1. Read each character in a given text.
2. See if it is number using ASCII values, If so, extract it.
3. Hold the number in variable.
4. Continue with next character.
1. Go to developer Tab else Press Alt + F11
2.Click on Visual Basic icon
3.Go to Insert click on Module
4.Paste below code in standard module