Forum Discussion
Bulk summary of granular data
Hi All,
I'm looking for some help/advice to consolidate/group granular data, my example is daily vehicle mileage over a months period, Column A would have vehicle registration numbers and then following columns would represent a day of the month and cells would populate with daily mileage total from vehicle tracking updated each day.
I'm trying to apply a formula or something that would enable me to group the % of vehicles that are in daily range brackets such as X% travel less than 100 miles per day X% travel less than 125 miles per day, X% travel less than 150 mile per day and so on, I'm currently manually doing this through conditional formatting but obviously some vehicles are in all groups so this takes time and I'm just wondering/hoping there is a better/easier way, any advise welcome, Thanks
Are you aware of the Pivot Table capability? From your description that would be where I'd go first.
If you are and haven't been able to make it work (or if you weren't but still need help) might I suggest you post a copy of the spreadsheet with all the data--the actual spreadsheet, not an image--on OneDrive or GoogleDrive with a link pasted here that grants access to it.
mathetes Thanks, i use pivot tables quite a lot to summarise data but cannot see how this would work for what i would like it to do to group % bandings as i want it to calculate the groups, my spreadsheet layout is very simple as described above but will have 1000 rows down for vehicle column (as that's the fleet size) and the 28-31 additional columns across depending on the days count in the month all with a variable mileage total in each cell.
- what i should add is that i need it to only include in the % groups where the vehicle has consistently throughout each day in the month been in the lowest groups i.e. a van doing 90 miles each day for 30 days but then doing 120 miles on the 31st day (or any order) wouldn't be in the <100 group it would move to the <125 & < 150 grouping, hope that makes sense
