Verticaal zoeken en dubbele waarden niet meenemen

[Sander2209]

Beste,

Ik heb een probleem in excel met de functie verticaal zoeken. Het probleem is dat de functie verticaal zoeken steeds de eerste waarde in de tabal pakt, terwijl er meer waarden zijn die aan deze criteria voldoen.

Mijn functie ziet er op dit moment zo uit: =VERT.ZOEKEN($A$6;'Basis 2'!$A$3:$I$3000;2;ONWAAR)

Vervolgens trek ik mijn functie door in de tabel naar beneden en krijg ik 100 keer dezelfde uitkomst te zien. Is het mogelijk om verticaal zoeken zo te ontwerpen dat hij de voorgaande criteria in de tabel niet meer meeneemt.

Bij voorbaat dank Dames en Heren!!

 

[abevleeming]

Dat kan beter met een draaitabel of de "INDEX()" en "VERGELIJKEN()" functies. Draaitabel heb ik weinig verstand van, de andere functies wel. Door hier een matrixformule van te maken kan het relatief eenvoudig. Maak anders even een voorbeeldje met de cellen waar je de formule in wilt plakken

 

[Sander2209]

Dat kan beter met een draaitabel of de "INDEX()" en "VERGELIJKEN()" functies. Draaitabel heb ik weinig verstand van, de andere functies wel. Door hier een matrixformule van te maken kan het relatief eenvoudig. Maak anders even een voorbeeldje met de cellen waar je de formule in wilt plakken

Het is dus de bedoeling dat op het blad Afzender_detail alle supermarkten komen te staan waar boer tim aan levert, zonder dat er ook dubbele tussen staan.

 

[VenA]

Het is een beetje afhankelijk van wat jij dubbel vindt.

Met een draaitabel.

 

[MarcelBeug]

Normaliter zou ik dit ook met een matrixformule doen, maar de zinsnede "verticaal zoeken zo te ontwerpen dat hij de voorgaande criteria in de tabel niet meer meeneemt" bracht me tot een oplossing met gewone formules die precies dat doet.

Ik heb een hulpkolom toegevoegd in 'Afzender_detail' met hulpvelden:
A4: Laatst gevulde regel in 'Basis 2'.
A5: Aantal keer dat de betreffende boer voorkomt.
A7 en gekopieerd naar beneden: de regel waarop de betreffende boer voorkomt in 'Basis 2'.
B7, gekopieerd naar rechts en naar beneden: simpele INDEX functie die de betreffende waarden ophaalt uit 'Basis 2'.

Formule in A7:

=ALS(RIJEN(A$7:A7)>$A$5;"";A6+VERGELIJKEN($B$6;INDEX('Basis 2'!A:A;A6+1):INDEX('Basis 2'!A:A;$A$4);0))

Let op: A6 moet leeg blijven.

 

[Sander2209]

Normaliter zou ik dit ook met een matrixformule doen, maar de zinsnede "verticaal zoeken zo te ontwerpen dat hij de voorgaande criteria in de tabel niet meer meeneemt" bracht me tot een oplossing met gewone formules die precies dat doet.

Ik heb een hulpkolom toegevoegd in 'Afzender_detail' met hulpvelden:
A4: Laatst gevulde regel in 'Basis 2'.
A5: Aantal keer dat de betreffende boer voorkomt.
A7 en gekopieerd naar beneden: de regel waarop de betreffende boer voorkomt in 'Basis 2'.
B7, gekopieerd naar rechts en naar beneden: simpele INDEX functie die de betreffende waarden ophaalt uit 'Basis 2'.

Formule in A7:

=ALS(RIJEN(A$7:A7)>$A$5;"";A6+VERGELIJKEN($B$6;INDEX('Basis 2'!A:A;A6+1):INDEX('Basis 2'!A:A;$A$4);0))

Let op: A6 moet leeg blijven.

Heel erg bedankt voor deze functie!! We zijn al een eindje op weg, want ik vroeg me af of in 'Afzender_detail' die tweede plus, dus de Plus in Eindhoven, ook niet meegenomen kan worden, want nu staat Plus dubbel en het is de bedoeling dat er maar 1 keer Plus staat. Zodat je uiteindelijk alle supermarkten onder elkaar ziet staan waar boer tim aan levert, zonder dat er, in dit geval, twee keer Plus komt te staan. Ik heb voor het gemak even jouw bestandje als bijlage toegevoegd.

Alvast weer heel erg bedankt!!

 

[MarcelBeug]

Nou, dan zou ik zeker voor de draaitabel gaan: dan hoef je alleen maar "Stad" bij de rijen (rijlabels) te verwijderen.

Het kan nog wel met formules, maar die worden nogal draconisch en ook matrixformules zijn niet meer te vermijden (dit zijn formules die zijn ingegeven met Ctrl+Shift+Enter en ze zijn herkenbaar doordat Excel er dan accolades omheen zet: A5 en A7 naar beneden gekopieerd).

In A6 staat nu 2 (de rij met de kopregel in 'Basis 2').

 

[WHER]

Zoals hierboven reeds gezegd: doe het via een draaitabel.
Anders kan het ook via een matrix variant van je oorsprokelijke Vert.Zoeken formule:

 

[VenA]

Blijkbaar heeft de TS #4 gemist. Of reageert alleen op het laatst geplaatste bericht

Even de voor- en nadelen op rijtje. (mag aangevuld/gecorrigeerd worden)
Matrixformule:
voordelen:
-Direct reslutaat (wat niet geheel waar is maar dat komt bij de nadelen);
-Je kan de formule in elke willekeurige cel zetten.
Nadelen:
-Kan op grote bereiken zeer vertragend werken;
-Zijn, zeker voor minder ervaren Excel gebruikers, moeilijk te doorgronden;
-'Veel' typewerk;
-Je moet de lay-out nog aanpassen voor een beetje nette presentatie.

Draaitabel:
Voordelen:
-Razendsnel;
-Zeer eenvoudige wizzard;
-Direct een nette presentatie (wat niet geheel correct is maar dat komt bij de nadelen);
-Etc.
Nadelen:
-Wordt niet automatisch bijgewerkt;
-De standaard instellingen geven niet altijd de gewenste presentatie.

 

[Peter B]

Het laatste nadeel van de draaitabel valt te ondervangen met het change_event en een soort code als:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 2 And Target.Column < 8 Then
    If Target.Row > 1 And Target.Row < 50 Then
        Sheets("SheetDraaitabel").PivotTables("NaamDraaitabel").PivotCache.Refresh
    End If
End If

End Sub

Natuurlijk naar wens aan te passen ...

 

[VenA]

Waarschijnlijk bedoel je het eerste nadeel Dat het met een paar regels code wel op te lossen is, is mij bekend. Maar wat als de systeembeheerder het zo ingeregeld heeft dat er geen code gebruikt mag worden?

Dus blijft dit een nadeel.:d

 

[Peter B]

Je hebt gelijk. En natuurlijk weet ik dat jij dat weet. Maar misschien Sander niet

 

[Sander2209]

Iedereen heel erg bedankt voor het reageren op mijn bericht en het verzinnen van een oplossing!!! :thumb:
Alle functies en draaitabellen die jullie gemaakt hebben werken foutloos, dus ik heb nog keuze ook