Conditional formatting question

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.

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)

"Pat" wrote in message
...
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.

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)

--
Return email address is not as DEEP as it appears
"Pat" wrote in message
...
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.

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


"Bob Phillips" wrote in message
...
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)

"Pat" wrote in message
...
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.

On Mon, 23 Aug 2004 22:19:33 +0100, "Pat" wrote:

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.

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

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.--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Pat" wrote in message ...
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


"Bob Phillips" wrote in message
...
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)

"Pat" wrote in message
...
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.

Still think my earlier response works.

--
Return email address is not as DEEP as it appears
"Pat" wrote in message
...
Sorry gentlemen but I think my question was a bit unclear, let me detail
my
example a little better.