• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Dubbel verticaal zoeken?

Status
Niet open voor verdere reacties.
M

Martijnbm

Gebruiker
Lid geworden
13 aug 2016
Berichten
57
Goeieavond,

Ik heb een cliëntenoverzicht waarbij ik het resterende budget graag overzichtelijk in beeld wil hebben. Een cliënt kan meerdere budgetten hebben en ieder budget heeft een resterend bedrag.

De export uit het ERP systeem geeft echter slechts één rij per cliënt en in geval van meerdere budgetten worden deze achter elkaar weergegeven, niet onder elkaar zoals je zou verwachten.
Ik krijg het formule-technisch echter niet voor elkaar.

In het bijgevoegde bestand staat in Blad 1 de export en in Blad 2 het cliëntenoverzicht.
De waarde in de geel gemarkeerde cel moet via een formule uit de export komen die zoekt naar de waarde in cel B3. Is dit iets voor x.zoeken? Ik ken deze functie niet goed dus het wil nog niet lukken.
Het budgetrestant staat altijd één cel rechts van het budgetnummer.

Ik heb de softwareleverancier al gevraagd om de export aan te passen maar of ze dat doen is natuurlijk maar de vraag.

Alvast dank voor de hulp!
 

Bijlagen

  • Restant zoeken.xlsx
    9,8 KB · Weergaven: 16
Martijnbm zei:
Ik heb de softwareleverancier al gevraagd om de export aan te passen maar of ze dat doen is natuurlijk maar de vraag.
Klik om te vergroten...

Als je pak-em-beet 1500 euro op tafel legt voor een dag consultancy en bouwen zullen ze het met liefde voor je doen.



Tot die tijd zou ik powerquery gebruiken. Ik heb de indruk dat je voorbeeld te beperkt is en dat er soms ook 3 of 4 budgetten kunnen zijn, dus dan helpt nu je voorbeeldje uitwerken vrij weinig. daarom deze keer bij uitzondering een verwijzing naar 2 video's over hoe je van kolommen snel rijen kunt maken (unpivot)

Video: Multiple column pairs in power query
https://www.youtube.com/watch?v=U1O5LfMZP0s

Video2 Unpivot multiple column pairs dynamicly
https://www.youtube.com/watch?v=aEezNicgHkE

In de eerste video wordt uitgelegd hoe je van setjes van 2 kolommen (in jou geval budgetnr en budget bedrag) een nette datatabel maakt. nadeel van methode 1 is dat er "hard coded" kolomkoppen in staan en je foutmeldingen kunt krijgen als je meer kolommen krijgt in een later rapport of juist minder.
vermoedelijk heb jij dus ook video 2 nodig om die lijst weer dynamisch te maken op basis van het werkelijk aantal kolommen in je rapport.
 
Optie voor Excel 365:
Code: 
=LET(x;FILTER(Blad1!$A$2:$F$5;Blad1!$A$2:$A$5=A3;"");ALS.FOUT(INDEX(x;1+VERGELIJKEN(B3;x;0));""))
Is je voorbeeld representatief genoeg?
 
Laatst bewerkt:
Ook 365.
Code: 
=KIES.KOLOMMEN(FILTER(Blad1!A2:F5;(Blad1!A2:A5=A3)*(Blad1!E2:E5=B3);"");6)

Of voor oudere versies.
Code: 
=SOMPRODUCT((Blad1!A2:A5=A3)*(Blad1!E2:E5=B3)*Blad1!F2:F5)
 
Kies.kolommen werkt voorlopig nog niet in office 365.

Code: 
=FILTER(FILTER(Blad1!A2:F5;(Blad1!A2:A5=A3)*(Blad1!E2:E5=B3);"");{0\0\0\0\0\1})
 
Dat is dan weer jammer.

Deze dan?

Code: 
=X.ZOEKEN(A3;((Blad1!A2:A5)*(Blad1!E2:E5=B3));Blad1!F2:F5)
 
Het kan inderdaad zijn dat er nog meer budgetten zijn. De formule x.zoeken, somproduct en filter pakken de juiste waarde dan niet. Ook kies kolommen en let geven niet de juiste uitkomst helaas.
Het zoekbereik voor het budgetnummer moet dus niet een beperkt aantal kolommen zijn maar de hele rij achter het client ID.
 
Heb het bestand aangepast om te laten zien dat er meer budgetten kunnen zijn.
Wordt in B3 456 ingevuld, dan moet er 222 verschijnen. Wordt 123 ingevuld, dan moet 111 verschijnen.
 

Bijlagen

  • Restant zoeken.xlsx
    10,1 KB · Weergaven: 11
kan vba een oplossing zijn?
 

Bijlagen

  • Restant zoeken.xlsm
    22,5 KB · Weergaven: 8
Of als je het bereik van de functie een beetje aanpast?
Code: 
=LET(x;FILTER(Blad1!$A$2:[COLOR="#FF0000"]$ZZ$5[/COLOR];Blad1!$A$2:$A$5=A3;"");ALS.FOUT(INDEX(x;1+VERGELIJKEN(B3;x;0));""))
 
Maak van je gegevesn een genormaliseerde tabel.
Een draaitabel doet dan de rest.


Code: 
Sub M_snb()
   sn = Sheet1.Cells(1).CurrentRegion.Resize(100)
   y = Sheet1.Cells(1).CurrentRegion.Rows.Count + 1
   
   For j = 2 To UBound(sn)
     For jj = 5 To UBound(sn, 2) Step 2
      If sn(j, jj) = "" Then Exit For
       sn(n + y, 1) = sn(j, 1)
       sn(n + y, 2) = sn(j, 2)
       sn(n + y, 3) = sn(j, jj)
       sn(n + y, 4) = sn(j, jj + 1)
       n = n + 1
     Next
   Next

   Sheet1.Cells(1).CurrentRegion.Resize(100, 4).Offset(, 10) = sn
End Sub
 

Bijlagen

  • __budget_snb.xlsb
    16,9 KB · Weergaven: 21
Laatst bewerkt:
De functie van AlexCEL werkt als een trein, bedankt!
Van de LET functie had ik nog nooit gehoord.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan