How to give remark based on specific sequence number?

[Manith]

Hi everyone,
I have an excel problem which I want to give the remark based on the sequence number.
And the given remark is "Display". Every sneaker shoes that display in showroom should being sequence number (as show in table).
For example, if the any item has size 41, then it's priority to be display first, if size 41 no stock, then need to display size 42 (Please see the table below). And each model/code has different color, but each color need to remark based on the sequence if any size no stock. So Column E (Remark) in blue highlight is where I want to give the remark for the "Display" for the stock that have size base on the sequence number. And I hope that the formula will be working with long list. And the formula can be used for both office 2021 and 2019.
Thank you!

Display Size.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2	Code	Color	Size	Qty	Remark			Display Sequene
3	AB01	Black	39	12				No.	Size	Remark
4	AB01	Black	40	5				1	41	First Priority to display
5	AB01	Black	41	6	Display			2	42	If Size 41 run out of stock, then display size 42
6	AB01	Black	42	3				3	40	If Size 42 run out of stock, then display size 40
7	AB01	Black	43	7				4	39	If Size 40 run out of stock, then display size 39
8	AB01	White	39	19				5	43	If Size 39 run out of stock, then display size 43
9	AB01	White	40	2
10	AB01	White	42	12	Display
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2	Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3	Display
18	AD09	Silver	43	23
19	AD09	Silver	40	24	Display
20	AD09	Silver	39	2
21	AD09	Black	41	12	Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7	Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6	Display
33	DY777	Navy	43	3	Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1	Display
38	PU888	Light Blue	41	5	Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6	Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11	Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6	Display
47	PT002	Blue	41	7	Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14	Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13	Display
55	PT002	White	39	15
Sheet1

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F
2	Code	Color	Size	Qty	Remark	Remark
3	AB01	Black	39	12
4	AB01	Black	40	5
5	AB01	Black	41	6	Display	Display
6	AB01	Black	42	3
7	AB01	Black	43	7
8	AB01	White	39	19
9	AB01	White	40	2
10	AB01	White	42	12	Display	Display
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2	Display	Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3	Display	Display
18	AD09	Silver	43	23
19	AD09	Silver	40	24	Display	Display
20	AD09	Silver	39	2
21	AD09	Black	41	12	Display	Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7	Display	Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6	Display	Display
33	DY777	Navy	43	3	Display	Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1	Display	Display
38	PU888	Light Blue	41	5	Display	Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6	Display	Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11	Display	Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6	Display	Display
47	PT002	Blue	41	7	Display	Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14	Display	Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13	Display	Display
55	PT002	White	39	15
Sheet1

Cell Formulas
Range	Formula
F3:F55	F3	=IFS(AND(C3=41,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=41)*($D$3:$D$100))>0),"Display", AND(C3<>41,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=41)*($D$3:$D$100))>0),"", AND(C3=42,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=42)*($D$3:$D$100))>0),"Display", AND(C3<>42,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=42)*($D$3:$D$100))>0),"", AND(C3=40,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=40)*($D$3:$D$100))>0),"Display", AND(C3<>40,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=40)*($D$3:$D$100))>0),"", AND(C3=39,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=39)*($D$3:$D$100))>0),"Display", AND(C3<>39,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=39)*($D$3:$D$100))>0),"", AND(C3=43,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=43)*($D$3:$D$100))>0),"Display", AND(C3<>43,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=43)*($D$3:$D$100))>0),"",TRUE,"")

 

[Peter_SSs]

Given that there are no rows in your sample data with Qty = 0, I assume that if an item has no stock it is removed from the list.

If that is the case and it needs to work in 2019 as well as 2021 then try the formula in column F. I have included another formula in column L in case you decide you can go with just 2021.

23 11 30.xlsm
	A	B	C	D	E	F	G	H	I		K	L
1
2	Code	Color	Size	Qty	Remark	Remark		Display Sequene				Remark
3	AB01	Black	39	12				No.	Size
4	AB01	Black	40	5				1	41
5	AB01	Black	41	6	Display	Display		2	42			Display
6	AB01	Black	42	3				3	40
7	AB01	Black	43	7				4	39
8	AB01	White	39	19				5	43
9	AB01	White	40	2
10	AB01	White	42	12	Display	Display						Display
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2	Display	Display						Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3	Display	Display						Display
18	AD09	Silver	43	23
19	AD09	Silver	40	24	Display	Display						Display
20	AD09	Silver	39	2
21	AD09	Black	41	12	Display	Display						Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7	Display	Display						Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6	Display	Display						Display
33	DY777	Navy	43	3	Display	Display						Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1	Display	Display						Display
38	PU888	Light Blue	41	5	Display	Display						Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6	Display	Display						Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11	Display	Display						Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6	Display	Display						Display
47	PT002	Blue	41	7	Display	Display						Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14	Display	Display						Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13	Display	Display						Display
55	PT002	White	39	15
Manith

