Formula solution to extract unique based on 2 criteria name and month

[Lukma]

Hi Guys

I came across this formula here and i was wondering how to go about it which am not sure, so i decided have a play around the formula to extract the unique voyage no based on 2 criteria the name and month.

My Aim is to extract total voyage made for each name so i consider that at most each name can make 10 trips with different voyage number

in Column A i have the month format, then column B I have the voyage listed with duplicate, in column F2 i have predicted that for each name at least 10 trips in a month and in column F1 will be creating a drop down to select month so my idea is that in column G2 i need to extract the unique Voyage for each name without duplicate base on criteria for every month.

i will be very happy is this formula have answer to that and also if there is better solution without the use of array formula to extract the unique voyage

Book2
	A	B	C	D	E	F	G	H
1					Month	01-Jan-21	Extract Unique Voyage Base 2 Criteria Name and Month
2		Voyage No	Name		maximum Trip in a Month 10 to 11 for each	Name	Voyage No
3	01-Jan-21	3705	ADNOC-810		1	ADNOC-810	#N/A
4	01-Jan-21	3705	ADNOC-810		2	ADNOC-810
5	01-Jan-21	3705	ADNOC-810		3	ADNOC-810
6	01-Jan-21	3705	ADNOC-810		4	ADNOC-810
7	01-Jan-21	3706	ADNOC-224		5	ADNOC-810
8	01-Jan-21	3706	ADNOC-224		6	ADNOC-810
9	01-Jan-21	3706	ADNOC-224		7	ADNOC-810
10	01-Jan-21	3706	ADNOC-224		8	ADNOC-810
11	01-Jan-21	3706	ADNOC-224		9	ADNOC-810
12	01-Jan-21	3666-O	ASL SWIFT		10	ADNOC-810
13	01-Jan-21	3666-O	ASL SWIFT		1	ADNOC-224
14	01-Jan-21	3666-O	ASL SWIFT		2	ADNOC-224
15	01-Jan-21	3707	Z-POWER		3	ADNOC-224
16	01-Jan-21	3707	Z-POWER		4	ADNOC-224
17	01-Jan-21	3707	Z-POWER		5	ADNOC-224
18	01-Jan-21	3707	Z-POWER		6	ADNOC-224
19	01-Jan-21	3707	Z-POWER		7	ADNOC-224
20	01-Jan-21	3708	SMIT LUZON		8	ADNOC-224
21	01-Jan-21	3708	SMIT LUZON		9	ADNOC-224
22	01-Jan-21	3708	SMIT LUZON		10	ADNOC-224
23	01-Jan-21	3709	SMIT LUMUT
24	01-Jan-21	3709	SMIT LUMUT
25	01-Jan-21	3709	SMIT LUMUT
26	01-Jan-21	3709	SMIT LUMUT
27	01-Jan-21	3710	B-LIBERTY-313
28	01-Jan-21	3710	B-LIBERTY-313
29	01-Jan-21	3710	B-LIBERTY-313
30	01-Jan-21	3710	B-LIBERTY-313
31	01-Jan-21	3710	B-LIBERTY-313
32	01-Jan-21	3711	MAC PHOENIX
33	01-Jan-21	3711	MAC PHOENIX
34	01-Jan-21	3712	ADNOC-812
35	01-Jan-21	3713	A-HERCULES
36	01-Jan-21	3713	A-HERCULES
37	01-Jan-21	3713	A-HERCULES
38	01-Jan-21	3713	A-HERCULES
39	01-Jan-21	3713	A-HERCULES
40	01-Jan-21	3713	A-HERCULES
Sheet1

Cell Formulas
Range	Formula
G3	G3	=IF(C3="","",INDEX(B3:B40,MATCH(ROWS(G$3:G3),C3:C40,0)))

 

[jasonb75]

This would be much easier if you had the dynamic array functions at your disposal.

One alternative to the more complex formulas needed for your version of excel would be to use a pivot table instead. This was created from the data in columns A:C with a few clicks. The date can be selected from the dropdown filter located in F1

Book1 (version 1).xlsx
	E	F	G
1	Date	01 Jan 21
2
3	Name	Voyage No	Count of Voyage No
4	ADNOC-224	3706	5
5	ADNOC-810	3705	4
6	ADNOC-812	3712	1
7	A-HERCULES	3713	6
8	ASL SWIFT	3666-O	3
9	B-LIBERTY-313	3710	5
10	MAC PHOENIX	3711	2
11	SMIT LUMUT	3709	4
12	SMIT LUZON	3708	3
13	Z-POWER	3707	5
Sheet6

 

