Ahogy általában az Excelben lenni szokott, a ma bemutatásra kerülő kérdésre sem a posztban leírt megoldás az egyetlen lehetséges megoldás és akinek van kedve, nyugodtan ossza meg a saját verzióját, mindenesetre remélem, hogy többeknek segítséget tudok nyújtani a lentiekkel egy újabb lehetőség kiaknázására.

A kérdés alapvetően roppant egyszerű, van egy egyszerű Data Validation listünk:

Értelemszerűen a legördülő menüből a települések nevei közül tudunk választani, ahogy azt a Source mezőben szépen be is hivatkozzuk:

Eddig ez szerintem bármelyik Excel-felhasználónak ujjgyakorlat, ezért is tekintettem el ezen lépések részletes bemutatásától. Na de hogy érjük azt el, hogy a listában szereplő településeket csak egyetlen egyszer választhassuk ki, azaz ha az első mezőben kiválasztottuk Pilinyt, utána a lehetőségek között ez a nógrádi község már ne szerepeljen?

Ehhez segédoszlopokra (segédtáblára lesz szükségünk), magától értetődő módon csak a példában lesz ennyire látható, amit csinálok, ha élesben szeretnénk ezt a megoldást használni, mindenképpen rejtsük el a segédtáblát.

Szóval elsőként azon lista mellé, amelyből a legördülő menüt feltöltjük, egy plusz oszlopba szúrjuk be a következő függvényt:

=IF(COUNTIF($C$47:$C$51,M48>=1,"",ROW())

Mit is számolunk ezzel? Ugyebár a COUNTIF az első paramétereként megadott tartományban megnézi, hogy  abban hány, a második paraméterének megfelelő érték található, tehát a függvényünk belső része egy számot fog visszaadni, amely megmutatja, hogy a legördülő menüt használó listánkban hányszor szerepel az első településnév (jelen esetben ez most M48 azaz Piliny, de majd ha automatikus kitöltéssel lehúzzuk, nézi a következőt és így tovább). Ezt a számot egy IF függvény használja fel, azaz ha a COUNTIF által visszaadott szám 1 vagy esetleg nagyobb érték, akkor a függvény üres értéket ad vissza, ha pedig 0 a COUNTIF által visszaadott érték, akkor az adott sor számát adja eredményül.

Látható is, ha kiválasztjuk a legördülő menüt tartalmazó táblában az első sorban Litkét, akkor a segédtábla második segédoszlopában, tehát a Litke melletti cellában nincs érték, a többinél a sorszám szerepel.

Ezután még egy segédoszlopra lesz szükségünk, ahol a fentiekben létrehozott segédoszlopot és az eredeti adatokat is fel fogjuk használni. Ennek az új segédoszlopnak az első cellájába a következő függvényt kell beszúrnunk:

=IF(ROW(M48)-ROW(M$48)+1>COUNT(N$48:N$52),"",INDEX(M:M,SMALL(N$48:N$52,1+ROW(M48)-ROW(M$48))))

Első sokkolónak tűnhet, de alapvetően könnyen megfejthető, hogy mit is csinál ez a hosszú függvénysorozat, hiszen ha sorban haladunk az egyes beágyazott függvényeken, akkor előbb-utóbb kirajzolódik a cél és az eredmény.

Szóval HA az adott településünk SORSZÁMÁBÓL kivonjuk mindig az első településünk SORSZÁMÁT + egyet és ez az eredmény nagyobb, mint az első segédoszlopban sorszámot tartalmazó cellák száma (ahogy látható, Litke mellett nincs már sorszám, így ez a beágyazott COUNT négyet fog eredményként adni), akkor nem csinál semmit, viszont ha kisebb ez az érték, akkor a függvényünk INDEX-szel kezdődő részére lépünk. Vegyük észre, hogy minél több települést kiválasztottunk már, annál nagyobb az esélye, hogy üres lesz a függvényünk által visszaadott érték, hiszen például ha négyet már kiválasztottunk, akkor csak egyetlen sorszám marad a segédtáblában.

Ebben az INDEX-es részben azt fogjuk megnézni, hogy melyik az a település, amely nagyságát tekintve az "adott településünk SORSZÁMA plusz egyből mínusz mindig az első településünk SORSZÁMA" által meghatározott helyen áll. Tehát ezzel a résszel kezdjük elvadászni azokat a településeket, amelyek mellett még szerepel sorszám.

Ez most szörnyen hangzott, de egy gyors példán levezetve ez azt jelenti, hogy például Pilinyen végrehajtott vizsgálatunk eredménye az első lépésnél valahogy így néz ki:

48-48+1 = 1

Ami értelemszerűen kisebb mint az N oszlopban sorszámmal rendelkező települések száma, így az INDEX függvényrészre kell ugranunk.

Itt a SMALL függvényrészben az N oszlop sorszámai közül a legnagyobbbat kell megkeresnünk (hiszen 49-48 az 1), majd az ehhez tartozó településnevet kell visszaadnunk.

Így pedig már annyit kell csak csinálnunk, hogy a Data Validation listünkben a Source-ot át kell állítanunk erre a második segédoszlopra az eredeti helyett:

Így ha kiválasztunk mondjuk két települést a listáról, ezek a későbbi választási lehetőségek között már nem lesznek ott:

Azt azért mindenképpen meg kell jegyeznem itt a poszt végén, hogy az ötlet nem saját találmány, valamikor pár évvel ezelőtt egy Excel MVP könyvében láttam eredetileg.