Forum Discussion
Using Pivot Table to Consolidate multiple rows of data
Hello,
I am attempting to consolidate open and close times from a table into a Pivot Table. Times for Monday, Wednesday, and Friday are entered in one row while Tuesday, Thursday, and Saturday are entered in another. All the rows have an account number.
When entered into the PivotTable, there is no consolidation based on the account number - the data is pivoted exactly how it appears in the table - Blanks for the days where there is no open time and multiple lines if the account has hours that differ by day.
I've attached the workbook - Worksheet "TW Template" contains the data (Account number in column A; Open/ Close times in columns AC through AP). "PIVOT-HOO" is my attempt at consolidating the data with a PivotTable. Ideally, each account would only have one row of open/ close times.
Appreciate any assistance or alternative solutions that can be offered.
That's more job for Power Query, please check in Sheet2 attached.
4 Replies
That's more job for Power Query, please check in Sheet2 attached.
- SergeiBaklan - That will open up a new world for me. I've not opened Power Query until now. Thank you for your assistance and for opening up this gateway!
Just looking at your workbook, my guess is that the problem here has to do with the way your database is arrayed. You've done already some of the work of a Pivot Table--i.e., in columns Y through AL (under the red heading) you've already done some cross-tabulating--and that interferes with things. As Obi-wan says, "Trust the Force, Luke"
Have you done Pivot tables in the past? Here's one of any number of good resources of "Tips" to create well-functioning Pivot Tables. You'll see that having clean and well organized source data is right at the top of the list. https://exceljet.net/pivot-table-tips
mathetes - I've used PivotTables quite a bit, but never for anything like this. I wasn't sure if the PivotTable would be able to break the times out for the respective days, hence all those extra columns.
Thanks for the link - Always love using ExcelJet to brush up on things!
