+ Reply to Thread
Results 1 to 6 of 6

Identifying an out of place value that is out of a pre-determined order

  1. #1
    Registered User
    Join Date
    10-28-2019
    Location
    Bucharest, Romania
    MS-Off Ver
    2016
    Posts
    14

    Identifying an out of place value that is out of a pre-determined order

    Hello everybody, and happy to be here

    I am trying to identify a specific correct order for a list of companies (if they are in a correct order then no action should be taken, and if they are not in a correct order then they should be identified and marked as such). I have attached a file. The companies which have the correct order in my example are marked in green.

    The 'order' is numbered from 1-6. In my example, should a company have an 'order' number assigned to it higher than any of the 'order' numbers assigned to the same company previously (in the rows above) then everything is OK.

    Should a company have an 'order' number assigned that is lower than any of the 'order' numbers assigned to the same company previously (in my example company XYZ has 2 assigned on rows 11 and 12 where previously it had assigned a 3 on row 10) then the lines should be identified - I only need the lines which are not in order to be marked, not modified. I do not need a new order. All the lines marked in red on column C from my example should be marked preferably.

    Thank you in advance to whomever may answer this, it's been boggling my head for a while.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Identifying an out of place value that is out of a pre-determined order

    What am I missing?
    The following things in your sheet don't follow your rules:

    In your example, Row 6 DEF has a 1 assigned to it which is less than 2 assigned previously (should be red) but Row 5 should be green
    Row 8, is the first time XYZ appears but you have it red
    Rows 9 and 10 exceed other XYZ orders so should be green
    Row 19 is first appearance of ZGH so should be green
    Row 21 is highest order # for ZGH, should be green
    Rows 24-25 match highest numbers so (based on previous examples) should be green
    Last edited by ChemistB; 10-28-2019 at 11:32 AM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Identifying an out of place value that is out of a pre-determined order

    c2 and copy down =IF(AND(A2=A1,B2<B1),1,0)
    conditional format, use a formula to determine which cells to format =countifs($a$2:$a$25,a2,$c$2:$c$25,1) as red

    ChemistB I think what you are missing, is if any of a companys orders fails, all of the lines for the company fail
    Last edited by davsth; 10-28-2019 at 10:51 AM.

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Identifying an out of place value that is out of a pre-determined order

    ChemistB is right. I would assume the same thing. But if you care only for order logic and you are happy with a helper column then in C2, array formula(Ctrl+Shift+enter instead of regular Enter): =IF(B2>=MAX(IF($A$1:A2=A2,$B$1:B2)),"good","bad")
    And as CF rule in green: =COUNTIF($A$2:$A$25,A2)=COUNTIFS($A$2:$A$25,A2,$C$2:$C$25,"good")
    in red: =COUNTIF($A$2:$A$25,A2<>COUNTIFS($A$2:$A$25,A2,$C$2:$C$25,"good")
    Click the * to say thanks.

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Identifying an out of place value that is out of a pre-determined order

    I would need to use two helper column to this

    Enter into Cell C2 and then fill down
    =IF(AND(B2<B1,A2=A1),1,0)

    Enter into Cell D2 using Ctrl Shift Enter and then fill down.
    =IF(MAX(IF(A2 & 1=A:A&C:C,1,0))=1,1,0)

    Use Column D for your conditional format
    Formula for conditional formatting:
    =D1
    Attached Files Attached Files
    Last edited by mehmetcik; 10-28-2019 at 10:49 AM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  6. #6
    Registered User
    Join Date
    10-28-2019
    Location
    Bucharest, Romania
    MS-Off Ver
    2016
    Posts
    14

    Re: Identifying an out of place value that is out of a pre-determined order

    Thank you all for your help, I managed to solve the issue by slightly tweaking the formula provided by mehmetcik, as there was one other variable at play that I did not mention.

    ChemistB, as davsth mentioned, each time a string of values failed I needed them marked. So even if for row 19 ZGH makes a first appearance, it should have been marked in case one of the values below it failed to meet the set order. I will try to word it better next time, it was not very intuitive on my part.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to sum by group and place in specif order?
    By AccountingJ in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-01-2019, 09:57 AM
  2. Months are not in order- How to place in order?
    By thursday140 in forum Excel General
    Replies: 9
    Last Post: 02-08-2018, 02:43 AM
  3. Replies: 6
    Last Post: 01-14-2014, 11:48 PM
  4. [SOLVED] Construct a formula on a pre-determined cell for a pre-determined duration of cells
    By Shaun Gemiver in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 11:14 AM
  5. Need help in identifying not-in-ascending order set of values
    By santhosh.481 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2012, 02:46 PM
  6. Is there a way to place a row in numeric/alpheberical order?
    By Krystle in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-31-2006, 08:15 PM
  7. Copy cells in a determined order
    By pmarques in forum Excel General
    Replies: 3
    Last Post: 02-06-2006, 12:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1