Formula in the helper column. need duplicate to be count as One

[Lukma]

Good day Friends

Need help with the formula in my Helper column in Column B,
I have joined Column D & Column G and did a count with it.
the help I need in this formula is that if the Mus no is the same and the Port name repeats then it should still count as as one

Need the formula to provide me with this count if it is duplicate count it as one

Appreciate your help and support

Regards

OTD Data Extraction from Jan - Feb 2024.xlsx
	B	C	D	E	F	G
3	Help column	Month	MUS No:	Vessel Name	Port Name
4	& Rig Yemillah 0	Jan			Rig Yemillah	Rig Yemillah
5	& Umm Al Anbar 0	Jan			Umm Al Anbar Island-AD68	Umm Al Anbar
6	& Jopetwill-300 0	Jan			Barge JOPET WILL-300	Jopetwill-300
7		Jan			Umm Shaif Field	#N/A
8	236 & Rig Al Sila 1	Jan	236	MARCAP-203	Rig Al Sila	Rig Al Sila
9	& Rig Al Gharbia 0	Jan			Rig Al Gharbia	Rig Al Gharbia
10	146 & Al Qatia Island 1	Jan	146	ADNOC-1010	Al Qatia Island-AD78	Al Qatia Island
11	194 & Rig Al Hudairiyat 1	Jan	194	MARSOL SCAMANDER	Rig Al Hudairiyat	Rig Al Hudairiyat
12	& Barge Shammal 1	Jan			Barge Shammal	Barge Shammal
13	283 & Rig Al Lulu 1	Jan	283	B-GULF-101	Rig Al Lulu	Rig Al Lulu
14	334 & Al Qatia Island 1	Jan	334	WARDEH-1	Al Qatia Island-AD78	Al Qatia Island
15	578 & Rig Al Saadiyat 1	Jan	578	STANFORD ALPHA	Rig Al Saadiyat	Rig Al Saadiyat
16	406 & Rig Vivekanand-3 1	Jan	406	ADNOC-223	Rig VKN 3	Rig Vivekanand-3
17	451 & Rig Al Jubail 1	Jan	451	ADNOC-A02	Rig Al Jubail	Rig Al Jubail
18	& Rig Al Jubail 1	Jan			Rig Al Jubail	Rig Al Jubail
19	578 & Rig Al Saadiyat 2	Jan	578	STANFORD ALPHA	Rig Al Saadiyat	Rig Al Saadiyat
20	& Rig Al Jubail 2	Jan			Rig Al Jubail	Rig Al Jubail
21	69 & Rig Vivekanand-3 1	Jan	69	MARCAP-203	Rig VKN 3	Rig Vivekanand-3
22	55 & Rig Muhaiyimat 1	Jan	55	ADNOC-229	Rig Muhaiyimat	Rig Muhaiyimat
23	541 & ACPT 1	Jan	541	ADNOC-850	ACPT Complex	ACPT
24	236 & Rig Al Sila 2	Jan	236	MARCAP-203	Rig Al Sila	Rig Al Sila
25	31 & Rig Al Bateel 1	Jan	31	MUTAWA-402	Rig Al Bateel	Rig Al Bateel
26	31 & Rig Al Bateel 2	Jan	31	MUTAWA-402	Rig Al Bateel	Rig Al Bateel
CargoRequests (3)

Cell Formulas
Range	Formula
B4:B26	B4	=IFERROR(D4&" & "&G4&" "&COUNTIFS(D$4:D4,D4,G$4:G4,G4),"")
E4:E26	E4	=IFERROR(VLOOKUP(D4,'[2024 - VESSELS ONSHORE JETTY OPERATIONS.xlsx]2023-Vessel Onshore Operations'!$D$4:$G$60000,2,0),"")

 

[KRice]

Should "236 & Rig Al Sila 2" in row 24 appear with a 1 or 2? Same question for row 19...should it also have a 1 rather than a 2?

It seems that, based on your description, every row would result in a 1...every row is either a unique combination of a MUS No and a Port name (therefore a count of 1) or it is a repeated combination of MUS No/Port name shown above it (which is to be limited to a count of 1).

Also, the use of COUNTIFS is puzzling. For the first four rows, the MUS No. cells are blank, so does a blank MUS No. and any Port name count as 1?...you show those as 0. Rows 4-7 and 9 return the expected result for COUNTIFS...if the cell is blank, there is nothing to count, so COUNTIFS delivers a 0. Yet rows 12, 18, and 20 are counting the apparently blanks cells...so something is different...are those cells actually blank? Why do some apparently blank cells deliver a count and others do not?

 

[Lukma]

Dear Krice

Yes if it's repeated for the same MUS No: then I want it to count as 1 for the same example for what i need below

many thanks

 

[Lukma]

Dear Krice

