Help wih formula reqd.

[Ian Betteridge]

Good morning all,
Please see table below..

FEEDBACK TEST.xls
	A	B	C	D	E	F	G	H
1	Order	OPC	CC	DC	CT	Feedback Counts	Max Count	Feedback
2	1101054570	0001	0010	0021	high pressure in system	4	4	1
3	1101054571	0013	0013	0021	solid sugar in worm	4	4	1
4	1101054572	0009	0019	0014	1 of normal conditions overlooked	4	4	1
5	1101055392	0010	0016	0014	actuator or valve stuck in pos.	4	4	1
6	1101055393	0003	0019	0014	incorrect understanding of conditions ne	4	4	1
7	1101056241	0015		0003		3	3	0
8	1101056241	0021		0003	invertor comms error	3	3	0
9	1101056241	0021	0023		invertor damaged because of Shortcircuit	3	3	0
10	1101057220	0008	0013	0021	boxes jammed on c/v	4	4	1
11	1101057221	0139	0013	0003	unit had been knoked over	4	4	1
12	1101057222	0008		0021	Too many heaters on the syst.	3	3	0
13	1101057236	0116	0025	0013	excessive chocolate on mould	4	4	1
14	1101057258	0008	0013	0014	no start - box over the sensor	4	4	1
15	1101057363	0047	0022	0016	Glue guns got knocked ot pos.	4	4	1
16	1101057364	0009	0019	0014	lack of knowledge	4	4	1
17	1101057365	0075	0022	0016	latching sol not set correctly	4	4	1
FEEDBACK

At present columns F & G are helper columns to get a result in column H.
The result in column H is 1 if columns B,C,D & E are completed in the same row for any given order listed in column A. If any of columns B,C,D or E are missing on the same row that paticular row fails the criterea set although the same number may qualify on another line. i.e. rows 7,8 & 9 (same order) would qualify if any one of three rows had all four entered.

Question..
How can I make it so as long as all four columns B,C,D & E were entered on any rows for the same order then column H wold return 1 otherwise 0.
i.e. In the example given the order number 1101056241 would return 1 in H7,H8 & H9.

Hope I've explained ok.
Regards Ian

 

[Domenic]

Try...

H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ISNUMBER(MATCH(1,IF($A$2:$A$17=A2,IF($B$2:$B$17<>"",IF($C$2:$C$17<>"",IF($D$2:$D$17<>"",IF($E$2:$E$17<>"",1))))),0)),1,0)