Inhoud D controleren in A en op dezelfde hoogte plaatsen

[collapse]

Hoi,

Voor een mooi overzicht ben ik bezig een uitdraai uit een AD group in een Excel lijst te verwerken en controleren met de uitdraai van een lijst uit een RSA Security Console.

Hierbij wil ik controleren of de gebruikers die lid zijn van een bepaalde AD group ook in de RSA Security Console lijst voorkomen.

In bijgevoegd bestand zie je een voorbeeld van het bestand waarmee ik aan de slag ben gegaan (simpel dingetje).
http://www.mijnbestand.nl/Bestand-WJ6MB7SN3JP6.xlsx (sorry kreeg het bestand niet via de upload functie van de website zelf toegevoegd, misschien dat iemand dat even voor mij kan doen?)

Nu is de lijst met de uitdraai van alle members van de AD Group (zie Kolom A) langer dan die van de gebruikerslijst uit RSA (zie Kolom D).
De namen staan ook niet op dezelfde rij.

Ik ben voor dit overzicht opzoek naar een manier om hetgeen in Lijst D voorkomt (alleen de gebruikersnaam) te controleren of dat in Kolom A voorkomt.
Zo ja, zet dan hetgeen in Kolom D staat op dezelfde regel als dat het voorkomt in Kolom A, zonder dat hetgeen daar stond ingevuld kwijt is!

Omdat Kolom A meer gegevens bevat moeten er dus lege cellen onstaan in Kolom D, gezien sommige daar dus niet in voorkomen.

Ik hoop dat jullie begrijpen wat ik bedoel.

Wat ik als bijna vergelijkbare vraag ben tegen gekomen is dit:
http://www.helpmij.nl/forum/showthread.php/607963-Sorteren-en-op-gelijke-rij-plaatsen

Alleen kwam ik de formule niet in het bestand tegen en ben ik eigenlijk best een leek op gebied van VBA of formules maken in Excel.

Alvast bedankt voor jullie hulp!

 

[Wigi]

Zoiets bijvoorbeeld.

Er zijn 2 extra kolommen bijgekomen.

 

[collapse]

Sorry, Ik zou eigenlijk die twee kolommen niet erbij willen hebben gezien dit erg onoverzichtelijk en onduidelijk wordt in het de grote lijst die ik heb staan om te verwerken.

Op dit moment ben ik ook zelf aan het zoeken naar de oplossing middels een macro, volgens mij behoed een macro namelijk de toevoeging van extra kolommen waar je de formules in kwijt moet kunnen.

Wel erg bedankt voor je reactie en meedenken naar een oplossing!

Ik heb in de onderstaande link wel de daaronder weergegeven macro gevonden, maar hij plaatst dan het resultaat in een extra kolom, maar zoals ik al aangaf heb ik liever dat hij de wijzigingen aanpast in Kolom D van het bestand uit mijn eerste post.

Link: http://support.microsoft.com/kb/213367/nl

Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Stel CompareRange in op het bereik waarmee u de selectie
    ' wilt vergelijken.
    Set CompareRange = Range("C1:C5")
    ' Opmerking Als het vergelijkingsbereik zich in een andere werkmap
    ' of een ander werkbalk bevindt, gebruikt u de volgende syntaxis.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' Doorloop elke cel in de selectie en vergelijk deze met
    ' elke cel in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub

 

[Wigi]

Maak dan eens een bestandje aan met het gewenste eindresultaat.

Extra kolommen kan je altijd verbergen indien nodig/gewenst.

 

[collapse]

http://www.mijnbestand.nl/Bestand-OH7XDIFPKMBK.xlsx

In het bovenstaand bestand heb ik mijn lijstje in werkblad 1 en het resultaat hoe het moet zijn is in werkblad 2 te zien.

Hopelijk is het mogelijk via een macro zodat ik die kan toepassen op meerdere bestanden.

Als ik een stukje uit de macro die ik eerder heb geplaatst aanpas in onderstaande, dan had ik gedacht er te zijn, maar niets is minder waar, er gebeurd niets namelijk.

If x = y Then x.Offset(0, 3) = x

 

[WHER]

Probeer het eens met het bijgevoegde bestand.

 

[Warme bakkertje]

Nog eentje om te testen

Sub tst()
Dim sq As Variant, fRow As Integer
    With Sheets("Uitdraailijsten")
    sq = .Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row)
    Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row).ClearContents
        For i = 1 To UBound(sq)
            On Error Resume Next
            fRow = .Columns(1).Find(sq(i, 1), , xlValues, xlPart).Row
            .Cells(fRow, 4) = sq(i, 1)
        Next
    End With
End Sub

 

[collapse]

Beste WHER en Warme bakkertje,

@WHER, jou bestand doet precies wat ik wil, alleen als ik dit wil toepassen in mijn bestand krijg ik de button niet tevoorschijn na de macro uit VBE te halen.
Wanneer ik mijn gegevens in jou bestand kopieer maakt hij heel Kolom D leeg, ook krijg ik het niet voor elkaar die button te verplaatsen.

@Warme bakkertje, de code die ik van jou heb gekregen heb ik aangepast naar het tabblad wat ik in mijn bestand heb staan en vervolgens een button met de macro gemaakt, hij werkt perfect, waarvoor dank.

Nu kan ik hem verder verwerken met andere condities, totalen en subtotalen etc.

Super bedankt!

 

[collapse]

Hmm helaas. Toch klopt het niet geheel nu met de code die ik gebruik van Warme bakkertje.

Wanneer ik namelijk in Kolom D gegevens heb staan die niet in Kolom A voorkomen, wat doet hij dan met die gegevens?
In de praktijk in mijn bestand, doet hij er willekeurig iets mee volgens mij en verdwijnen er zelfs een aantal gegevens.

Zal even het bestand waarin ik nu werk bijvoegen in een linkje:
http://www.mijnbestand.nl/Bestand-RGTGICXZRP6F.xlsm

Druk dan maar eens op de button en zie dat het totaal tokens minder worden, en waar bij r_roshni in Kolom A staat vult hij in Kolom D nu r_lalara in en is r_roshni die voorkomt in Kolom D en A nergens meer terug te vinden, terwijl r_lalara niet in Kolom A voorkomt.

 

[Warme bakkertje]

Sub test()
Dim cl As Range, str As String, i As Integer
With Sheets("ADGroupMembers10142011")
    str = Join(WorksheetFunction.Transpose(.Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row).Value), "|")
    .Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row).ClearContents
    For Each cl In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        i = InStr(1, cl, "(", vbBinaryCompare)
        If i > 0 Then
            If InStr(1, str, Left(cl, i - 2), vbBinaryCompare) > 0 Then
                cl.Offset(0, 3) = Left(cl, i - 2)
            End If
       End If
    Next
End With
End Sub

 

[WHER]

Verderbordurend op de code hierboven.
De twee speciale gevallen (gele highlight, komen wel voor in kolom D maar niet in kolom A) worden nu naar kolom E verplaatst.
De gevallen waar er in kolom A geen "(" en ")" voorkomen, zouden nu ook goed moeten gaan.

 

[collapse]

Hoi WHER, thanks! ik heb de code in mijn eigen bestand gekopieerd. Hij past nu weliswaar niet de gele highlight toe, maar dat is niet erg want als die is toegepast en ik verwerk daarna de lijst, dan blijft de gele highlight staan namelijk (getest in jou bestand).

Het is niet helemaal waterdicht nog als ik namelijk iets laat staan in Kolom E dan haalt hij dat leeg wanneer ik de macro uitvoer.
Maar ik ben hier al heel erg blij mee!

 

[WHER]

De gele highlight had ik manueel toegevoegd (om iets duidelijker te maken).
Indien je niet wil dat kolom E leeggemaakt wordt, verwijder dan deze zin uit de code:

 .Range("E2:E" & .Cells(Rows.Count, 5).End(xlUp).Row).ClearContents

 

[collapse]

Wederom dank, dat stukje code heb ik nu weggehaald!

Wellicht is mijn volgende vraag handiger om hier te stellen gezien ik die samen wil laten werken met de Verwerk macro van WHER.

Ik ben opzoek naar een makkelijke manier om gegevens toe te voegen in dit bestand, nu is het niet echt bewerkelijk en gebruikersvriendelijk.
Maar er zijn 3 situaties waarin je gegevens kunt invullen:
1: Alleen iets invullen in Kolom A
2: Alleen iets invullen in Kolom D
3: Gegevens invullen in zowel Kolom A en D

Is er een manier waarbij ik bijvoorbeeld rij 2 kan gebruiken om gegevens in te voeren, die regel is dus bij het opstarten van het bestand altijd leeg.
Zodra ik de macro uitvoer moet hij rij 2 dus leegmaken en verwerken in de kolommen waarbij automatisch Kolom A alfabetisch (ascending) gesorteerd is.

Ik had een macro gevonden om Kolom A te sorteren, maar die krijg ik niet aan de praat.

Sub SorterenKolomA()
Dim strFileName As String
Dim RowStart As Long, RowEnd As Long, temp As Long

strFileName = Worksheets("VPNusers & RSA Token gebruikers").Cells(5, 2)
RowStart = Worksheets("VPNusers & RSA Token gebruikers").Cells(1, 2)
RowEnd = Worksheets("VPNusers & RSA Token gebruikers").Cells(2, 2)

If RowStart > RowEnd Then
temp = RowEnd
RowEnd = RowStart
RowStart = temp
End If

With Worksheets(strFileName)
.Range(.Rows(RowStart), .Rows(RowEnd)).Sort Key1:=.Range("A" & RowStart), _
Order1:=xlAscending, Header:=xlNo

End With
End Sub

Kan ik tevens twee codes in een module stoppen en die tegelijk laten uitvoeren of zal dat problemen opleveren?

Laatste versie van mijn bestand:
http://www.mijnbestand.nl/Bestand-GH7XILUTOEVO.xlsm

 

[collapse]

Wellicht dat het ook mogelijk is om een query richting de Active Directory te sturen en het resultaat in Kolom a te plaatsen (mag ook alleen gebruikersnaam of alleen display name/full name zijn, in plaats van de structuur die ik nu heb gebruikt)

Heb wel gezocht naar 'excel query to active directory' in google maar kwam daar allerlei ingewikkelde dingen tegen met 'function' codes, maar snap niet hoe ik die dan kan toepassen in een macro.

 

[Wigi]

Heb wel gezocht naar 'excel query to active directory' in google maar kwam daar allerlei ingewikkelde dingen tegen met 'function' codes, maar snap niet hoe ik die dan kan toepassen in een macro.

Dan laat je dat best zo. Het heeft weinig zin dingen te gaan doen die je helemaal niet snapt.