Merging Multiple Rows based on Criteria -Excel VBA

Share Now


PROBLEM STATEMENT

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

SOLUTION


Sub mergemultiplelines()

Dim lastrow As Long
Dim i As Integer

lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

With ActiveSheet

'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)
.Rows(i).Delete
End If

Next i

End With

APPROACH

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.

STEPS TO PERFORM

1. Go to developer Tab else Press Alt + F11
2.Click on Visual Basic icon
3.Go to Insert click on Module

User defined function in excel

4.Paste below code in standard module

CODE_UDF


Share Now
December 27, 2017

0 responses on "Merging Multiple Rows based on Criteria -Excel VBA"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy

    Drop us a Query

    Call Us: +91 8826547882

    Drop us a Query