Jun 23 2021 08:38 PM
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.
Jun 24 2021 06:07 AM
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
Jun 24 2021 01:17 PM
SolutionThat's more job for Power Query, please check in Sheet2 attached.
Jun 24 2021 06:30 PM
@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!
Jun 24 2021 07:47 PM
Jun 24 2021 01:17 PM
SolutionThat's more job for Power Query, please check in Sheet2 attached.