Alternatief voor "Filter" formule in Excel 2016

[D3LTTA]

Thuis met Office 365 een Excel gemaakt voor mijn werk waarbij ik de "Filter" formule gebruikt die helaas niet in Excel 2016 zit en laten we daar net mee werken op mijn werkplek.

Op mijn werkplek hebben we een centrale plek waar "Word" documenten worden bij gehouden met nood procedures indien bijv. de stroom uitvalt en er niet met de systemen gewerkt kan worden die intern draaien.

In deze noodprocedures zitten ook picklijsten van artikelen die bij elkaar gepickt moeten worden voor verschillende opdrachten. Nu is het zo dat elke keer als er een artikel veranderd deze handmatig aangepast moet worden in tientallen verschillende Word documenten en daarvoor ben ik een makkelijkere manier aan het bouwen in Excel waardoor artikelen op een plek veranderd kunnen worden en deze op elke pick procedure worden aangepast.

...

Excel 2016
...

Wat ik al gevonden of geprobeerd heb


Dit is hoe mijn Excel er nu uit, Zoals je kan zien zodra ik deze op mijn werk met Excel 2016 open en de filter formule selecteer in cel A8 krijg ik =_xlfn._xlws. ervoor te staan waarmee Excel aangeeft dat deze formule niet gebruikt kan worden in de huidige versie. Helaas ben ik nu niet thuis om een screenshot te maken van hoe het eruit hoort te zien maar op basis van het opdrachtnummer haal ik met de Filter formule de artikelen op in het tabblad artikelen die bij het opdrachtnummer horen. Omdat deze nu niet werkt in 2016 is dit de beste screenshot die ik nu kan maken. bij elke Naam? zou een artikelnummer moeten staan dat gevonden werd in mijn tabblad met artikelen op basis van het Opdrachtnummer in cel B5

En voor de volledigheid een screenshot van het werkblad met de artikelen voor elk Opdrachtnummer en de tabbladen.





