Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 28

Thread: VBA Code Require - Consolidate multiple sheets in 1 sheet when column Headin differs

  1. #1

    VBA Code Require - Consolidate multiple sheets in 1 sheet when column Headin differs

    Hi Team,

    VBA Code required to consolidate multiple sheets into one consolidated sheet when the column heading is different.

    Attached sample raw data where I need VBA code consolidate data from all the sheets into one sheet matching with the EIN and data against the column heading from all the sheets.

    Kindly let me know for any clarifications.

    Regards,
    Yadavagiri
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,572
    In the linked-to file below, 2 new sheets added:
    Consolidated: what it says on the tin: where possible columns merged, where not, just added as new columns.
    Pivot: The beginnings of a pivot table of the consolidated sheet.

    Power Query has consolidated the sheets. For you to use different data, update the information in the 6 tables, refresh the query called Consolidate (right-click the Consolidated table and choose Refresh) and refresh the pivot table.

    That's it.

    link to big file:
    https://app.box.com/s/60mpxqpwlin82q9bdx8b6ecypb5mwe72

    ps. the file size could be reduced by loading the consolidated date to the Data Model rather than the Consolidated sheet which would reduce the file size to less that two thirds.
    Last edited by p45cal; 08-23-2019 at 10:57 AM.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,572
    I've beeen playing about with Power Query (I don't know what I'm doing) and managed to reduce the file size even more to where it's only 6% bigger than your file, and so can attach it here. You can't see the consolidated data because it doesn't exist on a sheet, but the pivot table connects to it. Now it appears you only need to refresh the pivot table, and it seems to be quicker too.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    Option Explicit
    
    
     Sub test()
        Dim dic As Object
        Dim ws As Worksheet
        Dim v, w()
        Dim j As Long, k As Long
        Dim s As String
        Dim m As Long, n As Long
        
        Set dic = CreateObject("scripting.dictionary")
    
        ReDim w(1 To Rows.Count, 1 To 100)
        
        For Each ws In Worksheets
            v = ws.Cells(1).CurrentRegion.Value
            For j = 1 To UBound(v, 1)
                s = v(j, 1) & v(j, 2) & v(j, 3) & v(j, 4)
                If Not dic.exists(s) Then
                    n = dic.Count + 1
                    dic(s) = n
                    w(n, 1) = v(j, 1)
                    w(n, 2) = v(j, 2)
                    w(n, 3) = v(j, 3)
                    w(n, 4) = v(j, 4)
                End If
                n = dic(s)
                For k = 5 To UBound(v, 2)
                    w(n, k + m) = v(j, k)
                Next
            Next
            m = m + UBound(v, 2) - 4
        Next
            
        With Workbooks.Add(xlWBATWorksheet).Sheets(1).Cells(1)
            .Resize(dic.Count, m - 1).Value = w
            .CurrentRegion.Sort .Columns(3), Header:=xlYes
        End With
    
     End Sub

    マナ

  5. #5

    Error

    Hi Mana,

    Thanks for VBA Code,when I run the code, I am getting error. Attached error screenshot. please do the needful and let me now what needs to be done.

    Regards,
    Yadavagiri
    Attached Files Attached Files

  6. #6
    Hi,

    I don't have power query enabled in my system as it doesn't support Any possible for getting VBA Code?

    Regards,
    YadavagirI

  7. #7
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    try this


        'ReDim w(1 To Rows.Count, 1 To 100)
    
    
        Dim x As Long, y As Long 
    
    
        For Each ws In Worksheets
            x = x + ws.Cells(1).End(xlToRight).Column
            y = y + ws.Cells(1).End(xlDown).Row
        Next
        ReDim w(1 To y, 1 To x)

  8. #8
    Hi Mana,

    I am very sorry to come back as im little bad at VBA. Where do I input this code?

    Regards,
    Yadavagiri

  9. #9
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    Instead of this line.

    > ReDim w(1 To Rows.Count, 1 To 100)

  10. #10

    Error 2

    Hi Mana,

    I tried replacing, however, got error. Enclosed Screenshot.

    I have attached my sample excel file. Please could you help on the same.

    Regards,
    Yadavagiri
    Attached Files Attached Files

  11. #11
    Hi Mana,

    I thank you for assisting which is very much appreciated.

    your code with the above changes was success and it worked, however, if I add any additional column data in any of the sheets and also if any new sheets are added in the workbook, it is not working.

    please help!

    Regards,
    Yadavagiri

  12. #12
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    >it is not working

    What do you mean?
    Error occurs?

  13. #13

    Help

    Hi Mana,

    Thanks for getting back.

    I meant to say that, if I add any additional column, the data is not capturing. Also, if I add any additional sheet, it is not updating.

    Attached file, Consolidated Sheet is the sheet which I have worked from your code.

    Sheet highlighted in Orange in the attached, where the data has captured only one column (Work) remaining data in column F & G is not captured in the Consolidated Sheet.

    Also no data is captured from sheet highlighted in Red (Hold).

    Kindly help.

    Regards,
    Yadavagiri
    Attached Files Attached Files

  14. #14
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    It's not automatic,
    you have to run again.

  15. #15
    Hi Mana,

    Yes I have ran the code again but its the same as above.

    Regards,
    Yadavagiri

  16. #16
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    .Resize(dic.Count, m + 4).Value = w

  17. #17

    Perfect !!

    Hi Mana,

    Sorry to revert back so late.
    I have amended the changes which is perfectly working fine for my current set of data.

    however, I have another set of data where there are multiple column headers and when I update this code and run, it throws out error.

    Please help me with a VBA Code where the code will run even though there are multiple column headers.

    Regards,
    Yadavagiri

  18. #18
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    Quote Originally Posted by Yadavagiri View Post

    however, I have another set of data where there are multiple column headers and when I update this code and run, it throws out error.

    I need sample data.
    Please upload your workbook.

  19. #19

    Sample Data

    Hi Mana,

    File size is around 4mb and I am unable to upload. Do you have email id so that i can forward you?

    Regards,
    Yadavagiri

  20. #20
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,523
    @mana

    Use advancedfilter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •