Sorteren op basis van een waarde

[Roy3001]

Beste leden,

Ik heb jullie hulp nodig bij het volgende.
Iedere week krijg ik een overzicht met gegevens. In dit overzicht zijn collegae genoemd die met een zaak bezig zijn. Omdat we diverse clusters hebben ben ik iedere week voor ieder cluster apart de namen het sorteren om te zien wie wat in behandeling heeft van welk cluster. Nu wil ik met een druk op de knop zien wat ieder cluster in behandeling heeft, zonder de leden iedere week apart te selecteren.

Nu dacht ik dat als ik een overzicht maak met de namen van de collegae en onder welk cluster ze vallen dat ik dan op de een of andere manier zou kunnen sorteren. Mijn idee is dan ook dat als ik het aangereikte gegevensblad als nieuw tabblad 'zaken' plak in het bestand waar ik de namen en clusters heb, dat ik dan via een macro/VBA toewijs aan een knop die automatisch de gegevens lijst filtert op het cluster, bijvoorbeeld 'Cluster 1' of 'Cluster 3'. Dat de macro/VBA als ik op 'Cluster 1' klik kijkt in het tabblad 'naam en cluster' wie onder Cluster 1 valt en dan op het gegevensblad 'zaken' automatisch alleen de regels laat zien van de leden die onder cluster 1 vallen. De leden van een cluster kunnen wisselen/verdwijnen/erbij komen, dus de macro/vba moet hiermee rekening houden.

Het moet ook werken in Excel 2013

Ik hoop dat mijn bedoeling duidelijk is.

Graag advies of andere ideeën hoe ik zoiets kan realiseren.

Alvast bedankt voor de reacties.

Groet,

Roy

 

[Peter B]

Ik weet niet of ik je wens helemaal goed begrijp. Zoals ik het zie kan dit heel simpel middels een VERT.ZOEKEN (of VLOOKUP) worden verzorgd waarna je zelf filtert op het cluster.

Zet eens in E5 de volgende formule en trek deze door naar beneden:

=VLOOKUP($B5;'Naam en cluster'!$A$2:$B$9;2;FALSE)
of
=VERT.ZOEKEN($B5;'Naam en cluster'!$A$2:$B$9;2;ONWAAR)

 

[AlexCEL]

ik zat op hetzelfde spoor als Peter hierboven...

Zie voorbeeldje: Bekijk bijlage Naam en cluster (AC).xlsx

 

[Roy3001]

Aan vert.zoeken was ik zelf ook aan het denken, maar dat is gedeeltelijk wat ik wil. Nu moet ik dan handmatig in een kolom de formule van vert.zoeken erin zetten en dat is niet wat ik wil. Ik wil dat als ik de gegevens in het werkblad 'zaken' zet, dat ik dan bijvoorbeeld met de toetscombi ctrl+q (of een andere combi) dat de gegevens dan alleen van cluster 1 te zien zijn, bij ctrl+w de gegevens van cluster 2 enz. Dit alles zonder extra formules in het werkblad 'zaken' zelf te zetten. Hooguit standaard formules in het werkblad 'namen en clusters' die dus altijd werken op mijn zoekvraag in werkblad 'zaken'. De gegevens voor werkblad 'zaken' wordt altijd in dezelfde layout aangeleverd.

 

[VenA]

Is dit de bedoeling? Selecteer in I1 een cluster.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$1" Then
    ar = Sheets("Naam en cluster").Cells(1).CurrentRegion
    For j = 2 To UBound(ar)
        If LCase(ar(j, 2)) = LCase([I1]) Then c00 = c00 & "|" & ar(j, 1)
    Next j
    Cells(4, 1).CurrentRegion.AutoFilter 2, Array(Split(Mid(c00, 2), "|")), xlFilterValues
End If
End Sub

 

[Roy3001]

@VenA: dit is inderdaad wat ik in gedachte had!

Ik heb nog wel een paar vragen over de VBA. In dit voorbeeld staan de namen in het 'zaken' blad in kolom B, in het originele werkblad staan ze in een andere kolom bijvoorbeeld kolom J (weet ik even niet uit mijn hoofd). Wat moet ik dan in het VBA aanpassen zodat het VBA hetzelfde trucje doet ipv nu op kolom B. Ik denk autofilter op 10 zetten aangezien kolom J de 10 letter is? En ik neem aan dat 'cells 4, 1' aangepast moet worden naar het aantal kolommen van de sheet (bijvoorbeeld 15 kolommen het origineel)
Zou je me het VBA kunnen uitleggen wat het precies doet?

Maar nu ga ik ook nog even voor de finetuning:
- Hoe kan ik zorgen dat de lijst ook nog op alfabet gerangschikt wordt?
- hoe krijg ik ook een optie in de pulldown dat ook weer alle gegevens zichtbaar worden?

 

[VenA]

Met de namen in kolom J en een sortering op naam en de mogelijkheid om alles weer zichtbaar te maken.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$1" Then
    ar = Sheets("Naam en cluster").Cells(1).CurrentRegion
    For j = 2 To UBound(ar)
        If LCase(ar(j, 2)) = LCase([I1]) Then c00 = c00 & "|" & ar(j, 1)
    Next j
    With Cells(4, 1).CurrentRegion
        .Sort Cells(4, 10), , , , , , , True
        If [I1] = "Alles" Then .AutoFilter 10 Else .AutoFilter 10, Array(Split(Mid(c00, 2), "|")), xlFilterValues
    End With
End If
End Sub

Zoek de verschillen

 

[Roy3001]

@VenA: Dit is precies wat ik wil!!!
Graag had ik wel uitleg hoe eea werkt.

Het verschil tussen sorteren op kolom B en J zit hem in:

Next j
Cells(4, 1).CurrentRegion.AutoFilter 2

VERSUS

Next j
With Cells(4, 1).CurrentRegion
.Sort Cells(4, 10), , , , , , , True

Toch?

Kan je me de werking van beide VBA's uitleggen?

 

[VenA]

Dat kan ik wel maar het is beter om even Stop boven tussen de eerste 2 regels te zetten. Zorg ervoor dat je het scherm met lokale variabelen aan hebt staan en wandel met <F8> door de code.

In basis staat er niet veel meer dan:
- Hark alle gegevens van het blad 'Naam en cluster' vanaf A1 en alle aaneengesloten cellen in een array;
- Wandel door deze array vanaf de tweede rij door deze array en kijk of de waarde overeen komt met I1;
- Als dit het geval is dan stop het in de variabele c00 met een nagenoeg nooit gebruikt teken '|';
- Etc.:d

 

[Roy3001]

@VenA: oke, dat gedeelte min of meer gesnapt. Maar nu nog het gedeelte van het sorteren...

 

[VenA]

In google .sort vba.

Het filter gedeelte is veel complexer maar dat begrijp je?

Doorzoek sites als deze https://msdn.microsoft.com/en-us/library/office/ff194068.aspx of gebruik de ingebakken helpfunctie. Het aanreiken van een mogelijke oplossing is leuk het begrijpen vergt enige zelfstudie.