• 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.

Excel Matrix unieke waarden

Status
Niet open voor verdere reacties.
M

mvd2387

Gebruiker
Lid geworden
16 feb 2018
Berichten
17
Beste

Ik wil vanuit "Bron 1" kolom A alle waarden ophalen die beginnen niet "IR", deze moeten vervolgens verschijnen in "Test".
Dit lukt mij met volgende formule: "=INDEX(Bron1!A:A;KLEINSTE(ALS((LINKS(Bron1!A:A;2)="IR");RIJ(Bron1!A:A);999);RIJ()))" (Zie "Test" kolommen A+B+C)

Probleem
Maar nu wil ik dat elke waarde maar één keer voorkomt. (Nu komt "IR" 8 keer voor).
Ik heb geprobeerd met VERGELIJKEN maar het lukt me niet..

"=ALS.FOUT(INDEX(Bron1!A:A;KLEINSTE(ALS((LINKS(Bron1!A:A;2)="IR");VERGELIJKEN(Bron1!A:A;$E$1:E1;0);999);RIJ()));"")" (Formule geplakt in "Test" in kolom E)
Dit geeft waarde 0 terug.

"=ALS.FOUT(INDEX(Bron1!A:A;KLEINSTE(ALS((LINKS(Bron1!A:A;2)="IR");ALS(ISNB(VERGELIJKEN(Bron1!A:A;G$1:G2;0));RIJ(Bron1!A:A));999);RIJ()));"")" (Formule geplakt in "Test" in kolom G)
Ook dit geeft waarde 0 terug.

Wie kan mij helpen?
Alvast super hard bedankt!!

Bekijk bijlage Testbestand.xlsx

M
 
Deze (matrix)functie gebruiken:
Code: 
=ALS.FOUT(INDEX(Bron1!$A$1:$A$29;VERGELIJKEN(0;ALS(LINKS(Bron1!$A$1:$A$29;2)="IR";AANTAL.ALS($A$1:$A1;Bron1!$A$1:$A$29);"");0));"")
Zie ook bijlage.
 

Bijlagen

  • Testbestand (AC).xlsx
    15,7 KB · Weergaven: 82
Netjes Alex :thumb:
Hoewel ik vanaf XL2013 IFNA ipv IFERROR zou gebruiken
 
Super bedankt! Werkt prefect..

Maar nu ik toch bezig ben wil ik graag direct een nieuw 'probleem' aanhalen.
In bijlage hetzelfde bestandje opnieuw. Alleen Nu heb ik in "Test" Levels bijgevoegd. Per departement zijn er verschillende levels met bijhorende namen.
In "Test" kan ik deze namen aan de levels koppelen en bijhorende dep. koppelen.

De verschillende dep. mogen maar 1x voorkomen in de lijst (zie vorige vraag), maar hoe kan ik nu aan deze dep. meerdere levels koppelen?

Hoop dat ik mijn vraag goed formuleer.

Nogmaals bedankt!
M

Bekijk bijlage Testbestand2.xlsx
 
Zie bijlage. Voldoet dit?
 

Bijlagen

  • Testbestand2 (AC).xlsx
    25,3 KB · Weergaven: 65
Top!! Zou je misschien kunnen uitleggen wat je precies gedaan hebt? Want begrijp de formule niet 100%.. ;)

M
 
Korte toelichting:
Code: 
=ALS.FOUT(INDEX(Bron1!$C:$C;1/(1/SOMPRODUCT((Bron1!$A$2:$A$30=$A4)*(RECHTS(Bron1!$B$2:$B$30;2)=C$3)*RIJ(Bron1!$C$2:$C$30))));"")
- De SOMPRODUCT vergelijkt
1) of de afdeling overeenkomt, uitkomst is 0 of 1.
2) of de laatste 2 tekens van de "release code" overeenkomen met de kolomnaam (L0, L1, etc.), uitkomst is 0 of 1
- SOMPRODUCT werkt als een matrixfunctie, dus hij loopt het hele bereik af. Uitkomst is een matrix, bijvoorbeeld {0,0,0,1,0,0,0}
- Door deze uitkomst te vermenigvuldigen met het RIJ-nummer krijg je het rijnummer waar de oplossing van de vraagstelling (combinatie "department"/"release code") te vinden is. De INDEX-functie gebruikt dit.
- Bij geen overeenkomst geeft de SOMPRODUCT 0 als uitkomst. Niet handig, want dan zou de INDEX het 0-de getal op moeten zoeken, in dit geval "agent name"
- Om dit te ondervangen voer je de bewerking 1/(1/...) uit. Als de SOMPRODUCT 0 is geeft dit een foutmelding en wordt een lege cel geplaatst... anders krijg je gewoon de naam.

