Need help in identifying not-in-ascending order set of values
I need to identify the rows that have a set of values that are not in ascending order.
In the excel sheet attached below, i need to identify the months that have corresponding values in not-ascending order.
I manually changed the color of the few months for reader understanding purpose.
This is just a sample sheet, my original sheet has more than 10 thousand rows and I am not attaching that sheet here as I can't disclose the data.
Last edited by santhosh.481; 10-19-2012 at 08:43 AM.
Why attach a jpg when you can directly attach a sample excel file?
To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Condtional Formatting should do. Use the formula defintion in the columns with the values (except the first such).
Start at the first row and 2nd column with supposedly ascending value. Extend the selection to the last such row and column.
Enter the CF with =F2=<E2 (modify to suit; I've assumed 2nd col is F, and the rows start at 2) is the formula , set the format as required, e.g. color red.
Apply to the whole range at once, CF will change the row and column applicable to each cell.
Maybe something like:
=IF(SUMPRODUCT(--(E2:H2-D2:G2<=0)),"Out of Order","")
copied down.
This will give "Out of Order" if there are 2 consecutive duplicated numbers too... if you don't want that, then take the = sign away from the <=0 part.
P,S. Please update your title to make it more descriptive of the problem.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I added a sample excel and edited the title. Let me know if anything else needs to be done. Thanks!
Last edited by santhosh.481; 10-19-2012 at 08:33 AM.
please see the updated post. The length of the set is not constant in all the rows.Originally Posted by brynbaker
Try: In AA1
=IF(SUMPRODUCT(--(C1:INDEX(B1:Y1,MATCH(REPT("z",255),B1:Y1))-B1:INDEX(B1:Y1,MATCH(REPT("z",255),B1:Y1)-1)<=0)),"Out of Order","")
copied down.
You can extend the formula to accept blanks =(F2<>"")AND(F2=<E2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks