Notice: WC_Cart::get_cart_url is deprecated since version 2.5! Use wc_get_cart_url instead. in /home/swatipaliwal/public_html/excelcrazy.com/wp-includes/functions.php on line 3839
Excel Crazy

Merging Multiple Rows based on Criteria -Excel VBA

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
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 *

top
 

© LOKESH PALIWAL 1987 - 2017

Reporting Solutions | Data Consulting | Analytics Training