80359

Sort Rows in Excel?

Question:

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:

<blockquote>

=IF(F6=1,$E6,"")

</blockquote>

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.

=IF(C2="Finished",IF(MONTH(B2)<=3,"Q1",IF(MONTH(B2)<=6, "Q2",IF(MONTH(B2)<=9,"Q3","Q4"))),C2)

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

Recommend

  • SSAS - Return dates where no data exists
  • SUM(column) / COUNT(column) not working in Excel PivotTable
  • Capture Excel Pivot Table Refresh event
  • C# Excel VSTO - Possible to move a pivottable?
  • Need to add paste special condition a copy.destination for loop
  • loop generate plots for variables in a data frame
  • How to make custom ant task?
  • Run an exe in client machine from a website
  • Reactive variables in Shiny for later calculations
  • Compiling for Mac/Linux on Windows
  • Laravel - Dynamic relationship using hasManyThough() and unique merge
  • App Script HtmlService use with font like Google Web Fonts?
  • How to test chat web app
  • Copying different structs to byte arrays
  • Playing an array as sound and recording its output in parallel
  • Text similarity analysis (Excel)
  • Is there a greater chance to collide when comparing GUIDs based on a hash vs \"Guid.NewGuid()?
  • Is it possible to collect a stream to two different collections using one line?
  • Plotting A Hyperboloid
  • Fixed Background Works in Chrome but Not Firefox?
  • Responsive Form on top of Responsive Image? - Bootstrap
  • to implement a spinner in angular2+
  • What's the name of this finding square root algorithm?
  • How to open html table in xls on click of a button
  • Jenkins Grails plugin does not list lastest versions of Grails
  • Android changing fragment order inside FragmentPagerAdapter
  • Jquery popup on mouse over of calendar control
  • How do I get HTML corresponding to current DOM tree?
  • How to attach a node.js readable stream to a Sendgrid email?
  • How to create a file in java without a extension
  • Android screen density dpi vs ppi
  • How would I use PHP exceptions to define a redirect?
  • How to extract text from Word files using C#?
  • Calling of Constructors in a Java
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Data Validation Drop Down Box Arrow Disappearing
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • need help with bizarre java.net.HttpURLConnection behavior
  • Append folder name and increment by 1 using batch script