Countifs? or other formula?

[feni1388]

Hello,

It looks simple but I can't get any idea on how to do it.
If anyone can help, I would really appreciate it.

As you can see from the table below, on the right side is all the details of each item and its delivery code.
Sometimes the same customer code will have 2 or even more delivery code.
In the end, all the delivery code will have to be summarized like the table on the right side.
I don't mind if the whole number is shown again after the slash sign (Order No. 503172/503173)

Delivery code	Customer code	Item no.		Result that I'm trying to achieve
503172	HIM03001	AB02		AB02	HIM03001	Order No. 503172/3
503172	HIM03001	AS15		AS15	HIM03001	Order No. 503172/3
503172	HIM03001	AS25		AS25	HIM03001	Order No. 503172/3
503172	HIM03001	GI98		GI98	HIM03001	Order No. 503172/3
503172	HIM03001	KZ15		KZ15	HIM03001	Order No. 503172/3
503172	HIM03001	AS02		AS02	HIM03001	Order No. 503172/3
503173	HIM03001	AB03		AB03	HIM03001	Order No. 503172/3
503173	HIM03001	AB05		AB05	HIM03001	Order No. 503172/3

Thank you in advace.

 

[Eric W]

Try:

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1	Delivery code	Customer code	Item no.		Result that I'm trying to achieve
2	503172	HIM03001	AB02		AB02	HIM03001	Order No. 503172/503173
3	503172	HIM03001	AS15		AS15	HIM03001	Order No. 503172/503173
4	503172	HIM03001	AS25		AS25	HIM03001	Order No. 503172/503173
5	503172	HIM03001	GI98		GI98	HIM03001	Order No. 503172/503173
6	503172	HIM03001	KZ15		KZ15	HIM03001	Order No. 503172/503173
7	503172	HIM03001	AS02		AS02	HIM03001	Order No. 503172/503173
8	503173	HIM03001	AB03		AB03	HIM03001	Order No. 503172/503173
9	503173	HIM03001	AB05		AB05	HIM03001	Order No. 503172/503173
Sheet4

Cell Formulas
Range	Formula
G2:G9	G2	="Order No. "&TEXTJOIN("/",1,UNIQUE(FILTER($A$2:$A$9,$B$2:$B$9=F2)))

 

[feni1388]

Thank you..... it works exactly like what I'm trying to make.