Produce List of Values Based on ID / Batch

[surkdidat]

Hi

I have data as below, which has the batch number in column A, and a Product ID in Column C

I need to know (for Batch 5 only)

If its Unique to batch 5
If it is in Batch 5 and Batch 4
If it is in Batch 5 and Batch 3
If it is in Batch 5, Batch 4 and Batch 3

(II dont need to know if it is in 3 and 4 for example)

	Column A ( BATCH)	Column B (PRODUCT ID)		Column d (BATCH 5)	In Batch 3?	In Batch 4?	In Both?
Row 1	3	35901		65432
Row 2	3	23456		3591
Row 3	3	23145		23456
Row 4	3	13579		24680
Row 5	3	12345		13579
Row 6	4	65432		12345
Row 7	4	23456
Row 8	4	12345
Row 9	5	65432
Row 10	5	35901
	5	23456
	5	24680
	5	13579
	5	12345

 

[kweaver]

So, if I follow this since you didn't provide solutions 65432 is YES for batch 4; 35901 (assume you mistyped -- better to use XL2BB, by the way) is YES in batch 3; etc. ???

How about:

continents.xlsx
	A	B	C	D	E	F	G
1	BATCH	PRODUCT ID		BATCH 5	Batch 3	Batch 4	Both
2	3	35901		65432	NO	YES	NO
3	3	23456		35901	YES	NO	NO
4	3	23145		23456	YES	YES	YES
5	3	13579		24680	NO	NO	NO
6	3	12345		13579	YES	NO	NO
7	4	65432		12345	YES	YES	YES
8	4	23456
9	4	12345
10	5	65432
11	5	35901
12	5	23456
13	5	24680
14	5	13579
15	5	12345
Sheet3

Cell Formulas
Range	Formula
E2:F7	E2	=IF(0=SUMPRODUCT(((1*RIGHT(E$1,1))=$A$2:$A$15)*($D2=$B$2:$B$15)),"NO","YES")
G2:G7	G2	=IF(AND("YES"=E2,"YES"=F2),"YES","NO")

 

[surkdidat]

Column d (BATCH 5)​	In Batch 3?​	In Batch 4?​	In Both?​
65432​	NO​	YES​	NO​
35901​	YES​	YES​	NO​
23456​	YES​	YES​	YES​
24680​	NO​	NO​	NO​
13579​	YES​	NO​	NO​
12345​	YES​	YES​	YES​

 

[surkdidat]

So, if I follow this since you didn't provide solutions 65432 is YES for batch 4; 35901 (assume you mistyped -- better to use XL2BB, by the way) is YES in batch 3; etc. ???

Column d (BATCH 5)​	In Batch 3?​	In Batch 4?​	In Both?​
65432​	NO​	YES​	NO​
35901​	YES​	YES​	NO​
23456​	YES​	YES​	YES​
24680​	NO​	NO​	NO​
13579​	YES​	NO​	NO​
12345​	YES​	YES​	YES​