Counting if a word appears an odd number of times

[hboss_1]

Hi All,

I have a roster system created (thanks to the expertise of Bernie from this forum), for each week if a shift is swapped we put the letters SWP in a cell (with the initials of the person they are swapping with), but obviously if there is one SWP in a cell there should be another SWP in another cell in the same column (with the person they are swapping with). So there should always be either 0 or an even number of SWPs written in each column.

Is there any way to conditionally format if there is an ODD number of 'SWPs' in a column, the ideal scenario would be that if there is one SWP, the one cell is highlighted because another SWP must be put in.

I hope I have been clear enough.

In summary I want to count if a word appears an odd number of times.

Thank you,
H

[Pete_UK]

You can use COUNTIF to count the number of "SWP"'s in the column, and you can use MOD(... , 2)=1 around that to determine if the number is odd. You haven't said which column the SWP will be recorded in, and I don't want to guess. You can attach a sample Excel workbook by following the instructions in the yellow banner at the top of the screen.

Hope this helps.

Pete

[bebo021999]

Assum D1:D16 contain number of "SWP"

Choose D1:D16, then using below formula in Conditional formatting:

Please Login or Register  to view this content.

[hboss_1]

Hi Pete,

If the data is from B6:B37, how would the formula look?

Thank you,

H

[hboss_1]

Hi Both,

See below here is an example column. As you can see there are two cells with SWPs and initials indicating they are swapping with each other. The SWP in a cell further down is on it's on, when there should be another SWP indicating the swap.

Bebo I tried the formula, it seems to always highlight the final cell occupied with MX, as well as only highlighting when it ONLY contains the word MX. I want to count if it contains MX.

Sorry if I was unclear,

Thank you,

H
Attachment 733140

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Pete_UK]

In your example file you show all cells containing SWP (five in the upper table, from row 6 to 37) in red, although you have asked to highlight only those containing SWP (with no extra data), so I'm a bit confused. Isn't it just a matter of highlighting cells containing only SWP for each day, whether it is an even number or odd (as you will need to add that extra data about who to swap with and when)?

Pete

[hboss_1]

You are entirely correct, I was overthinking things again !!!
Thank you for your help,

H

[Pete_UK]

Glad to help - thanks for the rep.

Pete