Cell Formulas
Range	Formula
F3:F55	F3	=IF(MATCH(C3,I$4:I$8,0)=MIN(IF(A$3:A$55=A3,IF(B$3:B$55=B3,MATCH(C$3:C$55,I$4:I$8,0)))),"Display","")
L3:L55	L3	=LET(f,FILTER(C$3:C$55,(A$3:A$55=A3)*(B$3:B$55=B3)),IF(C3=INDEX(SORTBY(f,MATCH(f,I$4:I$8,0)),1),"Display",""))
Press CTRL+SHIFT+ENTER to enter array formulas.

If there might be items with Qty = 0 in the table, then here is an alternative for that.

23 11 30.xlsm
	A	B	C	D		F
1
2	Code	Color	Size	Qty		Remark
3	AB01	Black	39	12
4	AB01	Black	40	5
5	AB01	Black	41	0
6	AB01	Black	42	3		Display
7	AB01	Black	43	7
8	AB01	White	39	19
9	AB01	White	40	2		Display
10	AB01	White	42	0
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2		Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3		Display
18	AD09	Silver	43	23
19	AD09	Silver	40	0
20	AD09	Silver	39	2		Display
21	AD09	Black	41	12		Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7		Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6		Display
33	DY777	Navy	43	3		Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1		Display
38	PU888	Light Blue	41	5		Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6		Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11		Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6		Display
47	PT002	Blue	41	7		Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14		Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13		Display
55	PT002	White	39	15
Manith (2)

Cell Formulas
Range	Formula
F3:F55	F3	=IF(MATCH(C3,I$4:I$8,0)=MIN(IF(D$3:D$55>0,IF(A$3:A$55=A3,IF(B$3:B$55=B3,MATCH(C$3:C$55,I$4:I$8,0))))),"Display","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Manith]

Check this and revert -

Book1
	A	B	C	D	E	F
2	Code	Color	Size	Qty	Remark	Remark
3	AB01	Black	39	12
4	AB01	Black	40	5
5	AB01	Black	41	6	Display	Display
6	AB01	Black	42	3
7	AB01	Black	43	7
8	AB01	White	39	19
9	AB01	White	40	2
10	AB01	White	42	12	Display	Display
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2	Display	Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3	Display	Display
18	AD09	Silver	43	23
19	AD09	Silver	40	24	Display	Display
20	AD09	Silver	39	2
21	AD09	Black	41	12	Display	Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7	Display	Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6	Display	Display
33	DY777	Navy	43	3	Display	Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1	Display	Display
38	PU888	Light Blue	41	5	Display	Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6	Display	Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11	Display	Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6	Display	Display
47	PT002	Blue	41	7	Display	Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14	Display	Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13	Display	Display
55	PT002	White	39	15
Sheet1

Cell Formulas
Range	Formula
F3:F55	F3	=IFS(AND(C3=41,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=41)*($D$3:$D$100))>0),"Display", AND(C3<>41,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=41)*($D$3:$D$100))>0),"", AND(C3=42,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=42)*($D$3:$D$100))>0),"Display", AND(C3<>42,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=42)*($D$3:$D$100))>0),"", AND(C3=40,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=40)*($D$3:$D$100))>0),"Display", AND(C3<>40,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=40)*($D$3:$D$100))>0),"", AND(C3=39,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=39)*($D$3:$D$100))>0),"Display", AND(C3<>39,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=39)*($D$3:$D$100))>0),"", AND(C3=43,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=43)*($D$3:$D$100))>0),"Display", AND(C3<>43,SUMPRODUCT(($A$3:$A$100=A3)*($B$3:$B$100=B3)*($C$3:$C$100=43)*($D$3:$D$100))>0),"",TRUE,"")

Thank you so much Sir for your solution.

 

[Manith]

Given that there are no rows in your sample data with Qty = 0, I assume that if an item has no stock it is removed from the list.

If that is the case and it needs to work in 2019 as well as 2021 then try the formula in column F. I have included another formula in column L in case you decide you can go with just 2021.

