Jump to content


Photo

Selecting Cells After Highlighted cells

Conditional Formatting & More

  • Please log in to reply
1 reply to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 22 June 2017 - 02:01 PM

I will attach a WB with a grid of numbers where I used Conditional Formatting to highlight all cells that are >= 25. What I would like to do is find all the values in cells after the highlighted cells for each team and see what the averages for those cells are for each team.

As an example I will do ARI.

     This team had four such dates and I will display what those counts were and the counts for the ensuing day:

         April 25th    29      24

         April 27th    30      38

         April 28th    38      33

         April 29th    33      25

 

                                     120 / 4   ='s 30 

 

How can I filter those cells out?

 

Respectfully...........JPK 1066

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 23 June 2017 - 11:57 AM

Hi JPK,

 

Taking a look at your workbook, it already appears to be in a 'pivot table' format with dates along the top and teams along the left column. While this is useful for reading purposes, it doesn't lend itself to filtering.

 

The first thing to do is to create a 'Date' column and rearrange the data into a more typical table format:

 

unpivot1.png

 

I did this quickly by using Get & Transform's 'unpivot' feature, covered in our Excel 2016 Expert Skills book (available from our books page), but it's possible that the source data is already 'unpivoted', or you could do this manually.

 

With the data 'unpivoted', the problem is almost exactly the same as one you've faced previously, in this thread.

 

You will need to add a column with an IF function that checks whether the value is greater than or equal to 25:

=IF([@Value]>=25,TRUE,FALSE)

 

The IF function is explained in depth in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

Next you will need a more complex formula that checks whether the current row is greater than 25 or is immediately after a row that was greater than 25:

=IFERROR(IF([@[>=25]],1,IF(D1,2,0)),0)

 

I have used the values 0, 1 and 2 to signify:

0: Not greater than 25, not after a record that was greater than 25.

1: Greater than 25.

2: After a record that was greater than 25.

 

You could replace these with TRUE and FALSE if you don't need to filter them separately.

 

You'll notice that I also used an IFERROR function to suppress error messages on the first row of data. You can see more about IFERROR in the video tutorial: Expert Skills Lesson 3-23 Use an IFERROR function to suppress error messages.

 

Now that you have the data you need you can easily filter it using a simple filter, as shown in: Expert Skills Lesson 1-3 Apply a simple filter to a range.

 

I have attached my example copy with the filters applied:

 

Attached File  UnpivotFilter.xlsx   84.36KB   26 downloads

unpivotfilter.png

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users