I have an issue with an excel spreadsheet I want to see if I can do without VBA just because it seems easier to implement that way. Basically, there are many columns in the sheet I want to sort. However, I merely want to look at three columns: the title column, the data column and the status column.
In a new spreadsheet, there will be four sections. Each section corresponds to 3 months of the year (ie Jan, Feb, Mar. will map to the first column on the new spreadsheet, April, May, June will map to the second column on the new spreadsheet).
Based on the date, and if the status column has the word "Finished" (in the original spreadsheet), I want to map the title to a certain column under the new spreadsheet based on the date criteria as described in the previous paragraph. So for example, if the original spreadsheet has following:
Title Date Status Doc1 1/12/13 Finished Doc2 2/10/13 UnFinished Doc3 4/1/13 Finished Doc4 3/31/13 Finished
Would map to, on the new spreadsheet:
1st Column | 2nd Column Doc1 Doc3 Doc4
I have looked a lot into pivot tables but I can't "automate it" as much as I want to. I have gotten it down to the point where I can change the pivot tables into filtering based on date, but I want it even more automated than that. I've also tried excel formulas but that has been to no avail. Thanks for the help, I really appreciate it!Answer1:
With a PivotTable it seems fairly easy to 'automate' as far as Sheet
2 as below:
<img alt="SO17864374 example" class="b-lazy" data-src="https://i.stack.imgur.com/kUrqg.gif" data-original="https://i.stack.imgur.com/kUrqg.gif" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />
but from there to the result requested is relatively 'manual' without VBA, so may not suit.
For my convenience I have changed the date formats. The PivotTable is constructed as usual/indicated without showing grand totals for rows or for columns (PivotTable Options, Totals & Filters). The Column Labels are
Date with Grouping By Quarters with appropriate Starting at: and Ending at: (Group) and Collapse Entire Field (Expand/Collapse).
The formula in
I6 is to convert the document count (always 1) to document name:
However, to allow room for additional quarters in the PivotTable the formula should be moved to the right. The formula would need to be copied across and down as necessary.
The process becomes more ‘manual’ with copying the results of these formulae, pasting them (with Special / Values) into a new location (in the example 2!A1) and, if required, deleting blanks.Answer2:
This may be against the rules with regards to maintaining the integrity of the OP's request, but hopefully it doesn't offend :)
Here's another option.<ol><li>
Add another column (shame on me, I know) to the original data, and called this Quarter. The formula that goes next to the existing data is the following.
Basically, if the status is "Finished", then determine in what quarter the date is.</li> <li>
Create the pivot table with that data, and then add "Quarter" and "Title" to the Row Labels (in that order)</li> <li>
Last thing would be to click the arrow next to "Row Labels" and select "Does not Equal" under "Label Filters". There you'll type "Unfinished" (no quotation marks). This will give you something like the image below.</li> </ol>
<img alt="Another option using a formula and PivotTables" class="b-lazy" data-src="https://i.stack.imgur.com/WcQBx.png" data-original="https://i.stack.imgur.com/WcQBx.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />
From here the only manual thing you'll need to do is update the data range for the pivot table if more rows are added to the pivot table data and refresh the pivot table if the original data changes
NOTE: To address your question about sorting; after you do the steps above, you can select the Row Labels again and do an A>Z sort to get each quarter to be sorted in alphabetical order