Consolidate data from several worksheets with same columns but different number of rows in Excel

Question

I have an Excel workbook with several worksheets. They all have the same column names. However, the number of rows differs from sheet to sheet. I want to create an overview sheet where I can see all the data from every single sheet. I could go in every sheet an just copy&paste the data from the second row to the last row with data in every sheet. But I think there is a easier way to do this?

Thanks for your help!

Answer 1

This is a solution using VBA:

Public Sub overview()
    resultsheet = "Overview"
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    On Error GoTo SheetError:
        Set wks1 = wkb.Sheets(resultsheet)
    destrow = 1
    totalcolumn = 1
    totalwks = wkb.Sheets.Count
    For i = 1 To totalwks
        Set wks = wkb.Sheets(i) 'Iterate over all sheets
        wksname = wks.Name
        If wksname <> resultsheet Then 'Exclude the overview sheet
            rowdata = True
            columndata = True
            thisrow = 2
            thiscolumn = 1
            totalempty = 0
            While rowdata = True
                If i = 1 Then 'First sheet section
                    a = wks.Cells(thisrow - 1, thiscolumn)
                    If a <> "" Then
                        wks1.Cells(destrow, thiscolumn) = a
                        thiscolumn = thiscolumn + 1
                    Else
                        If thisrow = 2 Then
                            totalcolumn = thiscolumn
                        End If
                        totalempty = totalempty + 1
                        If totalempty = totalcolumn Then
                            rowdata = False
                        End If
                        If thiscolumn = totalcolumn Then
                            thisrow = thisrow + 1
                            thiscolumn = 1
                            destrow = destrow + 1
                            totalempty = 0
                        End If
                    End If
                Else ' Any other Sheet section
                    a = wks.Cells(thisrow, thiscolumn)
                    If a <> "" Then
                        rowdata = True
                        wks1.Cells(destrow, thiscolumn) = a
                        thiscolumn = thiscolumn + 1
                    Else
                        totalempty = totalempty + 1
                        If totalempty = totalcolumn Then
                            rowdata = False
                        End If
                        If thiscolumn = totalcolumn Then
                            thisrow = thisrow + 1
                            thiscolumn = 1
                            destrow = destrow + 1
                            totalempty = 0
                        End If


                    End If
                End If
            Wend

        End If
    Next i
    Exit Sub
SheetError:
    If Err.Number = 9 Then
        createwks = MsgBox("Worksheet " & resultsheet & " doesn't exist" & vbCrLf & "Do you want to create it?", vbYesNo, Error)
    End If
    If createwks = 6 Then
        Set wks1 = wkb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
        wks1.Name = resultsheet
        Resume
    End If
End Sub

You have to open Macros/VBA on ThisWorkbook insert a module and paste this code on the right side, then execute this and check the contents on the sheet called Overview when it ends.

Answer 2

I would do it manually :)

You can take a look at this solution (Consolidate data by position), you'll see it's the similar overhead:

In each worksheet that contains the data that you want to consolidate, set up the data by doing the following:

Make sure that each range of data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.

Put each range on a separate worksheet, but don't put any ranges on the worksheet where you plan to put the consolidation.

Make sure that each range has the same layout.

In the master worksheet, click the upper-left cell of the area where you want the consolidated data to appear.

Note To avoid overwriting existing data in the destination worksheet with the data you are consolidating, make sure that you leave enough cells to the right and below this cell for the consolidated data.

... and so long ...

Answer 3

I see no advantage in doing this. I can't tell what you have to do, but if you divided the data on the several sheets, you must have a reason to do that. If you have a reason, why join then together ? It makes no sense to me. All right, what I would do is, why do I need to see then in the first place, all together ? It just makes it difficult to find anything, Wait, yes, only if you want to use the edit function to locate specific data on it. But, do you have to do it many times ? I would, if there are anything related, make, for ex, a SUM or other resume in each sheet, and in the new sheet make a cross reference for that field on each separated spreadsheet.to consolidate the data, without copying the whole data to just one sheet. If you are a good programmer the first solution for copying it may help, but it is not that simple, you'll see. And last, but not least, if each sheet grows in row numbers, make sure, the SUM or whatever else be on the topmost and rightmost, so the sheet grows but the consolidate data don't get affected.