Complex Condition

[AndrewGL]

Hello

I'm trying to do what I think is a complex condition in Excel.

In the below spreadsheet example:

• I want the user to be able to enter a Buying Style Ref (Column A) multiples times based on certain conditions.
• If the user enters a duplicate Buying Style Ref multiple times. Then the data must match the data in Columns E, F and G of the previously entered Buying Style Ref: for example:
  • Row 5 and Row 6 = correct
  • Row 7 = incorrect, as it does not match the original data in the first instance (Row 5) (E7 does not match E5)

In simple terms every time a User enters data into a row I want it do the following logic:

1. Does Buying Style Ref already exist.
   • If 'yes'
     • Does the data in Column E match with the first instance?
       • Yes - leave white
       • No - highlight red
     • Does the data in Column F match with the first instance?
       • Yes - leave white
       • No - highlight red
     • Does the data in Column G match with the first instance?
       • yes - leave white
       • No - highlight red

 

[JamesCanale]

MrExcelPlayground2.xlsm
	Q	R	S	T
1	Style	X	Y	Z
2	A1	100Cotton	Knit	China
3	A1	100Cotton	Knit	China
4	A1	50Cotton50Rabbit	Knit	China
5	B1	Silk	perl	New Jersey
6	C1	Rubber	cast	Mexico
7	A1	100Cotton	Otherthing	China
8	B1	Silk	perl	Trenton
Sheet38

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R3:T8	Expression	=IF(NOT(ISNA(MATCH($Q3,$Q$2:$Q2,0))),IF(R3=INDEX(R$2:R2,MATCH($Q3,$Q$2:$Q2,0)),FALSE,TRUE),FALSE)	text	NO

 

[AndrewGL]

MrExcelPlayground2.xlsm
	Q	R	S	T
1	Style	X	Y	Z
2	A1	100Cotton	Knit	China
3	A1	100Cotton	Knit	China
4	A1	50Cotton50Rabbit	Knit	China
5	B1	Silk	perl	New Jersey
6	C1	Rubber	cast	Mexico
7	A1	100Cotton	Otherthing	China
8	B1	Silk	perl	Trenton
Sheet38

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R3:T8	Expression	=IF(NOT(ISNA(MATCH($Q3,$Q$2:$Q2,0))),IF(R3=INDEX(R$2:R2,MATCH($Q3,$Q$2:$Q2,0)),FALSE,TRUE),FALSE)	text	NO

Hi James,

This looks amazing thank you !!!!!!!

I've slighty adapted your Conditional Formatting to match the cells and columns of my excel spreasheet. However, I'm not getting the same results as you. I can't see what I'm doing wrong.

=IF(NOT(ISNA(MATCH($A6,$A$5:$A5,0))),IF(E6=INDEX(E$5:E5,MATCH($A6,$A$5:$A5,0)),FALSE,TRUE),FALSE)

 

[JamesCanale]

Any chance you can download the XL2BB addin from this site - it makes posting easier. I can't say why yours doesn't work - it looks right. It's giving me flashbacks to a similar problem a few weeks ago.

Any chance there are some invisible differences in the cells, like an extra space after 100% Cotton_ or something like that? And I couldn't say why G12 is red.

 

[AndrewGL]

Any chance you can download the XL2BB addin from this site - it makes posting easier. I can't say why yours doesn't work - it looks right. It's giving me flashbacks to a similar problem a few weeks ago.

Any chance there are some invisible differences in the cells, like an extra space after 100% Cotton_ or something like that? And I couldn't say why G12 is red.

 

[AndrewGL]

Hello

Yes, sorry, I was being an idiot. I can't thank you enough for your help:

Commodity Code Sheet test example - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	DEPARTMENT & TEAM:	Enter your department name and team here
2	BUYERS & MERCHANDISERS							CUSTOMS TEAM
3	BUYING STYLE REF	IMAGE	PRODUCT TYPE	DESCRIPTION - NB DO NOT USE THE WORD "TOP"!!	PRODUCT COMPOSITION (MATERIAL)	KNITTED OR WOVEN	PREFERENCE COUNTY	PREFERENCE CODE	COMMODITY CODE	DUTY RATE	BUYER COMMENTS	IMPORTS COMMENTS
4	The buying style reference number	If you feel it is necessary or helpful, please, insert an image		Insert gender & if required if for boys or girls	Such as 65% Poly / 35% Cotton	If not applicable leave blank					Please add any comments if needed	Please add any comments if needed
5	ABS21TT001		Ladies Black T-shirt		100% Cotton	Knitted	China
6	ABS21TT001		Ladies Red T-shirt		100% Cotton	Knitted	China
7	ABS21TT001		Ladies White T-shirt		50% Cotton 50% Wool	Knitted	China
8	ABS21TT001		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
9	ABS21TT002		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
10	ABS21TT003		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
11	ABS21TT004		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
12	ABS21TT005		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
13	ABS21TT005		Ladies Red T-shirt		50% Cotton 50% Wool	Knitted	India
14	ABS21TT005		Ladies White T-shirt		50% Cotton 50% Wool	Knitted	France
15	ABS21TT001		Ladies Black T-shirt		100% Cotton	Knitted	Italy
Clothing & Accessories

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:G102	Expression	=IF(NOT(ISNA(MATCH($A7,$A$5:$A6,0))),IF(E7=INDEX(E$5:E6,MATCH($A7,$A$5:$A6,0)),FALSE,TRUE),FALSE)	text	YES

