Organizing unique list ID's based off of flagged (Valid/Invalid) items

[MurdochQuill]

Hi all,

I have another question for today, I've been working through some list-based issues which seem simple but I just cant put it together in a workable format.

What I'm trying to do, is match unique ID's found in column "A" with some values. Based off of some simple rules, I am wanting to flag each rule with "Valid" or "Invalid". I've been fine up until this point, where I'm now trying to flag the unique ID as "No Valid flags" when all entries for the ID on the A column are marked as "Invalid" on any rule definition column.

So it should flag all unique ID's as "No valid flags" where no valid value for it exists. Basically I want to get rid of all the "Invalid" values where a "Valid" value exists for the same unique ID.

I have attached the spreadsheet example to this post

Any help is appreciated.

Flags.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	First	Final	Difference		Init >9	Final <1	Difference <2	Flags?		No Valids per unique ID?		Contains VALID or No Valid flags
2	Pond3	9	8.61	0.39		VALID	VALID	INVALID	INVALID		No Valid flags		YES
3	Pond3	9	8.6	0.4		VALID	VALID	INVALID	INVALID		No Valid flags		YES
4	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags		YES
5	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags		YES
6	Pond2	10	8	2		INVALID	VALID	VALID	INVALID
7	Pond1	11	5	6		INVALID	VALID	VALID	INVALID		No Valid flags		YES
8	Pond2	9	7	2		VALID	VALID	VALID	VALID				YES
9	Pond2	9	7	2		VALID	VALID	VALID	VALID				YES
10	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags		YES
11	Pond4	10	1	9		INVALID	VALID	VALID	INVALID		No Valid flags		YES
12	Pond4	10	1	9		INVALID	VALID	VALID	INVALID		No Valid flags		YES
13	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
14	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
15	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
16	Pond5	9	2	7		VALID	VALID	VALID	VALID				YES
17	Pond6	9	7	2		VALID	VALID	VALID	VALID				YES
18	Pond7	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags		YES
19	Pond8	9	7	2		VALID	VALID	VALID	VALID
20	Pond9	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags		YES
21	Pond10	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags		YES
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	=B2-C2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:I22	Cell Value	beginning with "INVALID"	text	NO
F2:I22	Cell Value	beginning with "VALID"	text	NO

 

[Peter_SSs]

Is it just one of the columns in your shown mini-sheet that you are wanting help with?
If so, which column?
If not, which columns?

 

[MurdochQuill]

Is it just one of the columns in your shown mini-sheet that you are wanting help with?
If so, which column?
If not, which columns?

K column. I don't know how to go about getting to the result of No valid flags for Unique ID "X"

 

[Peter_SSs]

K column.

Like this then?

21 05 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ID	First	Final	Difference		Init >9	Final <1	Difference <2	Flags?		No Valids per unique ID?
2	Pond3	9	8.61	0.39		VALID	VALID	INVALID	INVALID		No Valid flags
3	Pond3	9	8.6	0.4		VALID	VALID	INVALID	INVALID		No Valid flags
4	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags
5	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags
6	Pond2	10	8	2		INVALID	VALID	VALID	INVALID
7	Pond1	11	5	6		INVALID	VALID	VALID	INVALID		No Valid flags
8	Pond2	9	7	2		VALID	VALID	VALID	VALID
9	Pond2	9	7	2		VALID	VALID	VALID	VALID
10	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags
11	Pond4	10	1	9		INVALID	VALID	VALID	INVALID		No Valid flags
12	Pond4	10	1	9		INVALID	VALID	VALID	INVALID		No Valid flags
13	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
14	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
15	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
16	Pond5	9	2	7		VALID	VALID	VALID	VALID
17	Pond6	9	7	2		VALID	VALID	VALID	VALID
18	Pond7	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags
19	Pond8	9	7	2		VALID	VALID	VALID	VALID
20	Pond9	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags
21	Pond10	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags
Valid Flags

