Consolidation of Multiple Excel Files

[suryasabniveesu]

Hi all,

Background: After implementation of GST in India, Buyers of Goods / Services have got a serious issue in form of Reconciliation of GST Input. Every month we (Buyers) have to download GSTR-2A files in JSON format and convert into Excel. GSTR-2A is nothing but GSTR-1 of Suppliers who declare their output tax on GST portal.

Issue: Buyers claim Input Credit in GSTR-3B after arriving it from Books of Accounts as on date of filing of Tax Return. Ideally Credit available in GSTR-2A and GSTR-3B should match. So the onus is on the buyers to follow up with suppliers continuosly to file their returns (GSTR-1).

Present Method: Every week we download GSTR-2A files from July-17 to Mar-18 and convert them into Excel files. Consolidate all excel files into one excel files by using Copy and Paste. Then we insert a Pivot and extract GSTN wise GST Input Details then compare with our Books of Accounts by using VLOOKUP.

Requirement: Is there any shortcut / Macro / Tip to consolidate multiple excel files into one file? Column Headings are fixed in all files. But a file may contain 2 or 3 sheets. Sample GSTR-2A files are uploaded for review.

 

[Marc L]

Hi !

A must read : Consolidate data from different excel files (VBA) …​

 

[GraH - Guido]

Hi,
I find it easier via PowerQuery, because it is doable with the GUI. No need for coding skills, you would need to write a good macro.
One thing it lacks is the ability to work incremental. So it will depend on the use case.
So if you have it on board (aka Get & Transform) you might want to check it out.
Some tutorials for this specific transformation
(Mike Girvin, ExcelIsFun) h t t p s ://www.youtube.com/watch?v=LSDQGWdgNJs
or
(Trump Excel) h t t p s://www.youtube.com/watch?v=qbMW0iSgH4I
Similar request over here https://chandoo.org/forum/threads/p...-data-before-consolidation.36914/#post-221648

Also with PQ you can add a step or a new query that replaces your VLOOKUP.

 

[suryasabniveesu]

Hi !

A must read : Consolidate data from different excel files (VBA) …​

Thanks for the reply. But I am not that much familiar VBA Code. I can copy & paste and use the macro.

Data range end cell is not known. It will vary in source files.

Can we have a option to pick list of files which are to be merged?

 

[Marc L]

Via a double click on a column B cell so on VBE side
paste this code event to the Sheet1 (List) worksheet module :​

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim V, L&
    If Target.Column = 2 And Target.Row > 1 Then
        Cancel = True
        V = Application.GetOpenFilename("Workbooks (*.xlsx), *.xlsx")
        L = InStrRev(V, Application.PathSeparator)
        If L Then Target.Resize(, 2).Value = Array(Mid(V, L + 1), Left(V, L))
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​