trying to figure conditional format for 2 columns of ID's

[neowok]

I have 2 columns of ID's, both of which may contain duplicates or blanks. What I need to do is if an ID in column A has textlonger than 3 characters in the same row in column B then *every occurance* of that Id in column A should be coloured red. I am basically flagging up that if an Id in column A has been allocated to something in column B then all occurances of that Id in colunm A can no longer be used, so colour anything with that Id in red.

So if column A had 5 occurances of LP111, and any one of those had XYZABCDE in column B then all 5 cells with that ID in column A would be red.

I'm sure its not that hard, just struggling to get my head around the logic for it.

 

[Special-K99]

for your example

=(SUMPRODUCT((A$1:A$5=A1)*(LEN(B$1:B$5)>3))>0)

If you select the whole range of column A then it would probably have to be

=(SUMPRODUCT((A$1:A$5=A$1)*(LEN(B$1:B$5)>3))>0)

Obviously adjust the finishing row number as required.

 

[Peter_SSs]

Try this
Select A2:Axx and apply the Conditional Formatting (use a formula) shown.

Excel Workbook
	A	B
1	ID	Data
2	ID 1	abc
3	ID 2	de
4	ID 3	e
5
6	ID 3	dfrgt
7	ID 4
8	ID 5	45x
9	ID 7	abcd
10
11	ID 3	sd
12	ID 4	sfasfasfas
13	ID 5	ss
14	ID 1
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AGGREGATE(14,6,LEN(B$2:B$14)/(A$2:A$14=A2),1)>3	Abc

 

[neowok]

thanks they should do it