Conditional formatting question

P

Pat

I want to apply conditional formatting on column B where the value entered
does not equal a value already entered for the same item in column A

Example:

Column A Column B

Car 1
Lorry 1
Lorry 2
Motorbike 1
Motorbike 1 (false)

The last item shows that 1 has already been used for Motorbike.
 
B

Bob Phillips

Use FormulaIs and a formula of =COUNTIF($A$1:A1,A1)=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

Jack Schitt

There is probably a more elegant solution, but:
In your example, suppose that that data starts at row 1 (ie A1 contains
"Car").

Then set the conditional formatting of cell B5 to
Formula is: =SUM((A$1:A5=A5)*(B$1:B5=B5))<2

Copy the formats only up to the other cells in column B
(copy cell B5 to the clipboard, then select range B1:B4 and PasteSpecial
Formats)
 
P

Pat

Sorry gentlemen but I think my question was a bit unclear, let me detail my
example a little better.

Example:

A1 = Car B1 = 1
A2 = Lorry B2 = 1
A3 = Lorry B3 = 2
A4 = Motorbike B4 = 1
A5 = Motorbike B5 = 2
A6 = Tractor B6 = 1
A7 = Motorbike B7 = 3
A8 = Car B8 = 2

None of the values can be duplicated in col B if there are more than one
value similar in col A. If for example I entered 1 in either B5 or B7 a
warning will appear to tell me that that value has already been used for
that item in col A. Likewise if I entered 2 in B7 and B5 has already the
value 2 a warning will appear.

Hope this explains what I am trying to achieve a little better.

regards
Pat
 
R

Ron Rosenfeld

Sorry gentlemen but I think my question was a bit unclear, let me detail my
example a little better.

Example:

A1 = Car B1 = 1
A2 = Lorry B2 = 1
A3 = Lorry B3 = 2
A4 = Motorbike B4 = 1
A5 = Motorbike B5 = 2
A6 = Tractor B6 = 1
A7 = Motorbike B7 = 3
A8 = Car B8 = 2

None of the values can be duplicated in col B if there are more than one
value similar in col A. If for example I entered 1 in either B5 or B7 a
warning will appear to tell me that that value has already been used for
that item in col A. Likewise if I entered 2 in B7 and B5 has already the
value 2 a warning will appear.

Hope this explains what I am trying to achieve a little better.
Click to expand...

If you are manually entering the values (in Col B) then you could use data
validation to prevent entering a duplicate value.

For example:

Select B1:B8
Data/Validation/Allow Custom:
Formula: =SUMPRODUCT((A1=$A$1:A1)*(B1=$B$1:B1))=1

With the combination of relative and absolute references in the formula, things
should adjust appropriately for each entry.

You can then customize the warning box messages as appropriate. Data
validation would prevent entering a duplicate value.

If you still want to use conditional formatting, then the formula would be:

=SUMPRODUCT((A1=$A$1:A1)*(B1=$B$1:B1))>1

for an illegal entry.


--ron
 
D

David McRitchie

You could generate Column B, as long as you don't
change anything like sorting your rows.

B1: =COUNTIF($A$1,$A1)
using the fill handle produces
B2: =COUNTIF($A$1,$A2)
--etc.--
 
Top