Power Pivot - How to

Hello,

I have data on many sheets.  
All the column headings are the same.
I want to make a pivot of this data on many sheets.  

Is this something for power pivot or is there another solution?
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
You can make a pivot table using consolidation ranges but they are quite limited in terms of what you can do with them. Power Pivot won't help, but Power Query will if you have it as it can merge all the tables in to one data source.
You could also use code to either consolidate the data into one tab, or to create a recordset from all of them that you can then assign to your pivot table.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You may be relieved to know that you can use the standard PivotTables for this.
 
Have a look at this article: https://support.office.com/en-au/article/Consolidate-multiple-worksheets-into-one-PivotTable-report-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5
0
Rory ArchibaldCommented:
Just note that they are extremely limited. The only use I have ever had for consolidation range pivot tables is actually to normalise data.
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

pdvsaProject financeAuthor Commented:
Thank you for the info.  I went to the link.  What I am concerned about is once I set a named range on each of these sheets, I would want the range. To dynamically adjust if the data range has expanded (ie more rows).  

If the sheet was in the format of a table, and I assigned a named range to that table, would the names range adjust if the number of rows increased?

Thank you
0
pdvsaProject financeAuthor Commented:
Philip,

I did the PIvot as suggested but the results are not like I expected.  YOu can only choose Row, Column, Value.  The results are only a count of items and this is not what I am looking for.   I do not need a count for any of the various column heading.  I need the actual text (ie the person's name and not a count of that name and etc etc for the entire Pivot TAble.  I think this solution is only if you are interested in summing and I am not interested in this.  

If you have another idea let me know.  

Rory, not sure if you are folliwing me but does your solution shows the actual text and not counting.  

thank you
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What about combining all of the data onto one sheet before you start?
0
pdvsaProject financeAuthor Commented:
that is what I am trying to avoid to do this manually.
0
pdvsaProject financeAuthor Commented:
to combine the data manually on one sheet.  There are many sheets and it is quite tedious and time consuming and opens door to make a lot of mistakes.
0
Saurabh Singh TeotiaCommented:
One quick question...is this data which you have in multiple sheet tabs is it unique or duplicate because what i will say if its a unique data and you can have 1 to 1 relationship then convert that range to table and create a power pivot over it since in the power pivot you can do this feature easily by doing 1 to 1 relationship..

But please note if the data is not 1 to 1 then in the power pivot you won't be able to create a relationship as a result you won't be able to show them in powerpivot as well..

Alternatively if you are open a vba code can be written easily for you which can combine all of this data automatically on 1 sheet and on top of which you can make your pivot table..what i mean is something like this..this will combine all your data into sheets called combine... from each worksheet...this i have wrote for column A to D you can accordingly adjust the range basis to your needs...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete
    On Error GoTo 0

    For Each ws In ActiveWorkbook.Worksheets

        Set ws1 = Sheets("Combine")

        If ws1 Is Nothing Then
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = "Combine"
            Set ws1 = ActiveSheet
            ws.Range("A1:D1").Copy ws1.Range("A1")    '<---Copying headers
        End If
        lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
        lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
        ws.Range("A2:D" & lrow).Copy ws1.Range("A" & lr)

    Next ws
End Sub

Open in new window


Saurabh...
0
pdvsaProject financeAuthor Commented:
Hi Saurabh, thank you for your response.  Quick question:  I have many tabs but some f the tabs I would not need to combine the data which means I basically would it combine ALL sheets.  Is it possible to only combine the sheets with a common denominator such as the word "mod" in the name of the sheet?

Thank you much
0
Saurabh Singh TeotiaCommented:
In that case you can use something like this...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete
    On Error GoTo 0

    For Each ws In ActiveWorkbook.Worksheets

        If InStr(1, ws.Name, "mod", vbTextCompare) > 0 Then

            Set ws1 = Sheets("Combine")

            If ws1 Is Nothing Then
                Sheets.Add after:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Combine"
                Set ws1 = ActiveSheet
                ws.Range("A1:D1").Copy ws1.Range("A1")    '<---Copying headers
            End If
            lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
            lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
            ws.Range("A2:D" & lrow).Copy ws1.Range("A" & lr)
        End If

    Next ws
End Sub

Open in new window


This will combine all the worksheets in combine sheet which has mod in it..

Saurabh...
0
Rory ArchibaldCommented:
Do you not have Power Query?
0
pdvsaProject financeAuthor Commented:
Saurabh, thank you very much.  I will test soon.
Rory:  I do not have Power Query.  I do have Power Pivot.  I have very limited experience with PP and have no experience with PQ.  I could install then learn how to use it.  From what I gather PQ could be a solution but I do not know how much I would need to do in order to feel comfortable using it.
0
Rory ArchibaldCommented:
If you can use PQ, and format the source data as Tables, you can then have a 'live' connection to the data and all you would need to do is refresh the pivot table when the source data changes.
0
pdvsaProject financeAuthor Commented:
I will have to explore the VBA code method to combine sheets from Saurabh as I cant install on this computer as it locked down.  unfortuantely.  

Saurabh:  I get an error on line
Set ws1 = Sheets("Combine")

I added a sheet "Combine" and the code does delete the sheet but possibly the sheet "Combine" needs to be recreated and this is why I get the error as it cant find the sheet?  

let me know.  Waiting for your response.
0
Saurabh Singh TeotiaCommented:
In my code you don't have to do anything it will create the sheets combine on its own..all you need to change the range of the code and that will copy from all the worksheets which have mod in it in the sheets combine...
0
pdvsaProject financeAuthor Commented:
Oh I see.   I deleted the sheet "combine" but I still get that error on that line.  

I have uploaded the test file for your review.  
The code is in the excel file.  

when you have a sec, please let me know where I have made a mistake.
Combine-Sheets-with-VBA.xlsm
0
Saurabh Singh TeotiaCommented:
Their you go i created some more sample data in your worksheet with 10 headers which will do what you are looking for...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete

    For Each ws In ActiveWorkbook.Worksheets

        If InStr(1, ws.Name, "mod", vbTextCompare) > 0 Then
            On Error Resume Next
            Set ws1 = Sheets("Combine")

            If ws1 Is Nothing Then
                Sheets.Add AFTER:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Combine"
                Set ws1 = ActiveSheet
                ws.Range("A1:J1").Copy ws1.Range("A1")    '<---Copying headers
            End If
            lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
            lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
            ws.Range("A2:J" & lrow).Copy ws1.Range("A" & lr)
        End If

    Next ws
End Sub

Open in new window


Your workbook...

Saurabh...
Combine-Sheets-with-VBA.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Wow!  that works perfectly.  Thank you so much for your help.
0
pdvsaProject financeAuthor Commented:
Saurabh,

So sorry to pester you.   I hope you are still following this thread.

I have encountered something unknown.
I transferred to code to my workbook to do the real testing and I get a runtime error 9 "subscript out of range".

I have attached the spreadsheet.
Possibly you see an issue?
I have many more sheets than the attached spreadsheet but I have deleted all sheets but one and the remaining sheet does have the word "mod" in the sheet name as required.  I still get the error with one sheet.    
The range was modified out to column P and possibly that is the reason as I might not have edited the code properly.  

Please let me know when you have a second.  

thank you
TEST-Combine--sheets-deleted-.xlsm
0
Saurabh Singh TeotiaCommented:
You are still using an old version of the code..Use this version and this will do what you are looking for...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete

    For Each ws In ActiveWorkbook.Worksheets

        If InStr(1, ws.Name, "mod", vbTextCompare) > 0 Then
            On Error Resume Next
            Set ws1 = Sheets("Combine")

            If ws1 Is Nothing Then
                Sheets.Add AFTER:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Combine"
                Set ws1 = ActiveSheet
                ws.Range("A1:P1").Copy ws1.Range("A1")    '<---Copying headers
            End If
            lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
            lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
            ws.Range("A2:P" & lrow).Copy ws1.Range("A" & lr)


        End If

    Next ws
    ws1.Cells.EntireColumn.AutoFit

End Sub

Open in new window


Saurabh...
0
pdvsaProject financeAuthor Commented:
Thank you once again.  It works now.  Appreciate your help dearly...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.