Excel meest actuele waarde per klant

[Nemisia]

Mijn vraag
Ik zou graag een lijst willen maken met daarin de meest recente licentie per klant. De data exporteer ik vanuit ons ERP systeem naar Excel om zo een aantal filters te kunnen toepassen.

Onze klanten hebben een licentie nodig om bij ons zakelijk bepaalde producten te kunnen kopen. Om pro-actief "problemen" voor te zijn, willen wij onze klanten vooraf gaan informeren als hun licentie de komende maand verloopt en er nog geen nieuwe licentie bij ons bekend is.

Om de klanten waarvan de licentie verloopt en geen nieuwe/aansluitende licentie bekend is bij ons, wil ik de data die in de export naar Excel is gehaald een selectie hierop kunnen maken. Ik kom er alleen niet uit hoe ik een dergelijke selectie maak in Excel (wat in mijn ogen wel op een of andere manier mogelijk moet zijn).

Waar ik naar op zoek ben is een mogelijkheid om een lijst te maken met de licenties die komende maand verlopen waar geen nieuwe licentie voor bekend is. Dus als voor klant X licentie 1 op 31-5-2021 verloopt, maar deze klant X heeft een nieuwe lictentie die op 31-5-2026 pas verloopt dan hoeft deze niet in de lijst voor te komen.
Daarintegen, als klant X geen nieuwe licentie heeft dan moet deze wel in de lijst tevoorschijn komen.

In het plaatje een kort voorbeeld:
K10011 heeft een licentie tot 1-7-2021 en geen nieuwe licentie bekend. De moet in de lijst komen.
K10030 heeft een verlopen licentie op 9-5-2021, maar heeft een nieuwere die op 7-5-2024 verloopt. Deze moet niet in de lijst komen.

Relevante software en hardware die ik gebruik
De informatie wordt uit Navision 2016 geëxporteerd naar Excel 2013.

Wat ik al gevonden of geprobeerd heb
Ik heb geprobeerd om de data te filteren op verloopdatum, maar ik kom er niet op uit dat dit dan ook de laatst bekende licentie is. Na verschillende zoekpgingen op Google kom ik ook niet verder hierin, wellicht dat iemand mij hierin kan helpen.


...

[Admiral Freebee]

Als je de lijst aflopend sorteert kan je met een VLOOKUP de meest recente waarde vinden omdat VLOOKUP stopt na de eerste match. Dat lijkt mij de eenvoudigste oplossing.

[KabouterSuper]

MAX, INDEX en MATCH zijn je vrienden.
Kort door de bocht:

code:

=INDEX(C$1:C$999;MATCH(MAXIFS(C$1:C$999;A$1:A$999;"="&A1);C$1:C$999;0))

geeft je per regel de hoogste vervaldatum van het klantnr in die regel. Daarmee kan je met now() een if/then/else maken die doet wat je wil.

[ThinkPad]

Is het niet mogelijk om in Navision zelf een (SQL) query te maken? Dan kun je er wellicht een rapport ofzo van maken die je kunt hergebruiken. Scheelt handwerk in Excel.

[tritimee]

of ernaast een kolom met daarnaast een Maxifs(datum, Klantkolom = Klant) = Datum uit deze regel ?

[Nemisia]

Een query of rapport maken kunnen we zelf helaas niet, daar hebben we bij ons de kennis/kunde helaas niet voor. En omdat deze licenties normaal 5 jaar geldig zijn, gaan we dit op den duur periodiek van +-1x per 2-3 maanden draaien.

Ik ga eens aan de slag met jullie imput, dank jullie hier al voor.

[Fish]

ik zeg maar 1 ding "pivot table"

[Nemisia]

Tuurlijk, draaitabel.
En dan een Max op de vervaldatum per klant zetten, hierop weer filteren naar de gewenste datum bereik en klaar. Thanks.

[dix-neuf]

Ook mogelijk (naast de al vermelde antwoorden):
Typ in F2 (of in een andere kolom op de 2e rij), sluit af met Ctrl-Shift-Enter en kopieer naar beneden:

code:

=als(en(max(als(A$2:A$100=A2;C$2:C$100))=C2;C2<=datumwaarde("31-5-2021"));A2;"")

In de F-kolom verschijnt dan bij elk klantnummer bij de laatste licentievervaldatum die kleiner dan- of gelijk is aan 31-05-2021 het klantnummer. In het voorbeeld zijn dat de klantnummers K10013, K10017, K10085, en K10114.

[flip75]

De knop remove duplicates gebruiken en dan filteren op datum welke klanten binnenkort verlopen is ook een snelle en makkelijke optie.

[Orion84]

Gewoon aflopend sorteren op verloopdatum, dan dedupliceren op klant. Dan heb je van elke klant diens laatste licentie. Dan filteren op verloopdatum.