Een alternatief, veel langer, is:
Code: 
=ALS.FOUT(INDEX(Bron1!$C:$C;ALS(SOMPRODUCT((Bron1!$A$2:$A$30=$A4)*(RECHTS(Bron1!$B$2:$B$30;2)=C$3))=0;"";SOMPRODUCT((Bron1!$A$2:$A$30=$A4)*(RECHTS(Bron1!$B$2:$B$30;2)=C$3)*RIJ(Bron1!$C$2:$C$30))));"")
 
Bedankt voor de uitleg Axel!
Ik heb echter nog een probleempje met de formule die je stuurde..

Het kan gebeuren dat er meerdere namen per dep. per level zijn. Zie Testbestand en de geel gemarkeerde velden..
Hoe los ik dit het beste op?

Daarnaast heb ik ook een "Eindresultaat" toegevoegd. Het is ook nog de bedoeling dat de gegevens uit 'Bron 2' worden toegevoegd aan de lijst.
Hier heb ik zelf nog niet achter kunnen zoeken, maar misschien heeft u een idee hoe ik dit het beste aanpak.

Nogmaals bedankt voor uw tijd!
M

Bekijk bijlage Testbestand2.xlsx
 
Sheet anders opzetten. Met formules zoals jij het wilt presenteren lijkt me nogal lastig (=understatement).
 
AlexCEL zei:
Sheet anders opzetten. Met formules zoals jij het wilt presenteren lijkt me nogal lastig (=understatement).
Klik om te vergroten...

Oké, toch bedankt.
Wel nog één (hopelijk) laatste vraag: zoals ik al zei in mijn laatste bericht heb ik nog een probleempje met jou formule. Zoals je kunt zien in het testbestand is er voor departement 'IR-SAF' twee keer een L5 met twee verschillende personen. Wanneer ik jouw formule daarop toepast geeft hij een lege cel terug.

Is het mogelijk om in dit soort gevallen beide namen weer te geven in 1 cel (liefst onder elkaar)?

Bedankt!
M

Bekijk bijlage Testbestand2.xlsx
 
Kort antwoord? Dat is m.i. niet mogelijk met formules of extreem lastig...
 
Hiermee toch al een eind op weg:


Matrixformule:

PHP: 
=INDEX(Bron!C1:C100;LARGE((Bron!A1:A100&Bron!B1:B100=A16&"BU_MGR_"&G3)*ROW(1:100);1);1)
 
Leuk die matrix formules. Ik heb een top-of-the-line laptop met een Intel Xeon 64 bit processor en het berekenen van het bestand duurt tergend lang.

Wat vinden jullie van Get & Transform... Gewoon alles vernieuwen klikken en klaar. Moet je wel Excel 2016 hebben of de PowerQuery invoegtoepassing.
 

Bijlagen

  • Testbestand2.xlsx
    160,6 KB · Weergaven: 42
snb zei:
Hiermee toch al een eind op weg:


Matrixformule:

PHP: 
=INDEX(Bron!C1:C100;LARGE((Bron!A1:A100&Bron!B1:B100=A16&"BU_MGR_"&G3)*ROW(1:100);1);1)
Klik om te vergroten...

Beste, ik krijg de fout '#NAAM?'.
Zou u misschien het bestandje kunnen sturen met uw formule in?
Bedankt!
M
 
Of draai:

Code: 
Sub M_snb()
    sn = Sheet1.Cells(1).CurrentRegion
    ReDim sp(0, 7)
    
    With CreateObject("scripting.dictionary")
        For j = 2 To UBound(sn)
           y = Right(sn(j, 2), 1)
           If IsNumeric(y) Then
            st = sp
            st(0, 0) = sn(j, 1)
            If .exists(sn(j, 1)) Then st = .Item(sn(j, 1))
             st(0, y + 1) = IIf(st(0, y + 1) = "", "", st(0, y + 1) & vbLf) & sn(j, 3)
            .Item(sn(j, 1)) = st
           End If
        Next
        
        Sheet2.Cells(20, 1).Resize(.Count, UBound(sp, 2) + 1) = Application.Index(.items, 0, 0)
    End With
End Sub

@JKP

Dan ligt het aan de software.
Met mijn formule toegevoegd: in goed Nederlands: instantaneous.

XL, Office 2010.
 

Bijlagen

  • __pivot aangepast snb.xlsb
    25,7 KB · Weergaven: 31
Laatst bewerkt:
Berekenen van tabblad Test duurt bij mij 0.9 sec. Kon erger, maar als er meer gegevens zijn dan de 3000 rijen waarnaar nu wordt verwezen...
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan