Skip to content

Instantly share code, notes, and snippets.

@bhavin192

bhavin192/MergeMacro.bas

Last active July 24, 2021 12:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bhavin192/de83bf9542f2dcc31d97cec794fce54e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bhavin192/de83bf9542f2dcc31d97cec794fce54e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. bhavin192 revised this gist Jul 24, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    Attribute VB_Name = "MergeMacro"

    ' Copyright: (c) 2018-2021 by Bhavin Gandhi.
    ' http://git.io/MergeMacro.bas
    ' 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.
  2. bhavin192 revised this gist Jul 24, 2021. 1 changed file with 36 additions and 15 deletions.
    51 changes: 36 additions & 15 deletions MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,25 @@
    Attribute VB_Name = "MergeMacro"
    'Copyright: (c) 2018 by Bhavin Gandhi
    'License: GNU GPL v3, see https://www.gnu.org/licenses/gpl-3.0.en.html for more details

    ' Copyright: (c) 2018-2021 by Bhavin Gandhi.
    ' http://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
    ' Declaration
    Dim tallySheet As Variant
    Dim mergedSheet As Variant
    Dim rowIndex As Integer
    @@ -12,27 +28,33 @@ Sub processMerge()
    Dim keyFromRow As String
    Dim currentRow As Variant
    Dim mergeSheetRow As Integer
    Dim titleRows As Integer
    Dim keyColIndex As Integer

    'Initialise the variables
    ' 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 4 rows (Title etc.)
    For mergeSheetRow = 1 To 4
    ' 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 = 5 To tallySheet.UsedRange.Rows.Count - 1
    ' Actual merging
    For rowIndex = titleRows + 1 To tallySheet.UsedRange.Rows.Count - 1
    Set currentRow = tallySheet.Rows(rowIndex)
    keyFromRow = currentRow.Cells(1, 2).Text
    keyFromRow = currentRow.Cells(1, keyColIndex).Text

    If mergeDict.Exists(keyFromRow) Then
    For columnIndex = 5 To tallySheet.UsedRange.Columns.Count
    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
    @@ -42,15 +64,14 @@ Sub processMerge()
    mergeSheetRow = mergeSheetRow + 1
    mergeDict.Add Key:=keyFromRow, Item:=currentRow
    End If

    Next rowIndex

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

    'Clean dictionary
    ' Clean dictionary
    Set mergeDict = Nothing
    End Sub

  3. bhavin192 revised this gist Jan 1, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    Attribute VB_Name = "MergeMacro"
    'Copyright: (c) 2018 by Bhavin Gandhi
    'License: GNU GPL v3, see https://www.gnu.org/licenses/gpl-3.0.en.html for more details
    Sub proc()
    Sub processMerge()

    'Declaration
    Dim tallySheet As Variant
    @@ -48,7 +48,7 @@ Sub proc()
    'Copy last row of Total
    tallySheet.Rows(rowIndex).Copy
    mergedSheet.Rows(mergeSheetRow).Insert Shift:=xlDown
    MsgBox (mergeDict.Count)
    MsgBox ("Total " & mergeDict.Count & " parties processed.")

    'Clean dictionary
    Set mergeDict = Nothing
  4. bhavin192 revised this gist Jan 1, 2018. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,6 @@
    Attribute VB_Name = "MergeMacro"
    'Copyright: (c) 2018 by Bhavin Gandhi
    'License: GNU GPL v3, see https://www.gnu.org/licenses/gpl-3.0.en.html for more details
    Sub proc()

    'Declaration
  5. bhavin192 revised this gist Jan 1, 2018. 1 changed file with 44 additions and 44 deletions.
    88 changes: 44 additions & 44 deletions MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -1,54 +1,54 @@
    Attribute VB_Name = "MergeMacro"
    Sub proc()

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

    'Initialise the variables
    Set tallySheet = Sheets(1)
    Sheets.Add After:=tallySheet
    ActiveSheet.Name = "MergedSheet"
    Set mergedSheet = ActiveSheet
    Set mergeDict = CreateObject("Scripting.Dictionary")
    'Initialise the variables
    Set tallySheet = Sheets(1)
    Sheets.Add After:=tallySheet
    ActiveSheet.Name = "MergedSheet"
    Set mergedSheet = ActiveSheet
    Set mergeDict = CreateObject("Scripting.Dictionary")

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

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

    If mergeDict.Exists(keyFromRow) Then
    For columnIndex = 5 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
    'Copy first 4 rows (Title etc.)
    For mergeSheetRow = 1 To 4
    tallySheet.Rows(mergeSheetRow).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
    Next mergeSheetRow

    'Copy last row of Total
    tallySheet.Rows(rowIndex).Copy
    mergedSheet.Rows(mergeSheetRow).Insert Shift:=xlDown
    MsgBox (mergeDict.Count)
    'Actual merging
    For rowIndex = 5 To tallySheet.UsedRange.Rows.Count - 1
    Set currentRow = tallySheet.Rows(rowIndex)
    keyFromRow = currentRow.Cells(1, 2).Text

    If mergeDict.Exists(keyFromRow) Then
    For columnIndex = 5 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 (mergeDict.Count)

    'Clean dictionary
    Set mergeDict = Nothing
    'Clean dictionary
    Set mergeDict = Nothing
    End Sub

  6. bhavin192 revised this gist Jan 1, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    Attribute VB_Name = "Module1"
    Attribute VB_Name = "MergeMacro"
    Sub proc()

    'Declaration
    @@ -27,7 +27,7 @@ Next mergeSheetRow
    'Actual merging
    For rowIndex = 5 To tallySheet.UsedRange.Rows.Count - 1
    Set currentRow = tallySheet.Rows(rowIndex)
    keyFromRow = currentRow.Cells(1, 4).Text
    keyFromRow = currentRow.Cells(1, 2).Text

    If mergeDict.Exists(keyFromRow) Then
    For columnIndex = 5 To tallySheet.UsedRange.Columns.Count
  7. bhavin192 created this gist Dec 31, 2017.
    54 changes: 54 additions & 0 deletions MergeMacro.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,54 @@
    Attribute VB_Name = "Module1"
    Sub proc()

    '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

    'Initialise the variables
    Set tallySheet = Sheets(1)
    Sheets.Add After:=tallySheet
    ActiveSheet.Name = "MergedSheet"
    Set mergedSheet = ActiveSheet
    Set mergeDict = CreateObject("Scripting.Dictionary")

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

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

    If mergeDict.Exists(keyFromRow) Then
    For columnIndex = 5 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 (mergeDict.Count)

    'Clean dictionary
    Set mergeDict = Nothing
    End Sub