MergeMacro.bas

Attribute VB_Name = "MergeMacro"

' Copyright: (c) 2018-2021 by Bhavin Gandhi.
' https://git.io/MergeMacro.bas
' https://gist.github.com/bhavin192/de83bf9542f2dcc31d97cec794fce54e
' License: GNU GPL v3+, see
' https://www.gnu.org/licenses/gpl-3.0.en.html for more details.

' 0. Open the purchase register sheet generated by Tally.
' 1. Open Visual Basic Editor by pressing Alt + F11.
' 2. Press Ctrl + M to import this file or go to File -> Import
'    File…. Select MergeMacro.bas, and click Open.
' 3. Select Modules -> MergeMacro from the Project panel on the left
'    side.
' 4. Run macro by pressing F5, click Run for the processMerge.
' 5. Now, it should show a message like: "Total 22 parties processed."
' 6. You can close the Visual Basic Editor now.


Sub processMerge()

    ' Declaration
    Dim tallySheet As Variant
    Dim mergedSheet As Variant
    Dim rowIndex As Integer
    Dim columnIndex As Integer
    Dim mergeDict As Variant
    Dim keyFromRow As String
    Dim currentRow As Variant
    Dim mergeSheetRow As Integer
    Dim titleRows As Integer
    Dim keyColIndex As Integer

    ' Initialise the variables
    Set tallySheet = Sheets(1)
    Sheets.Add After:=tallySheet
    ActiveSheet.Name = "MergedSheet"
    Set mergedSheet = ActiveSheet
    Set mergeDict = CreateObject("Scripting.Dictionary")
    ' First 4 rows have things like headline, titles and FY
    titleRows = 4
    ' The GSTIN/UIN is used as key for merging, it's at column F i.e. 6
    keyColIndex = 6

    ' Copy first titleRows rows (Title etc.)
    For mergeSheetRow = 1 To titleRows
        tallySheet.Rows(mergeSheetRow).Copy
        mergedSheet.Rows(mergeSheetRow).Insert Shift:=xlDown
    Next mergeSheetRow

    ' Actual merging
    For rowIndex = titleRows + 1 To tallySheet.UsedRange.Rows.Count - 1
        Set currentRow = tallySheet.Rows(rowIndex)
        keyFromRow = currentRow.Cells(1, keyColIndex).Text

        If mergeDict.Exists(keyFromRow) Then
            For columnIndex = keyColIndex + 1 To tallySheet.UsedRange.Columns.Count
                mergeDict(keyFromRow).Cells(1, columnIndex).Value = mergeDict(keyFromRow).Cells(1, columnIndex).Value + currentRow.Cells(1, columnIndex).Value
            Next columnIndex
        Else
            currentRow.Copy
            mergedSheet.Rows(mergeSheetRow).Insert Shift:=xlDown
            Set currentRow = mergedSheet.Rows(mergeSheetRow)
            mergeSheetRow = mergeSheetRow + 1
            mergeDict.Add Key:=keyFromRow, Item:=currentRow
        End If

    Next rowIndex

    ' Copy last row of Total
    tallySheet.Rows(rowIndex).Copy
    mergedSheet.Rows(mergeSheetRow).Insert Shift:=xlDown
    MsgBox ("Total " & mergeDict.Count & " parties processed.")

    ' Clean dictionary
    Set mergeDict = Nothing
End Sub