Excel Formula Help, how to extract unique value from a duplicate list based on Multiple criteria without Ctrl Shift Enter

[Lukma]

Hi Guy

Just a Quick One am also having this formula in Column X with ctrl Shift Enter and i really don't want to use the Array Formula is there other formula to extract the unique based on Criteria am having 2016 excel

In column B i have Month and in column i Have the Repeated Value for each name and in D i have the Name listed and in column V i created permanent max 11 entry for each names and in Column X i have my formula to extract the unique value based on The Criteria for every month.. so is there any other option without using Ctrl Shift Enter.

[=IFERROR(INDEX($C$18:$C$60000, MATCH(0, COUNTIF(X$17:$X17, $C$18:$C$60000)+IF($D$18:$D$60000<>V18, 1, 0)+IF($B$18:$B$60000<>$B$9, 1, 0), 0)), "")]

 

[jasonb75]

You can circumvent Ctrl Shift Enter by using alternative methods, although this doesn't work with everything. Also, it would still be an array formula and quite possibly less efficient due to having additional functions involved.

You might be able to make it more efficient by adding a helper column to the source data, but that is about the only practical option with 2016.

 

[Lukma]

Thanks

 

[Rahul1987]

Hi Guy

Just a Quick One am also having this formula in Column X with ctrl Shift Enter and i really don't want to use the Array Formula is there other formula to extract the unique based on Criteria am having 2016 excel

In column B i have Month and in column i Have the Repeated Value for each name and in D i have the Name listed and in column V i created permanent max 11 entry for each names and in Column X i have my formula to extract the unique value based on The Criteria for every month.. so is there any other option without using Ctrl Shift Enter.

[=IFERROR(INDEX($C$18:$C$60000, MATCH(0, COUNTIF(X$17:$X17, $C$18:$C$60000)+IF($D$18:$D$60000<>V18, 1, 0)+IF($B$18:$B$60000<>$B$9, 1, 0), 0)), "")]

Is it possible for you to paste your sample data via xl2BB method for the better understanding?

 

[Rahul1987]

I did not understand your data , however have created my own data to explain you as in how you can achieve your goal without an array

Hope this helps you.

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Date	Sales	helper		MAX	5	Sr	ID	Date	Sales
2	10	18-Nov-18	115	1				1	10	18-Nov-18	115
3	14	24-Nov-14	36	2				2	14	24-Nov-14	36
4	125	19-Nov-21	112	3				3	125	19-Nov-21	112
5	16	19-Nov-20	126	4				4	16	19-Nov-20	126
6	13	23-Nov-10	112	5				5	13	23-Nov-10	112
7	14	24-Nov-14	36	5
8	10	18-Nov-18	115	5
Sheet1

Cell Formulas
Range	Formula
G1	G1	=MAX(D2:D8)
I2:K6	I2	=INDEX(A$2:A$8,MATCH($H2,$D$2:$D$8,0))
D2:D8	D2	=SUM(D1,SUMPRODUCT(--($A$2:A2&"|"&$B$2:B2&"|"&$C$2:C2=A2&"|"&B2&"|"&C2))=1)

 

[jasonb75]

you can achieve your goal without an array

Your formula in column D is an array that circumvents Ctrl Shift Enter.

The closest you will get without an array will be something like this.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	ID	Date	Sales	helper		Sr	ID	Date	Sales
2	10	43422	115	2		2	10	43422	115
3	14	41967	36	3		3	14	41967	36
4	125	44519	112	4		4	125	44519	112
5	16	44154	126	5		5	16	44154	126
6	13	40505	112	6		6	13	40505	112
7	14	41967	36
8	10	43422	115
Sheet8

Cell Formulas
Range	Formula
F2:F7	F2	=IFERROR(SMALL(D:D,ROWS(F$2:F2)),"")
G2:I7	G2	=IF($F2="","",INDEX(A:A,$F2))
D2:D8	D2	=IF(COUNTIFS(A$1:A1,A2,B$1:B1,B2,C$1:C1,C2),"",ROW(D2))

 

