+ Reply to Thread
Results 1 to 4 of 4

find all with same number, if all = 0, highlight?

Hybrid View

  1. #1
    Registered User
    Join Date
    03-14-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    13

    find all with same number, if all = 0, highlight?

    Hi,

    Pretty much what it says in the title.

    I have invoices, some of which have 2-30 separate lines. I already have a variance column which goes to 0 when information is updated (if costs are correct)

    I would like some solution to highlight when all the variances within one invoice have turned to 0 (or ideally a range of -5 to 5) so I can quickly check off the ones that are good to pay.

    For example below showing column with 2 different invoice numbers in 123456 has 1 line with a variance of 10 so cannot be paid. whereas 1010 all say 0 so it's good to go - its hard to see in a BIG sheet like I have so I'd like it to be highlighted or even another column to the right with a figure in?

    Invoice no Variance
    123456 0.00
    123456 0.00
    123456 10.00
    123456 0.00
    123456 0.00
    1010 0.00
    1010 0.00
    1010 0.00
    Last edited by pinky24; 07-03-2014 at 06:49 AM.

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: find all with same number, if all = 0, highlight?

    Assuming your data is in A23:B31, you could put something like this in C23:
    =COUNTIFS($A$23:$A$31,A23,$B$23:$B$31,">5")+COUNTIFS($A$23:$A$31,A23,$B$23:$B$31,"<-5")
    then drag it down the rest of the rows.
    If the result is not 0, then that invoice has a variance outside of your -5 to 5 range. This example used a separate column, but you could use a similar formula for conditional highlighting
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: find all with same number, if all = 0, highlight?

    If Pauley's solution doesn't work out, then can u post a workbook with sample data? (remove sensitive information)
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  4. #4
    Registered User
    Join Date
    03-14-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: find all with same number, if all = 0, highlight?

    sorry for not replying - someone's actually found a solution for me (not sure what they did but it works)

+ 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] Find number and highlight in yellow
    By Jerry HKA in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-10-2014, 04:17 AM
  2. Find And Highlight
    By congratx in forum Excel General
    Replies: 10
    Last Post: 07-03-2011, 11:16 AM
  3. find n highlight max value
    By only_lonely in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 05-22-2007, 10:37 PM
  4. find and highlight
    By mariasa in forum Excel General
    Replies: 7
    Last Post: 07-01-2006, 02:57 AM
  5. find and highlight
    By mariasa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2006, 02:10 AM

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