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

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
118
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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)), "")]
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
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.
 

Rahul1987

Board Regular
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJK
1IDDateSaleshelperMAX5SrIDDateSales
21018-Nov-18115111018-Nov-18115
31424-Nov-1436221424-Nov-1436
412519-Nov-211123312519-Nov-21112
51619-Nov-20126441619-Nov-20126
61323-Nov-10112551323-Nov-10112
71424-Nov-14365
81018-Nov-181155
Sheet1
Cell Formulas
RangeFormula
G1G1=MAX(D2:D8)
I2:K6I2=INDEX(A$2:A$8,MATCH($H2,$D$2:$D$8,0))
D2:D8D2=SUM(D1,SUMPRODUCT(--($A$2:A2&"|"&$B$2:B2&"|"&$C$2:C2=A2&"|"&B2&"|"&C2))=1)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1IDDateSaleshelperSrIDDateSales
21043422115221043422115
314419673633144196736
4125445191124412544519112
51644154126551644154126
61340505112661340505112
7144196736     
81043422115 
Sheet8
Cell Formulas
RangeFormula
F2:F7F2=IFERROR(SMALL(D:D,ROWS(F$2:F2)),"")
G2:I7G2=IF($F2="","",INDEX(A:A,$F2))
D2:D8D2=IF(COUNTIFS(A$1:A1,A2,B$1:B1,B2,C$1:C1,C2),"",ROW(D2))
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
118
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
PQRSTUVWX
1MonthVoyage NameVisited As per Voyage01-Jan-21Assuming Max Trip in Month for each is 4NameMonth Unique Trip Voayge No.
201-Jan-21Jan-213705ADNOC-810USSCTrip 1 VoyageADNOC-8103705
301-Jan-21Jan-213705ADNOC-810Jopetwill-300Trip 2 VoyageADNOC-8103710
401-Jan-21Jan-213705ADNOC-810USSCTrip 3 VoyageADNOC-810
501-Jan-21Jan-213705ADNOC-810USSCTrip 4 VoyageADNOC-810
601-Jan-21Dec-203706ADNOC-224Al Ghallan Island Trip 1 VoyageADNOC-2243706
701-Jan-21Dec-203706ADNOC-224Asseifiya Island Trip 2 VoyageADNOC-2243711
801-Jan-21Dec-203706ADNOC-224Ettouk Island Trip 3 VoyageADNOC-224
901-Jan-21Dec-203706ADNOC-224Umm Al AnbarTrip 4 VoyageADNOC-224
1001-Jan-21Dec-203706ADNOC-224Deep Driller-3Trip 1 VoyageZ-POWER
1101-Dec-20Jan-213666-OASL SWIFTRig Artabhatt-1Trip 2 VoyageZ-POWER
1201-Dec-20Jan-213666-OASL SWIFTRig Al BzoomTrip 3 VoyageZ-POWER
1301-Dec-20Jan-213666-OASL SWIFTRig Artabhatt-1Trip 4 VoyageZ-POWER
1401-Jan-21Jan-213707Z-POWERRig Artabhatt-1Trip 1 VoyageASL SWIFT
1501-Jan-21Jan-213707Z-POWERBarge Pride Trip 2 VoyageASL SWIFT
1601-Jan-21Jan-213707Z-POWERRig DiyinaTrip 3 VoyageASL SWIFT
1701-Jan-21Jan-213707Z-POWERDeep Driller-3Trip 4 VoyageASL SWIFT
1801-Jan-21Jan-213707Z-POWERDeep Driller-3Trip 1 VoyageSMIT LUZON
1901-Jan-21Jan-213708SMIT LUZONRig HudairiyatTrip 2 VoyageSMIT LUZON
2001-Jan-21Jan-213708SMIT LUZONRig HudairiyatTrip 3 VoyageSMIT LUZON
2101-Jan-21Jan-213708SMIT LUZONBarge ShamalTrip 4 VoyageSMIT LUZON
2201-Jan-21Jan-213709SMIT LUMUTRig Al Lulu
2301-Jan-21Jan-213710ADNOC-810USSC
2401-Jan-21Jan-213710ADNOC-810Jopetwill-300
2501-Jan-21Jan-213710ADNOC-810USSC
2601-Jan-21Jan-213710ADNOC-810USSC
2701-Jan-21Dec-203711ADNOC-224Al Ghallan Island
2801-Jan-21Dec-203711ADNOC-224Asseifiya Island
2901-Jan-21Dec-203711ADNOC-224Ettouk Island
3001-Jan-21Dec-203711ADNOC-224Umm Al Anbar
3101-Jan-21Dec-203711ADNOC-224Deep Driller-3
3201-Dec-20Jan-213712ASL SWIFTRig Artabhatt-1
3301-Dec-20Jan-213712ASL SWIFTRig Al Bzoom
3401-Dec-20Jan-213712ASL SWIFTRig Artabhatt-1
3501-Jan-21Jan-213713Z-POWERRig Artabhatt-1
3601-Jan-21Jan-213713Z-POWERBarge Pride
3701-Jan-21Jan-213713Z-POWERRig Diyina
3801-Jan-21Jan-213713Z-POWERDeep Driller-3
3901-Jan-21Jan-213713Z-POWERDeep Driller-3
4001-Jan-21Jan-213714SMIT LUZONRig Hudairiyat
4101-Jan-21Jan-213714SMIT LUZONRig Hudairiyat
4201-Jan-21Jan-213714SMIT LUZONBarge Shamal
4301-Jan-21Jan-213714SMIT LUMUTRig Al Lulu
Sheet2
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
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
GH
1NameMonth Unique Trip Voayge No.
2ADNOC-2243706
3ADNOC-2243711
4ADNOC-8103705
5ADNOC-8103710
6SMIT LUMUT3709
7SMIT LUMUT3714
8SMIT LUZON3708
9SMIT LUZON3714
10Z-POWER3707
11Z-POWER3713
Sheet9
Cell Formulas
RangeFormula
G2:H11G2=SORT(SORT(UNIQUE(FILTER(C2:D43,A2:A43=F1,"")),2,1),1,-1,1)
Dynamic array formulas.
 

Rahul1987

Board Regular
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
Hahaha...never listen to others. Why did we work so hard??? lol
Anyways glad that you got your solution @ LUKMA
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
118
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

 

Forum statistics

Threads
1,143,688
Messages
5,720,305
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top