+ Reply to Thread
Results 1 to 4 of 4

Checking if a column has odd or even count of a text in cell

  1. #1
    Registered User
    Join Date
    05-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    7

    Checking if a column has odd or even count of a text in cell

    I want to be able to look at a column (column A on attached spreadsheet - page 'Thur 29 July')
    on column A there are lots of numbers going down the column - these are scans of tools going in and out of the tool store.
    the sequence is as follows;
    - someone takes a tool out of the store and the tool is scanned and the person is scanned (row 3 as example on 'Thur 29 July' page
    - if they take more than one tool out the tools on lines 4, 5 and 6 are scanned but we don't scan the person again
    - When the tools are returned to the store they are scanned again and shown in column A but we don't scan the person

    PROBLEM
    When a tool is taken out and returned we put conditional formatting on so the cells go pink. Any tool not returned is then left non shaded. That means we can quickly see what tools are outstanding.
    When a tool is taken out more than once in the same day we cannot quickly tell what tools are not returned

    WHAT WE WANT TO DO
    is have some sort of formula that can check column A and highlight any ODD scenarios for a specific number
    ie. if tool number 2 has been taken out and returned twice there should be 4 x no 2 in column A. As that is an even scenario it would be fine
    if the tool number 2 was taken out twice and returned once it would show 3 x no 2 in column A. As that is an odd scenario we want this highlighted (we aren't bothered about having conditional formatting)

    SOME POINTS
    - we do a new page for every day
    - the tools and the people are pulling information through from the master sheets showing every tool in the store and every person that may take a tool out
    - ideally need to work out something that is relatively (?) easy to manage so that we can amend if we need to

    not sure what will do this ?
    all help appreciated
    Belinda
    Attached Files Attached Files

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

    Re: Checking if a column has odd or even count of a text in cell

    I would use a formula to countif the number of times the value on the row appears in the column then put this in an isodd function and if this is true conditionally format accordingly

    something like conditional format , new rule use formula to determine which cells to format

    ISODD( COUNTIF($A$3:$A$100,A3 ) ) adjust the hundred accordingly and choose the colour you wish to format

  3. #3
    Registered User
    Join Date
    05-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    7

    Re: Checking if a column has odd or even count of a text in cell

    got excited but my use of excel formula is very limited
    is it possible you can edit the sheet sent with the formula you mean ? so that I can then just save and use on my full format ?
    (I'm not very savvy...)
    thanks

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

    Re: Checking if a column has odd or even count of a text in cell

    Try this so you can learn and if you really cant manage I will attach something
    select range a3:a100
    goto the home tab, click on conditional formating, choose new rule, use a formula to determine which cells to format

    in the format values where this is true put =ISODD( COUNTIF($A$3:$A$100,A3 ) )

    click on the format button, the perhaps choose fill from the tabs at the top and pick a colour

+ 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. Concatenation after checking column contains any text or not
    By hamzabinnaeem in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-21-2015, 06:47 AM
  2. Help with IF formula checking text in a cell
    By ejf1987 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2014, 05:11 PM
  3. VBA code to return a count in a cell after cross checking three different criteria
    By jmartineau in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2013, 03:40 PM
  4. Replies: 1
    Last Post: 10-27-2012, 12:04 PM
  5. Count of column C by checking column A
    By nicdyb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2012, 09:22 AM
  6. Define color of row checking two dates within same cell and text in other cell
    By amragl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2011, 07:33 AM
  7. [SOLVED] Checking a cell's (Row,Column)
    By Craig in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-25-2005, 09:50 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