Ik ben dus op zoek naar een vervangende formule waarmee ik makkelijk de lijst van artikelen gevuld krijg op elk tabblad met een ander opdrachtnummer. Al mijn Excel kennis komt van zelf zoeken en zover kom ik elke keer uit op INDEX en Aggraat formules maar zodra ik deze zelf probeer toe te passen loop ik elke keer op vast op foutmeldingen. Zo heb ik onderstaande formule op basis van een algemeen voorbeeld op een forum proberen toe te passen maar helaas zonder succes.
=als.fout(INDEX(artikelen!$A:$A;Aggregaat(15;6;(rij(artikelen!$A:$A)-rijen($B$5)+1/(Artikelen!$B:'Picklijst OPD001'!)/($B$5);rijen(A8;A8)));"'")

De meeste voorbeelden die ik kan vinden hebben alles op een werkblad waardoor de formule er al direct zoveel anders uit ziet en ik er helaas niet meer zelf uit kom. Verdere informatie in de picklijsten ga ik uiteindelijk vullen met verticaal zoeken.

Het idee is om uiteindelijk een Excel bestand te hebben met 30 tabbladen en dat er alleen in het tabblad "artikelen" data aangepast hoeft te worden.

[dixet]

Je zal dan denk ik naar VBA moeten grijpen.

Hier heb ik een mooie functie gevonden die volgens mij precies doet wat je wilt.

Om deze functie te gebruiken in Excel 2016 zal je hem wel als matrixfunctie moeten aanroepen, dus na het invoeren van de functie op SHIFT-CTRL-ENTER drukken. Je zien dan accolades om de functie heen verschijnen

code:

{=FILTER_AK(Artikelen!$A$2:$B$28;Artikelen!$A$2:$A$28=Opdrachttemplates!B6)}

[dix-neuf]

Ik zie staan: 'artikelcode', 'opdrachtnummer', en 'artikelomschrijving'? Zijn daar namen bij die hetzelfde betekenen? Is bv. artikelcode hetzelfe als opdrachtnummer? Zo ja, wat verwacht je dan als omschrijving? Er staat nergens een omschrijving ingevuld.

[JiMiHeNdRiX]

Mijn gevoel zegt dat je aan 2 tabbladen ook voldoende hebt ipv 30. Eentje met de layout en eentje met de opdrachtnummers incl artikelen.

[D3LTTA]

dixet schreef op maandag 2 december 2024 @ 16:44:
Je zal dan denk ik naar VBA moeten grijpen.

Hier heb ik een mooie functie gevonden die volgens mij precies doet wat je wilt.

Om deze functie te gebruiken in Excel 2016 zal je hem wel als matrixfunctie moeten aanroepen, dus na het invoeren van de functie op SHIFT-CTRL-ENTER drukken. Je zien dan accolades om de functie heen verschijnen

code:

{=FILTER_AK(Artikelen!$A$2:$B$28;Artikelen!$A$2:$A$28=Opdrachttemplates!B6)}

Bedankt voor je suggestie, Helaas krijg ik als resultaat "naam?" , In je formule gebruik je Opdrachttemplates!B6 daarmee bedoel je denk ik B5 waar het opdrachtnummer staat ? Ik heb dit in ieder geval proberen te veranderen maar dacht mocht ook niet helpen.

[D3LTTA]

dix-neuf schreef op maandag 2 december 2024 @ 17:15:
Ik zie staan: 'artikelcode', 'opdrachtnummer', en 'artikelomschrijving'? Zijn daar namen bij die hetzelfde betekenen? Is bv. artikelcode hetzelfe als opdrachtnummer? Zo ja, wat verwacht je dan als omschrijving? Er staat nergens een omschrijving ingevuld.

Deze gaan gevuld worden met verticaal zoeken.Mijn probleem is dat ik eerst de artikelnummers/codes (hier bedoel ik hetzelfde mee) per opdrachtnummer in de template krijg met alleen het veranderen van het opdrachtnummer in de template of in de lijst met artikelen.

[D3LTTA]

JiMiHeNdRiX schreef op maandag 2 december 2024 @ 17:18:
Mijn gevoel zegt dat je aan 2 tabbladen ook voldoende hebt ipv 30. Eentje met de layout en eentje met de opdrachtnummers incl artikelen.

In principe heb je helemaal gelijk en zou ik het opdrachtnummer op die ene template elke keer kunnen veranderen. maar dan moeten wel de artikelen meekomen als deze veranderen. iets wat mij momenteel dus niet lukt in Excel 2016.

[dixet]

D3LTTA schreef op maandag 2 december 2024 @ 20:44:
[...]


Bedankt voor je suggestie, Helaas krijg ik als resultaat "naam?" , In je formule gebruik je Opdrachttemplates!B6 daarmee bedoel je denk ik B5 waar het opdrachtnummer staat ? Ik heb dit in ieder geval proberen te veranderen maar dacht mocht ook niet helpen.

Ale je =FILTER begint te typen, komt hij dan wel met de suggestie voor FILTER_AK? Anders staat je VBA code misschien niet op de juiste plek.

Als hij hem wel herkent kan je debugging in VBA aanzetten om te kijken waar het mis gaat. Bij mij deed deze code het wel op een testsheetje.

[D3LTTA]

dixet schreef op maandag 2 december 2024 @ 21:08:
[...]

Ale je =FILTER begint te typen, komt hij dan wel met de suggestie voor FILTER_AK? Anders staat je VBA code misschien niet op de juiste plek.

Als hij hem wel herkent kan je debugging in VBA aanzetten om te kijken waar het mis gaat. Bij mij deed deze code het wel op een testsheetje.

Nee alleen FISHER als ik met FI begin.

[dixet]

Dan heb je waarschijnlijk de VBA code niet op de juiste plek staan of staat VBA uit in je werkboek

[D3LTTA]

dixet schreef op dinsdag 3 december 2024 @ 08:32:
Dan heb je waarschijnlijk de VBA code niet op de juiste plek staan of staat VBA uit in je werkboek

Onder de invoegtoepassingen in Excel kan ik alleen Analysis toolpack - VBA vinden maar ik ga even verder googlen of ik er meer over kan vinden. In ieder geval bedankt voor je tijd.

[dixet]

Invoegtoepassingen? Daar doen we hier niets mee hoor

Heb je de VBA wel in je code editor geplakt? Zoals in de link beschreven staat: rechtermuisknop op je sheetnaam, kiezen voor "View code". Daar een nieuwe module maken en de code plakken.

[ Voor 3% gewijzigd door dixet op 03-12-2024 09:16 ]

[dix-neuf]

@D3LTTA ,
Wat is de naam van het blad waarin je de artikelcodes wil zien verschijnen?
Dat is dit blad:

[ Voor 53% gewijzigd door dix-neuf op 03-12-2024 10:34 ]

[D3LTTA]

dixet schreef op dinsdag 3 december 2024 @ 09:16:
Invoegtoepassingen? Daar doen we hier niets mee hoor

Heb je de VBA wel in je code editor geplakt? Zoals in de link beschreven staat: rechtermuisknop op je sheetnaam, kiezen voor "View code". Daar een nieuwe module maken en de code plakken.

Oeps, iets te snel geweest, ik heb de VBA code die in de link staat nu in de VBA edito onder Module geplakt bij het tabblad waar de artikelen staan ( ik weet niet of dit uit maakt). Als ik nu je formule gebruik en CTRL Shift Enter dan krijg ik niet meer "Naam?" maar Waarde! terug.

Ik heb de formule wel nog aangepast naar B5 inplaats van de B6 want die verwijst naar een lege cel, ik neem aan dat die moet verwijzen naar de cel waar het opdrachtnummer staat.

[D3LTTA]

dix-neuf schreef op dinsdag 3 december 2024 @ 09:46:
@D3LTTA ,
Wat is de naam van het blad waarin je de artikelcodes wil zien verschijnen?
Dat is dit blad:
[Afbeelding]

Hoi Dix-neuf,

Dat tabblad heeft de naam Opdrachttemplates

[dix-neuf]

- Rechtsklik onderaan op de bladnaam "Opdrachttemplates" en kies in het verschijnende menu: "Programmacode weergeven", je komt dan in de vba-editor.
- Plaats daar aan de rechterzijde onderstaande code (die code komt dus NIET in een module) en sluit daarna het venster door op het kruisje rechtsboven te klikken.
- Sla het bestand op als een .xlsm-bestand (=bestand met macro's).
- Typ in het blad "Opdrachttemplates" in B5 een opdrachtnummer in en druk op Enter, de gevraagde artikelcodes zullen dan verschijnen (als tenminste opdrachtnrs. en bijbehorende artikelnrs. nog altijd in het blad "Artikelen" staan).

code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B5"), Target) Is Nothing Then
Dim lr1 As Integer, lr2 As Integer, m As Integer, r As Integer, x As Integer
lr2 = Range("A" & Rows.Count).End(xlUp).Row
m = WorksheetFunction.Max(8, lr2)
Range("A8:A" & m).ClearContents
r = 7
With Sheets("Artikelen")
lr1 = .Range("A" & .Rows.Count).End(xlUp).Row
For x = 2 To lr1
If .Range("A" & x).Value = Range("B5").Value Then
r = r + 1
Range("A" & r).Value = .Range("B" & x).Value
End If
Next x
End With
End If
End Sub

[D3LTTA]

dix-neuf schreef op dinsdag 3 december 2024 @ 10:57:
- Rechtsklik onderaan op de bladnaam "Opdrachttemplates" en kies in het verschijnende menu: "Programmacode weergeven", je komt dan in de vba-editor.
- Plaats daar aan de rechterzijde onderstaande code (die code komt dus NIET in een module) en sluit daarna het venster door op het kruisje rechtsboven te klikken.
- Sla het bestand op als een .xlsm-bestand (=bestand met macro's).
- Typ in het blad "Opdrachttemplates" in B5 een opdrachtnummer in en druk op Enter, de gevraagde artikelcodes zullen dan verschijnen (als tenminste opdrachtnrs. en bijbehorende artikelnrs. nog altijd in het blad "Artikelen" staan).

code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B5"), Target) Is Nothing Then
Dim lr1 As Integer, lr2 As Integer, m As Integer, r As Integer, x As Integer
lr2 = Range("A" & Rows.Count).End(xlUp).Row
m = WorksheetFunction.Max(8, lr2)
Range("A8:A" & m).ClearContents
r = 7
With Sheets("Artikelen")
lr1 = .Range("A" & .Rows.Count).End(xlUp).Row
For x = 2 To lr1
If .Range("A" & x).Value = Range("B5").Value Then
r = r + 1
Range("A" & r).Value = .Range("B" & x).Value
End If
Next x
End With
End If
End Sub

Hoi Dix-neuf,

Dit werkt perfect , is dit in samenwerking met de VBA oplossing van Dixet of staat deze code opichzelf ?

[dix-neuf]

Die code 'staat op zichzelf', dus los van alle andere codes.
Als je mijn macro wil gebruiken, best andere codes die op hetzelfde betrekking hebben, uitschakelen om geen conflicten te krijgen. Wat er bij 'Artikelomschrijving' moet komen, kun je in de macro uiteraard ook meenemen.

[D3LTTA]

dix-neuf schreef op dinsdag 3 december 2024 @ 11:49:
Die code 'staat op zichzelf', dus los van alle andere codes.
Als je mijn macro wil gebruiken, best andere codes die op hetzelfde betrekking hebben, uitschakelen om geen conflicten te krijgen. Wat er bij 'Artikelomschrijving' moet komen, kun je in de macro uiteraard ook meenemen.

Is goed ga ik de rest eruit halen. hartstikke bedankt ik heb hier gisteravond uren mee liggen klooien.

Ook @dixet bedankt !