[Rahul1987]

Hi LUKMA,

How about?

Book2
	A	B	C	D	E	F	G	H
1				Month	1-Jan-21	Extract Unique Voyage Base 2 Criteria Name and Month		Unique count
2	Date	Voyage No	Name	maximum Trip in a Month 10 to 11 for each	Name	Voyage No		10
3	1-Jan-21	3705	ADNOC-810	1	ADNOC-810	3705
4	1-Jan-21	3705	ADNOC-810	2	ADNOC-810	3706
5	1-Jan-21	3705	ADNOC-810	3	ADNOC-810	3666-O
6	1-Jan-21	3705	ADNOC-810	4	ADNOC-810	3707
7	1-Jan-21	3706	ADNOC-224	5	ADNOC-810	3708
8	1-Jan-21	3706	ADNOC-224	6	ADNOC-810	3709
9	1-Jan-21	3706	ADNOC-224	7	ADNOC-810	3710
10	1-Jan-21	3706	ADNOC-224	8	ADNOC-810	3711
11	1-Jan-21	3706	ADNOC-224	9	ADNOC-810	3712
12	1-Jan-21	3666-O	ASL SWIFT	10	ADNOC-810	3713
13	1-Jan-21	3666-O	ASL SWIFT	1	ADNOC-224
14	1-Jan-21	3666-O	ASL SWIFT	2	ADNOC-224
15	1-Jan-21	3707	Z-POWER	3	ADNOC-224
16	1-Jan-21	3707	Z-POWER	4	ADNOC-224
17	1-Jan-21	3707	Z-POWER	5	ADNOC-224
18	1-Jan-21	3707	Z-POWER	6	ADNOC-224
19	1-Jan-21	3707	Z-POWER	7	ADNOC-224
20	1-Jan-21	3708	SMIT LUZON	8	ADNOC-224
21	1-Jan-21	3708	SMIT LUZON	9	ADNOC-224
22	1-Jan-21	3708	SMIT LUZON	10	ADNOC-224
23	1-Jan-21	3709	SMIT LUMUT
24	1-Jan-21	3709	SMIT LUMUT
25	1-Jan-21	3709	SMIT LUMUT
26	1-Jan-21	3709	SMIT LUMUT
27	1-Jan-21	3710	B-LIBERTY-313
28	1-Jan-21	3710	B-LIBERTY-313
29	1-Jan-21	3710	B-LIBERTY-313
30	1-Jan-21	3710	B-LIBERTY-313
31	1-Jan-21	3710	B-LIBERTY-313
32	1-Jan-21	3711	MAC PHOENIX
33	1-Jan-21	3711	MAC PHOENIX
34	1-Jan-21	3712	ADNOC-812
35	1-Jan-21	3713	A-HERCULES
36	1-Jan-21	3713	A-HERCULES
37	1-Jan-21	3713	A-HERCULES
38	1-Jan-21	3713	A-HERCULES
39	1-Jan-21	3713	A-HERCULES
40	1-Jan-21	3713	A-HERCULES
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(IF(FREQUENCY(IF($B$3:$B$40<>"",IF($A$3:$A$40=$E$1,MATCH("~"&$B$3:$B$40,$B$3:$B$40&"",0))),ROW($B$3:$B$40)-ROW($B$3)+1),1))
F3:F12	F3	=IF(ROWS(F$3:F3)>$H$2,"",INDEX($B$3:$B$40,SMALL(IF(FREQUENCY(IF($B$3:$B$40<>"",IF($A$3:$A$40=$E$1,MATCH("~"&$B$3:$B$40,$B$3:$B$40&"",0))),ROW($B$3:$B$40)-ROW($B$3)+1),ROW($B$3:$B$40)-ROW($B$3)+1),ROWS(F$3:F3))))
F13:F40	F13	=IF(ROWS(F$3:F13)>$H$2,"",INDEX($B$3:$B$40,SMALL(IF(FREQUENCY(IF($B$3:$B$40<>"",MATCH("~"&$B$3:$B$40,$B$3:$B$40&"",0)),ROW($B$3:$B$40)-ROW($B$3)+1),ROW($B$3:$B$40)-ROW($B$3)+1),ROWS(F$3:F13))))
Press CTRL+SHIFT+ENTER to enter array formulas.