Distinct Values in Drop Down List in Google Sheets

Use Data Validation to Create Unique / Distinct / Drop-Down Lists · Go to cell A1 and then go to the menu Data > Data Validation. It'll open the Data Validation ... GoogleSheets Functions Charts Sheetsvs.Excel Docs Search InfoInspired GoogleSheets Functions Charts Sheetsvs.Excel Docs HomeGoogleDocsSpreadsheetDistinctValuesinDrop-DownListinGoogleSheets Inthistutorial,Iamgoingtoelaborateonthestepstogetonlydistinctvaluesinadrop-downlistinGoogleSheets. Inotherwords,removingausediteminonedrop-downiftheitemisalreadyselectedinanotherdrop-down. Bydoingso,youcanyourselforallowausertoonlyselectonevalueatonetimeinthedrop-downselection.Thisavoidsduplicatesinthedrop-downselection. Quiteconfusingright? First,seethebelowimageandthenreadtheparathatfollows. RemovingUsedItemsfromDataValidationDrop-DownListsinGoogleSheets Intheabovescreenshot,columnAcontainsdrop-downmenusthatpopulatevaluesfromColumnF.Asyoucansee, I’vealreadyselectedafewitemsinthedrop-downlistsinrowsA1toA8. WhenIclickthedrop-downmenuinthecellinA9,itonlyshowsthevaluesthatarelefttoselect.Soitavoidsduplicateselection.Ihopeyoucouldunderstandtheconcept. Thatmeans,thedatavalidationdrop-downlistsavoidselectingoneitemmultipletimes. Onceyou’veselectedanitem,thatitemdisappearsfromtheselectioninthedrop-downmenuinthenextroworcolumns. This’scalleddistinctvaluesinadrop-downlistinGoogleSheetsorremovingtheuseditemsfromdrop-downlists. Thenhowtogetdistinctvaluesinadrop-downlistinGoogleSheetsasabove?Ihaveexplainedthisindetailbelow. HowtoGetDistinctValuesinDrop-DownListinGoogleSheets ThoughcolumnFcontainsthedatatocreatethedrop-downlist,wearenotdirectlyusingit.Instead,weareusingthehelpercolumnC. ThiscolumnautomaticallypopulatesdistinctvaluesbasedonyourmenuselectionincolumnA. Nowseethefollowingimage.Inthat,asyoucansee,I’vedeletedallthedrop-downselectionsincolumnA. NowthevaluesinthehelpercolumnCandcolumnFarethesame. ThatmeansifyouselectanyfruitnameincolumnA,inanycell’sdrop-down,thatfruit’snamegetsremovedfromcolumnC. I’veusedcolumnC,notcolumnFtocreatethedrop-downlist.Thesaidcolumnhelpstoremovetheuseditemsthusgetdistinctvaluesinthedrop-down. HerearethestepstocreatethehelpercolumnCtogetdistinctvaluesinthedrop-downlistinGoogleSheets. ThewholesecretliesinthehelpercolumnC.IncolumnC,I’veappliedoneformulatogeneratedistinctvalues. InGoogleSheets,thepossiblysimplestwaytogetdistinctvaluesfromalistisbyusingtheQueryfunction. Herearethosesteps. GenerateDistinctValuesinGoogleSheets Pickacolumnandenterthevalues(herefruitnames)foryourdrop-downmenutopopulate.HereIamchoosingcolumnF.IncolumnF,I’veentereddifferentfruitnames. Thenextstepistopickahelpercolumn.Here,inmyexample,(pleaserefertothescreenshotsabove)I’vechosencolumnCasmyhelpercolumn. Ifyouchooseanycolumnsotherthantheonesusedbyme,pleasechangethecellreferencesinthefollowingformulaaccordingly. IncellC1,enterthebelowformulatogeneratedistinctvalues. =query( query( {F1:F;A1:A}, "SelectCol1,count(Col1)whereCol1<>''groupbyCol1" ), "SelectCol1whereCol2=1" ) HereisanalternativeformulatouseincellC1basedonCOUNTIF.IamusingtheaboveQuerythough. =ArrayFormula( if(countif(A1:A,F1:F)=0,F1:F, ) ) AnotherpopularfunctiontoreplaceQuerytopopulatedistinctvaluesistheFilterfunction.IncellC1,thisformulawillalsofitperfectly.Thechoiceisuptoyou. =filter(F1:F,NOT(regexmatch(F1:F,"^"&textjoin("$|^",1,A:A)&"$"))) Here,onceagain,pleasetakenoteofthecellreferencesinuse.DataRangeF1:Fcontainsthefruitnames.Actually,it’sthemasterlistforourdrop-downmenu. A1:AistherangewhereIamgoingtocreatedrop-downmenususingDataValidation.I’llcometothatlater. SomeofyoumaywanttoknowmoreaboutgeneratingadistinctlistinGoogleSheetsasaboveinColumnC.Imean,youmaybeexpectingaformulaexplanation. HereIamnotgoingintodetailabouthowtoreadthisformulaasyoucanusethisoutofthebox.Butdon’tgetdisappointed.I’vealreadyasimilartutorialforyou– HowtoFindDistinctRowsinGoogleSheetsUsingQuery. Havingsaidthatletmetakeyoutothenextstep.WehavegenerateddistinctvaluesinhelpercolumnC.Nowit’stimetocreatethedrop-downmenusincolumnAusingtheDataValidation. UseDataValidationtoCreateUnique/Distinct/Drop-DownLists WhenIsaygetdistinctvaluesinadrop-downlistinGoogleSheets,Isimplymeantosayavoidduplicatesinthedrop-downmenu/list.Youcanalsosay,getonlyuniquevaluesinadrop-downmenuinGoogleSheets. I’vealreadygenerateddistinct/uniquevaluesincolumnC.Nowwejustneedtoconnectthisintothedrop-downsincolumnA.Herearethosesteps. Steps: SeethestepstousedatavalidationinGoogleSheetstogenerateadrop-downlist. GotocellA1andthengotothemenuData>DataValidation.It’llopentheDataValidationsettingswindow.Theresetthedatavalidationsettingsasperthebelowimage.Takespecialcareofthecellreferencesused.Thenclickonthe“Save”button.AfterthatjustcopythecellA1drop-downtocellA2,A3,A4… It’saspermydatarange.Youmaywanttouseadifferentrangethatdependingonyourdata. Wehavecompletedallthestepsinvolvedingeneratingadrop-downmenuthatonlycontainsdistinctvalues. RemovingUsedItemsfromDrop-DownThatSharetheSameListFromRange Asperthebelowscreenshot,theactivedrop-dowlistisincellA14.I’vealreadyselecteddifferentfruitsfromthedatavalidationdrop-downlistsintherangeA1:A13. SoincellA14,thedrop-downlistonlyshowstheavailablefruittoselect.Thisensuresthatausercanonlyselectunique/distinctvaluesinthedrop-down. Inotherwords,thismethodensuresuseditemsarenomoreavailabletoselectinthedrop-downs. Youmayseeawarningineverycellwhereyou’vemadetheselectionofitems.Seetheredmarkonthecornerofthecells. Whenyouhoveryourmousepointeroverthat, youwillseethemessagesaying“Inputmustfallwithinspecifiedrange”.Nothingtoworryabout! Youcanjustignorethatwarning. That’sall.Enjoy! ExampleSheet81121 Resources DataValidation–HowNottoAllowDuplicatesinGoogleSheets.TheBestDataValidationExamplesinGoogleSheets.ProperWaytoUseCurrencyFormattinginDataValidationDrop-DowninGoogleSheets.ADrop-downMenuinGoogleSheetstoViewContentfromAnySheets.GoogleSheets:HowtoGetanAllSelectionOptioninaDrop-down.PopulateaFullMonth’sDatesBasedonaDrop-downinGoogleSheets.CreateaDrop-DowntoFilterDataFromRowsandColumns.CreateaDrop-DownMenuFromMultipleRangesinGoogleSheets.HowtoCombineMultipleSheetsinImportrangeandControlViaDrop-Down.Multi-RowDynamicDependentDropDownListinGoogleSheets.Auto-PopulateInformationBasedonDropdownSelectioninGoogleSheets.RejectaListofItemsinDataValidationinGoogleSheets. RELATEDARTICLESMOREFROMAUTHOR Spreadsheet HowtoUsetheBAHTTEXTFunctioninGoogleSheets Spreadsheet HowtoHighlightanEntireColumninGoogleSheets Spreadsheet HowtoUsetheVARPAFunctioninGoogleSheets 41COMMENTS Idontknowhowtogetthistoworkwithnumbersinsteadofwords(fruit) Reply Hi,SeanMcNamara, ReplacetheQueryformulaasbelow. =query(query({F1:F;A1:A},"SelectCol1,count(Col1)whereCol1isnotnullgroupbyCol1"),"SelectCol1whereCol2=1") Thanks. Reply TY Reply Doyouhaveanexamplespreadsheetlinktosharewiththisqueryinaction?Iamcopyingthissetupverbatimbutamunabletogetthequerytooutputthelistofremainingunselecteditems. Reply Hi,billc, LuckilyIdohaveacopyofthatSheetwhichIhaveincludedinthelastpartofthistutorial. Thanks. Reply Hello, Thankyouforyourexplanation. Iwaswonderingifitwaspossibletodothesamewithseveraldropsdowncolumnsinsteadofjustonebigcolumn?Itriedchangingthecellreferenceswiththis: =query(query({F1:F;C1:G1},"SelectCol1,count(Col1)whereCol1''groupbyCol1"),"SelectCol1whereCol2=1") butitdoesnotwork… Thankyouforyourtime. Best Reply Hi, Itwillwork.Buttheformulawon’tbelikethat. Thereisonecontradictioninyourformula.Sinceyourdrop-downmenusareinC1:G1,howtheF1:F(masterlist)cancontainthesource.ItcanbeF2:F,oranyothercolumn. IamusingN1:Nasthesourcelist.Thentheformulawillbelikethis. =query(query({N1:N;transpose(C1:G1)},"SelectCol1,count(Col1)whereCol1<>''groupbyCol1"),"SelectCol1whereCol2=1") IhavethisformulaincellK1.Accordingly,pleasechangethesettingsinthedatavalidationrule. IhavedemonstratedthesameonmySheet(SeetheSheetlinkinoneofmypreviouscommentabove) Best Reply Thankyouforyourhelp! Reply Hi, Thisarticleisdefinitelyhelpingandthanksfortheeffort! HoweverInowencounteracasethatI’mgoingtomake2dropdownlistwherebothoftheirdropdownitemsaresame. Whenthefirstdropdownitemisselected,theseconddropdownlistshouldexcludetheselectediteminthefirstdropdownlisttopreventfromsameitemsselectedtwice. Bothofthedropdownlistitemsarebasedonalistofitemsratherthanalistofrange… Doyouhaveanyideaonthisone? Again,thisarticleisreallyhelpfulforme! Best Reply Hi,Ben, Youarewelcome! Atpresent,thereisnowaytoachievethatusingdatavalidation“Listofitems”,insteadof“Listfromrange”. Best Reply Hithere, ThisformulaisexactlywhatIwaslookingfor. Sadlyitdoesn’tworkformeatall.Italwaysgivesme#ERROR!EvenwhenIcopyyourentiresheet,thatyoulinkedabove,toanewone. Ialsotriedtosetitupfromscratch.Stilldidn’tworkeventhoughIusedthesameexactsamecolumnsasyoudid. WhenIopenthesheetyoulinked,everythingisworkingfine. IsthereanyoptionIhavetoactivateinthenewsheettomakeitwork? Also,inyoursheet,youonlyhavethequeryformulainC1.Howdoestheformulapasstheinformation(leftoverfruits)intoC2-C18? Thanksinadvance. Reply Hi,derSaft, Theerrorisprobablyduetothe‘Locale’settingsofyourSheet. Solution: CopymySheetandgotothemenuFile>Spreadsheetsettings. TherechangetheSheets‘Locale’fromtheUnitedKingdomtoyourcountry.TimeZoneisnotimportant,butyoucanchangethattoo. ThiswillmodifytheexistingQueryformulabasedonthenewLocale(InEUcountries,theformulausesasemicoloninsteadofacomma). NowyoucancopythecontentoftheSheettoanyotherSheetwithouterror. Nowtoyoursecondquestion,thatis; Howdoestheformulapasstheinformation(leftoverfruits)intoC2:C18? IntheQueryformula,theDATAisthevaluesincolumnAandcolumnFcombined. {F1:F;A1:A} IhaveusedtheCountaggregationfunctioninQuerytocountthevaluesinthisvirtualcolumn.Ifthecountreturnsthe#1,suchvaluesaredistinct.Ihavefilteredthatdistinctvalues. Best, Reply HiPrashanth, Thankssomuchforthereply! Itwasindeedtheswitchingfromcommastosemicolons.Noweverythingisworkingfine🙂 Isawthatyoualsoincludedaversionthatletsyouusethescriptfordropdownsindifferentcolumnsinsteadofonlyone. Iwaswonderingifthere’sactuallyafastsolutionwhichcombinesbothversions(multiplecolumns+multiplerows). I’musing8columnsthateachhave5rowsasdropdowns(samesourceforall40dropdowns). IguesssinceyouuseTRANSPOSEwhichjustswitchescolumnsandrows,it’snotthatsimpletocombineboth,right? Doyouknowofanyworkaroundforthisorhaveanyothertutorialsthatmighthelp? Thanks! GreetingsfromGermany Reply Hi,derSaft, It’satoughtasktoaddmultiplecolumns.Idon’tfindanimmediatesolutiontothis. Reply OMG,thankyousomuch!Yoursolutionworkedperfectly! Well,Ineededalittleupdateforusingnumbersinsteadoftextstrings,butnowit’sworkingperfectly! MyHelpercolumnislistingthenumbervaluesinnumericalorder,butmydrop-downlistrestoresmypreviouslyrandomizedorder–whichisexactlywhatIwanted. SoIjustwantedtodropbyandletyouknowhowmuchIappreciateyourarticle!SooftenIfindfixesthatareclunkyworkaroundsorjustdon’twork.Yoursolutionisrocksolid!Thankyou! Justsoyouknow,I’mcreatinganinventorylistforequipmentusedinmycompany.Theuniquedatathatthissolutionhelpswithisregistrationnumbersforeachpiece;sonoduplicatesareallowed. Withhundreds(andlaterthousands)ofpiecesofequipmentinourinventory,youcanseejusthowhelpful/usefulthissolutionis!Thanksagain! Reply Ok,soIhadanerrorinmysheets.Buttheonlyrealchangeoncefixedisthattherangeisnotrandomizedpermypreference.Buteverythingstillworks.WouldIbeabletorandomizetherangeinthegeneratedlistbelowtheformula? Reply Hi,ValerieAnnSavage, IhavetwodifferentformulastouseincellC1(helpercolumn). UsetheCountifformulainsteadofQueryforkeepingtheorderasperyouroriginallistincolumnF. NB:MyQueryformulawon’tworkinanumbercolumn.Itshouldbemodifiedlikethis. =query(query({F1:F;A1:A},"SelectCol1,count(Col1)whereCol1isnotnullgroupbyCol1"),"SelectCol1whereCol2=1") Ifyouwantanyadditionalhelp,feelfreetoshareacopyofyourSheetoranexampleSheetwithoutanypersonal/confidential/sensitivedata. Best, Reply Hello, Iwaswonderingifitispossibletohavethedrop-downmenusonadifferentsheetthantheselectionandhelpermenu?Howwouldyoueditthequeryforthis?Thank Reply Nevermind,Ifiguredoutthesolutionforthis. Reply Iwouldlovetoknowhowyoufixedthis…Ihaveonesheetwherethedrop-downmenu(s)willappear…andanothersheetwheretherangelistsexist(FcolumnandCcolumnliketheexampleabove). Butwhenselectingfromthedrop-down–theselectionswillnotdisappear. Reply Hi,dave2112, Inyourcase,thelisttouseisinPlayers!C1:Candthedrop-downsyouwantinPool!F4:Z12.Soindatavalidation,followthebelowsettings: Cellrange:Pool!F4:Z12 Criteria>Listfromarange:=Players!$C:$C Ihavealreadyappliedtheabovesettingsinyoursharedfile“2021TPCPool–A”. =Players!$C:$C Reply Hello,Iwaswonderingthesame. Howdidyouendupdoingitthen?Itriedusing =query(query({'sheet'!H6:H7;P1:P2},"SelectCol1... nottoavail… Anyhelpappreciated!Thanksinadvance. Reply Hello,Tribune, Iguessyouwanttokeepthedistinctvaluedrop-downmenuinonesheet(Sheet1)andthesupportingdatainanothersheet(Sheet2). Herearethechangesrequired. InSheet2; RangeF1:Fcontainsthelistofitems. TheFormulatouseincellC1inthatsheet. =query(query({F1:F;Sheet1!A1:A},"SelectCol1,count(Col1)whereCol1<>''groupbyCol1"),"SelectCol1whereCol2=1") InSheet1; InDatavalidationselect/enterthecriteriarangeSheet2!C:C. Best, Reply HiThere!!! Maybeyoucanhelpme…. Iappliedtheformulaandisworkingperfect….theproblemis: InmyGoogleSheetsIhave2columns: 1)dateofappointment 2)timeofappointment(dropdownwithoptions–Morning,afternoon,evening). Theideaistheuserselectsadateandthencheckstheavailableslotinthedropdown… IMPORTANT:EverytimeIhaveanewuseritwillbeaddinganewrowinthesheet TheproblemishowcanIremovetheusedoptionsinthedropdowniftheuserchoosesthesamedate? ForExample: 1)UserAchooseJanuary01–morning 2)UserBchoosealsoJanuary01–butnowthedropdownsupposedtoshowonlytheoptions“afternoon”and“evening”. 3)UserCchoosealsoJanuary01–nowthedropdownsupposestoshowonlytheleftoption. ThismeansthateverytimeIhavethesamedateinthecolumn“DATE”,thedropdowninthecolumn“TIME”onlyshowstheleftoptions(optionsnottakenalready). Iwillbeverythankfulforyourhelp! Thanksalot, Gerson. Reply Hi,Gerson, Wecan’tdothatusingaformulaasinGoogleSheetswehavelimitedcontroloverthedrop-down. Reply Nicework!Reallyhelpful,Igiveyoumythanks! Reply Iwouldliketodotheoppositeinadropdown. Ihaveacolumntitled“RequestingUnit”.Itisnotacompletelyfixeddataset. Iknowthatsheetswillsuggestotherentriesintherow,butIwouldlikeforthelisttobecompiledindatavalidationtomakeiteasierinthelongrun…lesstyping. Isthereawaytotakeeachentryinthecolumnandhaveitbeaddedtoadatavalidationlist?i.e.1st“row-RR7J”,2ndrow“RQ7T”,etc.Butaftereachrowisenteredbytyping,itisautomaticallyaddedtoadatavalidationlist.Thanks Reply Hi,tomP, AssumeyourlistisinP2:P10andcreatingdrop-downinA2. IndatavalidationCriteria>Listfromrange,selectP2:PinsteadofP2:P10.Sothatfuturevalueswillbeaddedtothedrop-downlist. Pleaseshareanexamplesheet,ifthisisnotwhatyouwereaksing. Reply Idon’thaveanexamplesheetbecauseIhaven’tbeenabletowork.ButwhatIwastryingtoaccomplishwasnothavingalistinothercells(P2:P10). IwashopingtojustentervaluesintoA2andasmoreinformationisentered,adrop-downlistwouldbebuiltintheAcolumn. Reply Hi,tomP, Usingahelpercolumn,youcanachievethat. IncellP2,keythefollowingFILTERformula(thiscolumnisgoingtobeourhelpercolumn). =ifna(filter(A1:A,A1:A<>""),"Apple") Replace“Apple”intheformulawiththeveryfirstvalue,thatyouaregoingtoenterincellA2. SelectA2:A20(orA2:A)andgotoData>Datavalidation>Criteria>Listfromrange. Inthefieldagainstthe“Listfromrange”,SelectP2:PandSAVE. Note:YoucanhidethehelpercolumnP. Reply Thankssomuch,Prashanth! Reply Thanks,thisworksinacolumn… Ineedtodotwodropdownlistsinsuccessivecolumns..likeAandB. ColumnBhidesselecteditemsincolumnAandthisruleisrepeatedineveryrow. Inotherwords,thesamefulllistshowsinthedropdownlistincolumnAofthesecondrow… Reply Hi,HassanMaateeq, Sorry!I’minthedark.Anexamplesheetmayhelpme. Reply DearPrashanth, Firstofall–tremendousjob,usedyourqueriesalotoftimesbutthistimeIgotstuck(duetolackofabilitiesofcourse;). Couldyoupleasetakealookatthefollowingsheet? …linkremovedbyadmin… Ihavemadeacopyjustforthetest.WhatIneedistoassignweddingguests(columnP)totables.Ineeddrop-downlistsoneveryC3:N12field.Ihavespent3hourstryingtosetitupandnowIamlosingmymind🙂 WhatIneedofcourseistomakeeveryguestnameunique,onceusedinanycolumn,cannotbeusedagain. Couldyoupleaseadvise? BR, Mateusz Reply Hi,MateuszSzkudlarek, Ihavemadeacopyofyourexamplesheetandsolvedtheprobleminthat. Youmaypleasemakeacopyofthesamefromhere. WeddingTableSample Inthatsheet,youcanfindthefollowingformulaincellQ2. =filter(P2:P,regexmatch(P2:P,"^"&textjoin("$|^",true,C3:N12)&"$")=FALSE) Theoutputoftheaboveformulausedforcreatingthedrop-downs. Reply Youareagod!isthereanywaytosupportyousomehow? Reply Hi,MateuszSzkudlarek, Thanksforthekudos! Reply HiPrashanth! Greatexplanation,I’mreallynewtoformulasbutmadeitworkusingthearrayformulaoption.Forme,insteadofhavingaseparatecolumnofdropdowns,Ihaveadropdownabovethereferencedcolumn.Here’swhatIused: =ARRAYFORMULA(IF(COUNTIF(A8:A,J8:J)=0,J8:J,)) A8:A–notsurewhatthisdoes J8:J–containsthedata J4–hasthedatavalidation(usedtobe“listofitems”untilyourformula) ThedatavalidationisinJ4,andIputyourformulainO8.Everythingworkswell,butIalsohaveconditionalformattingforwhenJ8:JmatchesJ4,thebackgroundcolorchangestoyellow.ToworkaroundallthecolumnsturningyellowwhenJ4isempty,Ihadadefaultselected. MynewproblemisthatwhenJ8:JisemptythenJ4isemptyandalltheboxesturnyellow.DoyouknowhowtomakeitsothebackgroundofacellinJ8:JonlyturnsyellowwhensomethingispresentinJ4andtheymatch,butremainswhitewhenbothareblank(andwithouterrors)? Hopethismadesense…wouldappreciateanyhelpIcanget!Alreadythankfulforthearticle. Daniel Reply Hi,Daniel, Iaminthedark.Canyoupleasemakeacopyofyoursheet,replacetheoriginaldatawithsomesample(mockup)data,andsharethatsheetURLinthereplybelow? Reply HowcanImakethisworkacrossmultiplerows? Reply Hi,Adam, I’veaddedanewtabnamed“AcrossRows”inmyexampleSheet. YoucancheckthelastpartofthistutorialforthatSheet’slink. Reply LEAVEAREPLYCancelreply Pleaseenteryourcomment! Pleaseenteryournamehere Youhaveenteredanincorrectemailaddress! Pleaseenteryouremailaddresshere Savemyname,email,andwebsiteinthisbrowserforthenexttimeIcomment. Δ RecentCommentsPrashanthonHowtoUsetheFilterFunctioninGoogleSheets(BasicandAdvancedUse)JimonHowtoUsetheFilterFunctioninGoogleSheets(BasicandAdvancedUse)PrashanthonHowtoFindMultipleOccurrencesofRowsinGoogleSheets[Duplicates]PrashanthonComparisonOperatorsinGoogleSheetsandEquivalentFunctionsDanielonHowtoHideTabsfromSpecificPeopleinGoogleSheets KeyFunctions|ResourcesSumif|Query|Date|IF|Filter|Vlookup|ConditionalFormatting|DataValidation|ExcelVsSheets|Forms|Docs|Row-wiseArray Home ContactUs About Disclaimer PrivacyPolicy Sitemap ©2021PRASHANTKV|InfoInspired|AllRightsReserved