Thanks if the cell is blank I don't need it to count because this is the master data with items, so my idea is to number them while as one even if port name is repeated in same Mus No;
so it should still count it or number as one.

regards

 

[KRice]

Okay...thank you. That's different from your first post, but the 1's in the orange cells make sense. So that still leaves the question about the different behavior with blank cells. Why do some blanks give counts of 0 and other blanks give counts of 1 or 2?

 

[KRice]

If blanks are not to be counted, then you do not need a counting formula at all, do you? You just need to check whether the column E cell has something in it, and if so, then "1", otherwise "0". I've isolated the COUNTIFS formula in column J (note that all blank MUS No cells result in 0). Compare those results to a simple --(E<>"") formula in column I to see if those are the values you expect.

MrExcel_20240305.xlsx
	B	C	D	E	F	G	H	I	J
3	Help column	Month	MUS No:	Vessel Name	Port Name
4	& Rig Yemillah 0	Jan			Rig Yemillah	Rig Yemillah		0	0
5	& Umm Al Anbar 0	Jan			Umm Al Anbar Island-AD68	Umm Al Anbar		0	0
6	& Jopetwill-300 0	Jan			Barge JOPET WILL-300	Jopetwill-300		0	0
7		Jan			Umm Shaif Field	#N/A		0	0
8	236 & Rig Al Sila 1	Jan	236	MARCAP-203	Rig Al Sila	Rig Al Sila		1	1
9	& Rig Al Gharbia 0	Jan			Rig Al Gharbia	Rig Al Gharbia		0	0
10	146 & Al Qatia Island 1	Jan	146	ADNOC-1010	Al Qatia Island-AD78	Al Qatia Island		1	1
11	194 & Rig Al Hudairiyat 1	Jan	194	MARSOL SCAMANDER	Rig Al Hudairiyat	Rig Al Hudairiyat		1	1
12	& Barge Shammal 0	Jan			Barge Shammal	Barge Shammal		0	0
13	283 & Rig Al Lulu 1	Jan	283	B-GULF-101	Rig Al Lulu	Rig Al Lulu		1	1
14	334 & Al Qatia Island 1	Jan	334	WARDEH-1	Al Qatia Island-AD78	Al Qatia Island		1	1
15	578 & Rig Al Saadiyat 1	Jan	578	STANFORD ALPHA	Rig Al Saadiyat	Rig Al Saadiyat		1	1
16	406 & Rig Vivekanand-3 1	Jan	406	ADNOC-223	Rig VKN 3	Rig Vivekanand-3		1	1
17	451 & Rig Al Jubail 1	Jan	451	ADNOC-A02	Rig Al Jubail	Rig Al Jubail		1	1
18	& Rig Al Jubail 0	Jan			Rig Al Jubail	Rig Al Jubail		0	0
19	578 & Rig Al Saadiyat 2	Jan	578	STANFORD ALPHA	Rig Al Saadiyat	Rig Al Saadiyat		1	2
20	& Rig Al Jubail 0	Jan			Rig Al Jubail	Rig Al Jubail		0	0
21	69 & Rig Vivekanand-3 1	Jan	69	MARCAP-203	Rig VKN 3	Rig Vivekanand-3		1	1
22	55 & Rig Muhaiyimat 1	Jan	55	ADNOC-229	Rig Muhaiyimat	Rig Muhaiyimat		1	1
23	541 & ACPT 1	Jan	541	ADNOC-850	ACPT Complex	ACPT		1	1
24	236 & Rig Al Sila 2	Jan	236	MARCAP-203	Rig Al Sila	Rig Al Sila		1	2
25	31 & Rig Al Bateel 1	Jan	31	MUTAWA-402	Rig Al Bateel	Rig Al Bateel		1	1
26	31 & Rig Al Bateel 2	Jan	31	MUTAWA-402	Rig Al Bateel	Rig Al Bateel		1	2
CargoRequests

Cell Formulas
Range	Formula
I4:I26	I4	=--(E4<>"")
J4:J26	J4	=COUNTIFS(D$4:D4,D4,G$4:G4,G4)
B4:B26	B4	=IFERROR(D4&" & "&G4&" "&COUNTIFS(D$4:D4,D4,G$4:G4,G4),"")
E4:E26	E4	=IFERROR(VLOOKUP(D4,'2023-Vessel Onshore Operations'!$D$4:$G$60000,2,0),"")

 

[Lukma]

Dear Krice

Thanks, column I did exactly what I needed, but I need to join the MUS No: with the Port Name as this is for a different MUS no

so I don't mind if the empty cell is not blank my main concern is focusing on the Mus No and Port name.

so is there any way around this?

Regards

 

[KRice]

but I need to join the MUS No: with the Port Name as this is for a different MUS no

I don't understand what you mean. If you mean you need to join the text together as you have done in B4 and down, then replace the B4 formula with:

=D4&" & "&G4&" "&--(E4<>"")