Formula to Extract Unique Value base Multiple Criteria

[Lukma]

Hi Friend

I need help with this formula i have a range of Data containing name of vessel in Column i 5 and Voyage on H 5 and month in G 5 and i have created Month in B 3 to search for each month i also list all vessel fix in B 5
my issue now is that i have entered my formula D 5 to extract using Ctr Shift Enter but still now giving right extraction and also i need a formula that do not require Ctr Shift Enter if there is Option which will not allow my work to get slow to open

Please can anyone help with formula that can run faster to extract the voyage based on criteria appreciate your assistance

Book2
	B	C	D	E	F	G	H	I
3	01-Jan-21
4	Supply Vessel's	P&D VSL	Voayge Mus-No			Month	Mus-No	Supply Vessel
5	ADNOC-1010	Half	3705			01-Jan-21	Mus-3705	ADNOC-1010
6	ADNOC-1010	Full	3713			01-Jan-21	Mus-3706	ADNOC-224
7	ADNOC-1010	Half	3713			01-Jan-21	3666-O	ASL SWIFT
8	ADNOC-1010	Full	3713			01-Jan-21	Mus-3707	Z-POWER
9	ADNOC-1010	Half	3713			01-Jan-21	Mus-3708	SMIT LUZON
10	ADNOC-1010	Half	3713			01-Jan-21	Mus-3709	SMIT LUMUT
11	ADNOC-1010		3713			01-Jan-21	Mus-3710	B-LIBERTY-313
12	ADNOC-1010		3713			01-Jan-21	Mus-3711	ADNOC-1011
13	ADNOC-1010		3713			01-Jan-21	Mus-3712	ADNOC-812
14	ADNOC-1010		3713			01-Jan-21	Mus-3713	ADNOC-1010
15	ADNOC-1010		4			01-Jan-21	Mus-001	LCT-TARFFAH
16	ADNOC-1011		7			01-Jan-21	Mus-002	ADNOC-850
17	ADNOC-1011		7			01-Jan-21	Mus-003	ADNOC-510
18	ADNOC-1011		7			01-Jan-21	Mus-004	ADNOC-1010
19	ADNOC-1011		7			01-Jan-21	Mus-005	MARCAP-2
20	ADNOC-1011		7			01-Jan-21	Mus-006	QMS DELTA
21	ADNOC-1011		7			01-Jan-21	Mus-007	ADNOC-1011
22	ADNOC-1011		16			01-Jan-21	Mus-008	QMS NEPTUNE
23	ADNOC-1011		16			01-Jan-21	Mus-009	ADNOC-221
24	ADNOC-1011		16			01-Jan-21	Mus-010	SEACOR ALPS
25	ADNOC-1011		16			01-Jan-21	Mus-011	ADNOC-229
26	ADNOC-1011		16			01-Jan-21	Mus-012	LCT-MARWAH-1
27	ADNOC-221					01-Jan-21	Mus-013	Z-OCEAN
28	ADNOC-221					01-Jan-21	Mus-014	ADNOC-1010
29	ADNOC-221					01-Jan-21	Mus-015	A-LIBERTY
30	ADNOC-221					01-Jan-21	Mus-016	ADNOC-1011
31	ADNOC-221					01-Jan-21	Mus-017	ADNOC-225
32	ADNOC-221					01-Jan-21	Mus-018	A-CHLOE
33	ADNOC-221					01-Jan-21	Mus-019	ADNOC-811
34	ADNOC-221					01-Jan-21	Mus-020	SWISSCO RUBY
35	ADNOC-221					01-Jan-21	Mus-021	ADNOC-512
36	ADNOC-221					01-Jan-21	Mus-022	AMS-RUBY
37	ADNOC-221					01-Jan-21	Mus-023	ADNOC-511
Sheet1

Cell Formulas
Range	Formula
D5:D33	D5	=IFERROR(INDEX($H$5:$H$37,MATCH(0,IF($B5=$I$5:$I$37,IF($B$3=$G$5:$G$37,COUNTIF($H$4:$H4,$H$5:$H$37), "")), 0)),"")
D34:D37	D34	=IFERROR(INDEX($DF$14:$DF$60401,MATCH(0,IF($X34=$DG$14:$DG$60401,IF($B$5=$DE$14:$DE$60401,COUNTIF($Z$13:$Z33,$DF$14:$DF$60401), "")), 0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alansidman]

With Power Query, create a parameter query and filter for the date you wish. Insert a row in your worksheet at A1 so that your lookup date is in A2.
The Mcode for the parameter is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    crit=Record.Field(#"Changed Type"{0},"Column1")
in
    crit

close and load this to a connection only.

Your code for then filtering the data is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Mus-No", type any}, {"Supply Vessel", type text}, {"Column1", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Month] = Mnth)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Supply Vessel"}, {{"Data", each _, type table [Month=nullable date, #"Mus-No"=any, Supply Vessel=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Mus-No"}, {"Data.Mus-No"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"Supply Vessel", Order.Ascending}})
in
    #"Sorted Rows"

 

[alz]

Hi Try This ! But It need Ctrl+shift +enter

{=IFERROR(SUBSTITUTE(INDEX($H$5:$H$37,AGGREGATE(15,6,IF(($I$5:$I$37=B5)*($G$5:$G$37=$B$3),ROW($G$5:$G$37)-ROW($G$4)),COUNTIF($B$5:B5,B5))),"Mus-",""),"")}

 

[Lukma]

Hi alz / Alansidman

But ways work just perfect and my worksheet open much better i really appreciate both side been so helpful

Regards​