Cell Formulas
Range	Formula
D2:D21	D2	=B2-C2
K2:K21	K2	=IF(COUNTIFS(A$2:A$21,A2,I$2:I$21,"INVALID")=COUNTIF(A$2:A$21,A2),"No Valid flags","")

Edit: Or, although this is slightly longer, it should reduce any calculation overhead a little.

=IF(A2=A1,K1,IF(COUNTIFS(A$2:A$21,A2,I$2:I$21,"INVALID")=COUNTIF(A$2:A$21,A2),"No Valid flags",""))

 

[MurdochQuill]

Like this then?

21 05 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ID	First	Final	Difference		Init >9	Final <1	Difference <2	Flags?		No Valids per unique ID?
2	Pond3	9	8.61	0.39		VALID	VALID	INVALID	INVALID		No Valid flags
3	Pond3	9	8.6	0.4		VALID	VALID	INVALID	INVALID		No Valid flags
4	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags
5	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags
6	Pond2	10	8	2		INVALID	VALID	VALID	INVALID
7	Pond1	11	5	6		INVALID	VALID	VALID	INVALID		No Valid flags
8	Pond2	9	7	2		VALID	VALID	VALID	VALID
9	Pond2	9	7	2		VALID	VALID	VALID	VALID
10	Pond3	10	8.6	1.4		INVALID	VALID	INVALID	INVALID		No Valid flags
11	Pond4	10	1	9		INVALID	VALID	VALID	INVALID		No Valid flags
12	Pond4	10	1	9		INVALID	VALID	VALID	INVALID		No Valid flags
13	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
14	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
15	Pond5	10	2	8		INVALID	VALID	VALID	INVALID
16	Pond5	9	2	7		VALID	VALID	VALID	VALID
17	Pond6	9	7	2		VALID	VALID	VALID	VALID
18	Pond7	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags
19	Pond8	9	7	2		VALID	VALID	VALID	VALID
20	Pond9	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags
21	Pond10	10	7	3		INVALID	VALID	VALID	INVALID		No Valid flags
Valid Flags

Cell Formulas
Range	Formula
D2:D21	D2	=B2-C2
K2:K21	K2	=IF(COUNTIFS(A$2:A$21,A2,I$2:I$21,"INVALID")=COUNTIF(A$2:A$21,A2),"No Valid flags","")

Edit: Or, although this is slightly longer, it should reduce any calculation overhead a little.

=IF(A2=A1,K1,IF(COUNTIFS(A$2:A$21,A2,I$2:I$21,"INVALID")=COUNTIF(A$2:A$21,A2),"No Valid flags",""))

Perfect!

Also, out of curiosity how does the code edited in at the bottom reduce overhead?

 

[Peter_SSs]

Perfect!

Good news!

Also, out of curiosity how does the code edited in at the bottom reduce overhead?

In the original formula, those COUNTIFS and COUNTIF functions have to be calculated for every row.
In the last formula, if the value in column A is the same as the value in column A in the row above, the col K value is just copied from the row above so no COUNTIFS or COUNTIF is required for that row.
For your sample data, the blue rows below are simply copied from above and only the yellow rows require the more complex calculation.

21 05 16.xlsm
	A										K
1	ID										No Valids per unique ID?
2	Pond3										No Valid flags
3	Pond3										No Valid flags
4	Pond3										No Valid flags
5	Pond3										No Valid flags
6	Pond2
7	Pond1										No Valid flags
8	Pond2
9	Pond2
10	Pond3										No Valid flags
11	Pond4										No Valid flags
12	Pond4										No Valid flags
13	Pond5
14	Pond5
15	Pond5
16	Pond5
17	Pond6
18	Pond7										No Valid flags
19	Pond8
20	Pond9										No Valid flags
21	Pond10										No Valid flags
Valid Flags

Cell Formulas
Range	Formula
K2:K21	K2	=IF(A2=A1,K1,IF(COUNTIFS(A$2:A$21,A2,I$2:I$21,"INVALID")=COUNTIF(A$2:A$21,A2),"No Valid flags",""))