[Lukma]

Jasonb75

Thanks for much interest on my subject however if i can explain in simple details, is to extract the unique Trips voyage monthly based on criteria Month and Name

Without stressing i created and assume that the maximum Trip for each name in a month is 4 trip with different Voyage Number. which i have created a fix name in column W2
Then column X i need to extract the Unique Voyage number without the use of array ctrl Shift Enter base on the 2 criteria ( Month and Name to extract the unique voyage monthly

in column X2 i need same to be extracted and when i pull formula down it will give me for each unique voyage made for each name for every month whenever i change the month

ILSP KPI Performance Tracking.xlsx
	P	Q	R	S	T	U	V	W	X
1	Month		Voyage	Name	Visited As per Voyage	01-Jan-21	Assuming Max Trip in Month for each is 4	Name	Month Unique Trip Voayge No.
2	01-Jan-21	Jan-21	3705	ADNOC-810	USSC		Trip 1 Voyage	ADNOC-810	3705
3	01-Jan-21	Jan-21	3705	ADNOC-810	Jopetwill-300		Trip 2 Voyage	ADNOC-810	3710
4	01-Jan-21	Jan-21	3705	ADNOC-810	USSC		Trip 3 Voyage	ADNOC-810
5	01-Jan-21	Jan-21	3705	ADNOC-810	USSC		Trip 4 Voyage	ADNOC-810
6	01-Jan-21	Dec-20	3706	ADNOC-224	Al Ghallan Island		Trip 1 Voyage	ADNOC-224	3706
7	01-Jan-21	Dec-20	3706	ADNOC-224	Asseifiya Island		Trip 2 Voyage	ADNOC-224	3711
8	01-Jan-21	Dec-20	3706	ADNOC-224	Ettouk Island		Trip 3 Voyage	ADNOC-224
9	01-Jan-21	Dec-20	3706	ADNOC-224	Umm Al Anbar		Trip 4 Voyage	ADNOC-224
10	01-Jan-21	Dec-20	3706	ADNOC-224	Deep Driller-3		Trip 1 Voyage	Z-POWER
11	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Rig Artabhatt-1		Trip 2 Voyage	Z-POWER
12	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Rig Al Bzoom		Trip 3 Voyage	Z-POWER
13	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Rig Artabhatt-1		Trip 4 Voyage	Z-POWER
14	01-Jan-21	Jan-21	3707	Z-POWER	Rig Artabhatt-1		Trip 1 Voyage	ASL SWIFT
15	01-Jan-21	Jan-21	3707	Z-POWER	Barge Pride		Trip 2 Voyage	ASL SWIFT
16	01-Jan-21	Jan-21	3707	Z-POWER	Rig Diyina		Trip 3 Voyage	ASL SWIFT
17	01-Jan-21	Jan-21	3707	Z-POWER	Deep Driller-3		Trip 4 Voyage	ASL SWIFT
18	01-Jan-21	Jan-21	3707	Z-POWER	Deep Driller-3		Trip 1 Voyage	SMIT LUZON
19	01-Jan-21	Jan-21	3708	SMIT LUZON	Rig Hudairiyat		Trip 2 Voyage	SMIT LUZON
20	01-Jan-21	Jan-21	3708	SMIT LUZON	Rig Hudairiyat		Trip 3 Voyage	SMIT LUZON
21	01-Jan-21	Jan-21	3708	SMIT LUZON	Barge Shamal		Trip 4 Voyage	SMIT LUZON
22	01-Jan-21	Jan-21	3709	SMIT LUMUT	Rig Al Lulu
23	01-Jan-21	Jan-21	3710	ADNOC-810	USSC
24	01-Jan-21	Jan-21	3710	ADNOC-810	Jopetwill-300
25	01-Jan-21	Jan-21	3710	ADNOC-810	USSC
26	01-Jan-21	Jan-21	3710	ADNOC-810	USSC
27	01-Jan-21	Dec-20	3711	ADNOC-224	Al Ghallan Island
28	01-Jan-21	Dec-20	3711	ADNOC-224	Asseifiya Island
29	01-Jan-21	Dec-20	3711	ADNOC-224	Ettouk Island
30	01-Jan-21	Dec-20	3711	ADNOC-224	Umm Al Anbar
31	01-Jan-21	Dec-20	3711	ADNOC-224	Deep Driller-3
32	01-Dec-20	Jan-21	3712	ASL SWIFT	Rig Artabhatt-1
33	01-Dec-20	Jan-21	3712	ASL SWIFT	Rig Al Bzoom
34	01-Dec-20	Jan-21	3712	ASL SWIFT	Rig Artabhatt-1
35	01-Jan-21	Jan-21	3713	Z-POWER	Rig Artabhatt-1
36	01-Jan-21	Jan-21	3713	Z-POWER	Barge Pride
37	01-Jan-21	Jan-21	3713	Z-POWER	Rig Diyina
38	01-Jan-21	Jan-21	3713	Z-POWER	Deep Driller-3
39	01-Jan-21	Jan-21	3713	Z-POWER	Deep Driller-3
40	01-Jan-21	Jan-21	3714	SMIT LUZON	Rig Hudairiyat
41	01-Jan-21	Jan-21	3714	SMIT LUZON	Rig Hudairiyat
42	01-Jan-21	Jan-21	3714	SMIT LUZON	Barge Shamal
43	01-Jan-21	Jan-21	3714	SMIT LUMUT	Rig Al Lulu
Sheet2

 

[jasonb75]

I thought that you were satisfied with the pivot table suggestion when you marked it as a solution in your other thread.

In short. Trying to do what you want without array formulas is near impossible with your versions of excel. As I already said, you can short circuit the formulas with other functions to get around the need for using Ctrl Shift Enter, but they are still array formulas.

The helper column methods that we have suggested might work (I haven't tried them with your actual data yet). One thing for certain, you will need to use 1 or more of the following in order to get the results that you want.

A CSE array formula.
An array formula that uses extra functions to circumvent CSE.
Helper columns.
A pivot table.
Upgrade to office 365.
A vba alternative.

This example is done using office 365 functions (which you will not have in your 2016 / 2019 versions). IT does not require CSE, however it is still an array formula. It is also most likely the most efficient way of doing what you want.

Book1 (version 1).xlsb
	G	H
1	Name	Month Unique Trip Voayge No.
2	ADNOC-224	3706
3	ADNOC-224	3711
4	ADNOC-810	3705
5	ADNOC-810	3710
6	SMIT LUMUT	3709
7	SMIT LUMUT	3714
8	SMIT LUZON	3708
9	SMIT LUZON	3714
10	Z-POWER	3707
11	Z-POWER	3713
Sheet9

Cell Formulas
Range	Formula
G2:H11	G2	=SORT(SORT(UNIQUE(FILTER(C2:D43,A2:A43=F1,"")),2,1),1,-1,1)
Dynamic array formulas.

 

[Rahul1987]

Hahaha...never listen to others. Why did we work so hard??? lol
Anyways glad that you got your solution @ LUKMA

 

[Lukma]

Hi, Rahul / Jasonb75

Yes i was ok with it, and i do really appreciate all effort, which you guys have been so helpful since day i join the forum, my work as been so easy every day, however i was ok with the pivot table, But due to my range of data keep expanding am unable to find right way to get it Auto update and also since the forum allow just few XL2BB for sample and unable to find a way to upload my data file in full

However Jasonb75 my excel is yet updated to office 365 at present by difficulties is that am using excel 2016 which does not include Unique function and that my only trouble all suggestion from forum are all great but i was hoping that i could get a solution ( formula ) that could run faster Without the use of Ctrl Shift Enter.

Hence am not having the unique function is there any better option for this

once again i really Appreciate all effort

Regards

​