Cells with Data Validation
Cell	Allow	Criteria
A5:A15	Custom	=AND(COUNTIF($A$5:$A$500,A5)=1, LEN(A5)=10)

 

[JamesCanale]

It looks like your conditional formatting formulas are off by one row:

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G
1	DEPARTMENT & TEAM:	Enter your department name and team here
2	BUYERS & MERCHANDISERS
3	BUYING STYLE REF	IMAGE	PRODUCT TYPE	DESCRIPTION - NB DO NOT USE THE WORD "TOP"!!	PRODUCT COMPOSITION (MATERIAL)	KNITTED OR WOVEN	PREFERENCE COUNTY
4	The buying style reference number	If you feel it is necessary or helpful, please, insert an image		Insert gender & if required if for boys or girls	Such as 65% Poly / 35% Cotton	If not applicable leave blank
5	ABS21TT001		Ladies Black T-shirt		100% Cotton	Knitted	China
6	ABS21TT001		Ladies Red T-shirt		100% Cotton	Knitted	China
7	ABS21TT001		Ladies White T-shirt		50% Cotton 50% Wool	Knitted	China
8	ABS21TT001		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
9	ABS21TT002		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
10	ABS21TT003		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
11	ABS21TT004		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
12	ABS21TT005		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
13	ABS21TT005		Ladies Red T-shirt		50% Cotton 50% Wool	Knitted	India
14	ABS21TT005		Ladies White T-shirt		50% Cotton 50% Wool	Knitted	France
15	ABS21TT001		Ladies Black T-shirt		100% Cotton	Knitted	Italy
Sheet39

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:G15	Expression	=IF(NOT(ISNA(MATCH($A6,$A$5:$A5,0))),IF(E6=INDEX(E$5:E5,MATCH($A6,$A$5:$A5,0)),FALSE,TRUE),FALSE)	text	NO

 

[AndrewGL]

It looks like your conditional formatting formulas are off by one row:

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G
1	DEPARTMENT & TEAM:	Enter your department name and team here
2	BUYERS & MERCHANDISERS
3	BUYING STYLE REF	IMAGE	PRODUCT TYPE	DESCRIPTION - NB DO NOT USE THE WORD "TOP"!!	PRODUCT COMPOSITION (MATERIAL)	KNITTED OR WOVEN	PREFERENCE COUNTY
4	The buying style reference number	If you feel it is necessary or helpful, please, insert an image		Insert gender & if required if for boys or girls	Such as 65% Poly / 35% Cotton	If not applicable leave blank
5	ABS21TT001		Ladies Black T-shirt		100% Cotton	Knitted	China
6	ABS21TT001		Ladies Red T-shirt		100% Cotton	Knitted	China
7	ABS21TT001		Ladies White T-shirt		50% Cotton 50% Wool	Knitted	China
8	ABS21TT001		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
9	ABS21TT002		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
10	ABS21TT003		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
11	ABS21TT004		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
12	ABS21TT005		Ladies Black T-shirt		50% Cotton 50% Wool	Knitted	China
13	ABS21TT005		Ladies Red T-shirt		50% Cotton 50% Wool	Knitted	India
14	ABS21TT005		Ladies White T-shirt		50% Cotton 50% Wool	Knitted	France
15	ABS21TT001		Ladies Black T-shirt		100% Cotton	Knitted	Italy
Sheet39

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:G15	Expression	=IF(NOT(ISNA(MATCH($A6,$A$5:$A5,0))),IF(E6=INDEX(E$5:E5,MATCH($A6,$A$5:$A5,0)),FALSE,TRUE),FALSE)	text	NO

YOU ARE AMAZING !!!!!!!!

THANK YOU THANK YOU THANK YOU