How to group by mixed values and sort by date in a PivotTable?
travelbug928
I am attempting to create a spreadsheet with a lot of data captured in it. The two requirements I have to meet are 1) group jobs/parts with the same PROJECT #, and 2 sort by JOB START DATE. I thought PivotTables were the best way to do so, but I keep running into a brick wall. I'm either unable to group by Project # (most likely because they are a mixture of numbers and text, this cannot be changed), or I'm unable to sort by Job Start Date.
I've tried moving Project # and Job Start Date from Rows to Values, as well as changing the order they're displayed in (Job Start Date before Project # and vice versa).
If grouped and sorted correctly, the records should show the grouped PROJECT # with the earliest start date first, then the next group with the next start date, etc.
An example would be:
>2074, 68506, BUC10626, 3/4/19
>>2074, 68568, AUC15393, 3/4/19
>>2074, 68570, AUC14509, 5/30/19
>2552, 69920, 99163786, 4/1/19
>>2552, 71066, H695359, 6/5/19
>1166, 71527, 5450926, 5/16/19
>2497, 71138, 2436-923, 6/11/19
>>2497, 73445, H646427, 7/24/19
>2704, 72682, AUC11771, 6/24/19
teylyn
Pivot tables build a hierarchy. If you have a cascade of Project > Job > Part > Date, then you can only sort by date within the container of the previous level, i.e. Part.
If you have more than one part in the hierarchy, then different dates will show up sorted inside that part, e.g.
ProjectA
JobA
Part A
January 'these rows are
February 'sorted by
March 'date
Part B
August
September
Part C
March
April
If you want the projects sorted by date, then you need to have the date column before the project column.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
How to sort a column with Date and time values in Spark?
- 2
How to group by one column and sort the values of another column?
- 3
How can I group by the count number of column values and sort it?
- 4
Copying PivotTable Values in VBA
- 5
Sort by datetime, then group by date
- 6
How to do a sort of mixed values in R
- 7
R - How to sort age values into age group
- 8
How to sum values of two tables and group by date
- 9
PivotTable to forecast by date
- 10
How to sort values by date and drop duplicates by a column?
- 11
How to Group by Date Field in a PivotTable using win32com.client
- 12
Group object values by date
- 13
PivotTable -- how to sum the result of a countif formula by group?
- 14
How to solve mixed date formats
- 15
Excel PivotTable; how to show values horizontally
- 16
Sort List by date values
- 17
How to copy PivotTable values without copying it?
- 18
sort array of mixed integer and string values
- 19
Sort and concatenate values by group
- 20
how to sort "GROUP BY" query result by Frequent values?
- 21
How to group ans sort posts in wordpress according to date?
- 22
How to sort values in a column based on group_by in dplyr
- 23
Sort values based on group
- 24
R how Sum values by group by date
- 25
SQL Sort by date and optional group
- 26
How to sort output within a group by date?
- 27
Python: Formatting columns with mixed string and date values
- 28
how to sort a mixed string with numbers
- 29
Pandas Mixed Date Format Values in One Column
Comments