23 11 30.xlsm
	A	B	C	D	E	F	G	H	I		K	L
1
2	Code	Color	Size	Qty	Remark	Remark		Display Sequene				Remark
3	AB01	Black	39	12				No.	Size
4	AB01	Black	40	5				1	41
5	AB01	Black	41	6	Display	Display		2	42			Display
6	AB01	Black	42	3				3	40
7	AB01	Black	43	7				4	39
8	AB01	White	39	19				5	43
9	AB01	White	40	2
10	AB01	White	42	12	Display	Display						Display
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2	Display	Display						Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3	Display	Display						Display
18	AD09	Silver	43	23
19	AD09	Silver	40	24	Display	Display						Display
20	AD09	Silver	39	2
21	AD09	Black	41	12	Display	Display						Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7	Display	Display						Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6	Display	Display						Display
33	DY777	Navy	43	3	Display	Display						Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1	Display	Display						Display
38	PU888	Light Blue	41	5	Display	Display						Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6	Display	Display						Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11	Display	Display						Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6	Display	Display						Display
47	PT002	Blue	41	7	Display	Display						Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14	Display	Display						Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13	Display	Display						Display
55	PT002	White	39	15
Manith

Cell Formulas
Range	Formula
F3:F55	F3	=IF(MATCH(C3,I$4:I$8,0)=MIN(IF(A$3:A$55=A3,IF(B$3:B$55=B3,MATCH(C$3:C$55,I$4:I$8,0)))),"Display","")
L3:L55	L3	=LET(f,FILTER(C$3:C$55,(A$3:A$55=A3)*(B$3:B$55=B3)),IF(C3=INDEX(SORTBY(f,MATCH(f,I$4:I$8,0)),1),"Display",""))
Press CTRL+SHIFT+ENTER to enter array formulas.

If there might be items with Qty = 0 in the table, then here is an alternative for that.

23 11 30.xlsm
	A	B	C	D		F
1
2	Code	Color	Size	Qty		Remark
3	AB01	Black	39	12
4	AB01	Black	40	5
5	AB01	Black	41	0
6	AB01	Black	42	3		Display
7	AB01	Black	43	7
8	AB01	White	39	19
9	AB01	White	40	2		Display
10	AB01	White	42	0
11	AB01	White	43	23
12	AB04	Blue	42	43
13	AB04	Blue	39	12
14	AB04	Blue	41	2		Display
15	AB04	Blue	43	23
16	AB04	Red	43	12
17	AB04	Red	39	3		Display
18	AD09	Silver	43	23
19	AD09	Silver	40	0
20	AD09	Silver	39	2		Display
21	AD09	Black	41	12		Display
22	AD09	Black	42	4
23	AD09	Black	39	23
24	AD09	Black	43	6
25	AD09	Orange	39	9
26	AD09	Orange	40	4
27	AD09	Orange	42	7		Display
28	AD09	Orange	43	4
29	DY777	Cream	39	9
30	DY777	Cream	40	2
31	DY777	Cream	43	1
32	DY777	Cream	42	6		Display
33	DY777	Navy	43	3		Display
34	DY777	Noir	40	5
35	DY777	Noir	43	1
36	DY777	Noir	42	3
37	DY777	Noir	41	1		Display
38	PU888	Light Blue	41	5		Display
39	PU888	Beige	42	7
40	PU888	Beige	41	6		Display
41	PU888	Beige	39	1
42	PU888	Green	39	2
43	PU888	Green	40	11		Display
44	PT002	Black	39	16
45	PT002	Black	40	6
46	PT002	Black	41	6		Display
47	PT002	Blue	41	7		Display
48	PT002	Blue	39	9
49	PT002	Purple	42	14		Display
50	PT002	Purple	43	12
51	PT002	Purple	39	9
52	PT002	White	42	7
53	PT002	White	43	4
54	PT002	White	41	13		Display
55	PT002	White	39	15
Manith (2)

Cell Formulas
Range	Formula
F3:F55	F3	=IF(MATCH(C3,I$4:I$8,0)=MIN(IF(D$3:D$55>0,IF(A$3:A$55=A3,IF(B$3:B$55=B3,MATCH(C$3:C$55,I$4:I$8,0))))),"Display","")
Press CTRL+SHIFT+ENTER to enter array formulas.

Your formulas work perfectly.
Thank you so much!

 

[Peter_SSs]

You are welcome. Glad to help. Thanks for the follow-up.