Met VLOOKUP de hoogste versienummer tonen van bepaald document

[tomme]

Hallo,
Ik ben al een tijd bezig om het volgende voor elkaar te krijgen: Uit een statische lijst die steeds groter wordt wil ik de hoogste versie nummer/letter terug halen van een (bepaald) document.. Het wordt vast duidelijker met een versimpeld voorbeeld van mijn excel document:

Kolom A                    Kolom B 
Naam:                      Versie nummer/letter:

Control philosophy	        4
Layout		                B
P&ID		                A
Control philosophy	        5
P&ID		                C
Proces calculations	        1
Proces calculations	        4

Ik zou uit deze lijst automatisch de hoogste versienummer van bijvoorbeeld de Control philosophy willen tonen in een aparte cel. (zo ook de hoogste versie van de P&ID etc.)

Door het gebruik van de VLOOKUP functie wordt dan versienummer 4 getoond, terwijl er in de lijst ook een Control philosophy versie 5 is.

=VLOOKUP(''Control philosophy'';A1:B8;2)

Hoe pas ik mijn code aan om er voor te zorgen dat er het hoogste versienummer (die in kolom B staat) van de control philosophy wordt getoond?

Groeten,
Tom

 

[Tilly1987]

Vlookup properties

Hallo tomme,

De vlookup functie heeft een aantal opmerkingen als je in de help omschrijving kijkt van de functie. Hier staat vermeldt dat de gegevens "ascending" gesorteerd dienen te zijn, anders kunnen er onjuiste zoekresultaten getoond worden.

Als ik het echter goed lees is de data statisch, dus niet te sorteren...?(<== Klopt dit?) Als de sortering niet plaats kan vinden, dan kan de Vlookup functie niet gebruikt worden.

Ik heb echter een aantal vragen. Kan de hoogste waarde meerdere malen voorkomen? Zo ja, dient er een specifieke waarde gevonden te worden of een van de hoogste waarden?

Met vriendelijke groet,

Tilly

 

[tomme]

Hallo Tilly,

Het klopt dat de data niet te sorteren valt. Dmv het invullen van een userform ontstaat er een nieuwe rij, van een aantal kolommen breed. Deze nieuwe rij bevat informatie over een document dat klaar/af is (in mijn versimpelde voorbeeld zie je alleen de naam en het versie nummer). Omdat er over de tijd uiteindelijk wel 12 versies (ongesorteerd) van bv. het document ''Control philosophy'' inkomen te staan hebben ze allemaal een eigen versie nummer.

Mijn vraag is hoe ik dmv een functie de recentste (lees: hoogste) versie van bv. Control philosophy uit deze lange lijst filter en presenteer in een ander tabel. De bedoeling is dat er in een oogopslag gezien kan worden welk document af is. Als bv. versie 6 van de Control philosophy af is en ingevuld wordt m.b.v. het userform moet er in de ''Actual status'' tabel zien dat versie nummer 6 van Control philosophy de laatste/recentste is.

Uitgaande van mijn voorbeeld moet deze ''actual status'' tabel dus dit aangeven (maar hoe?

(actual status tabel)
Layout		                C
Control philosophy	        5
P&ID		                C
Proces calculations	        3

(lijst met alle documenten)
Kolom A                    Kolom B 
Document naam:       Versie nummer/letter:

Layout		                A
Control philosophy	        3
Proces calculations	        1
Layout		                B
Control philosophy	        4
P&ID		                A
Control philosophy	        5
P&ID		                B
Layout		                C
Proces calculations	        2
Proces calculations	        3
P&ID		                C

Ja het gebeurt dat 2 de zelfde versie nummers voorkomen alleen zijn ze van verschillende documenten. bv. Proces calculation versie 3 en Control philosophy versie 3. als dit de recentste zijn dienen ze beide getoond te worden in de actual status tabel.

ik hoop een verduidelijking te hebben gegeven en je vragen zo goed mogelijk te hebben beantwoord. Als je een idee hebt hoe ik mn probleem kan oplossen zou dat geweldig zijn!!:thumb:
alvast bedankt Tilly

 

[Tilly1987]

Mogelijke oplossing

Hallo tomme,

Ik hoop dat onderstaande code in ieder geval een opzetje kan geven naar het gewenste effect. Is denk ik niet optimaal, maar volgens mij doet het wat jij wenst. Weet alleen niet of je het in deze vorm direct kan gebruiken. Het werkt echter met cijfers en nummers (Wat je wilt neem ik aan ).

Hopelijk kun je er wat mee. Ik ga nu eerst slapen .

Met vriendelijke groet,

Tilly

Sub zoektest()
Dim werkrange As Range
Dim rg As Range
Dim version As Variant
Dim High_version_container As Range
Dim name As String

'Voer zoek criteria in
name = "P&ID"
'Stel de zoekrange in (Dit kan genuanceerder, maar had haast met typen :P)
Set werkrange = Range("A1").CurrentRegion

'Zoek naar de zoek criteria in de werkrange
Set rg = werkrange.Find(What:=name, _
                        After:=werkrange.Cells(werkrange.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=True)

Do
    'Als het criteria niet bestaat in de range houdt het op.
    If rg Is Nothing Then Exit Do
    'Als hij bestaat versienummer/letter onthouden en reverentie waar de hoogste waarde was.
    If version < rg.Offset(0, 1).Value Then
        version = rg.Offset(0, 1).Value     'bewaar versie
        Set High_version_container = rg     'bewaar range hoogste waarde zoek criteria voor latere verwijzing
    End If
    'Zoek naar evtuele volgende range die aan zoek criteria voldoet
    Set rg = werkrange.FindNext(rg)
    'Als de range bestaat verder zoeken en nogmaals controleren of versie hoger is.
    'Loop stopt als er niks is gevonden of als de hoogste waarde nogmaals als zoekwaarde tevoorschijn komt
Loop While Not rg Is Nothing And rg.Address <> High_version_container.Address

'Ter informatie wat de functie gevonden heeft.
MsgBox "Cel : " & High_version_container.AddressLocal & " dient het hoogste nr van " & name & " weer te geven"

End Sub

 

[tomme]

Hey Tilly, bedankt voor de code! heb er samen met mn pa naar gekeken die een beetje in visual basic thuis is.. heb op het moment nog niet helemaal door hoe ie werkt en waar en hoe de code er precies in moet komen te staan. De code is iets uitgebreider/ingewikkelder dan ik tot nu toe heb gebruikt in mijn excel scripts. Ben net naar de bieb geweest en heb een VBA boek geleend en ben van plan om het te doorgronden en deze code te implementeren in mijn sheet. Heel erg bedankt voor de opzet! :thumb: tnx
Gr,
Tom

 

[Tilly1987]

Zelfde trucje alleen nu als functie

tomme,

graag gedaan. Had zin om een functie te schrijven, dacht laat ik dat eens voor deze doen. Wellicht kun je hem zo beter gebruiken in je programma. Nu kun je de Version_Search Functie oproepen om een range in te stellen.

Veel succes met de VBA boeken. Kun je erg veel van leren en waarschijnlijk een hoop dingen verbeteren die hier geprogrammeerd staan .

Met vriendelijke groet,

Tilly

Function Version_Search(SearchRange As Range, SearchCriteria As String, Optional Version_Colom_Offset As Byte = 1) As Range
Dim tRng As Range
Dim version As Variant
Dim HighRng As Range

'Zoek in de opgegeven range naar zoekcriterium
Set tRng = SearchRange.Find(What:=SearchCriteria, _
                            After:=SearchRange.Cells(SearchRange.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=True)

Do
    'Als het criterium niet bestaat in de range houdt het op.
    If tRng Is Nothing Then Exit Do
    'Als hij bestaat versienummer/letter onthouden en reverentie waar de hoogste waarde was.
    'Er wordt aangenomen dat het versienummer standaard een kolom rechts van de zoekrange bevindt
    'Zie ook wel Version_Colom_Offset = 1, (kan anders opgegeven worden waar nodig)
    If version < tRng.Offset(0, Version_Colom_Offset).Value Then
        version = tRng.Offset(0, Version_Colom_Offset).Value        'bewaar versie
        Set HighRng = tRng                                          ' bewaar range hoogste waarde zoek criteria voor latere verwijzing
    End If
    
    'Zoek naar volgende range die aan zoek criteria voldoet
    Set tRng = SearchRange.FindNext(tRng)
    'Als de range bestaat verder zoeken en nogmaals controleren of versie hoger is.
    'Loop stopt als er niks is gevonden of als de gevonden hoge waarde nogmaals als zoekwaarde tevoorschijn komt
Loop While Not tRng Is Nothing And tRng.Address <> HighRng.Address

'Geeft de hoogste waarde terug als range
If Not HighRng Is Nothing Then Set Version_Search = HighRng

End Function

Sub Zoek_Hoogste_Versies()
Dim wb As Workbook
Dim ws As Worksheet
Dim Range_Namen As Range
Dim Highest_version As Range
Dim SearchFor As String

Set wb = ThisWorkbook
'Stel hier de juiste waarden in, welke sheet dient doorzocht te worden
Set ws = wb.Worksheets([COLOR="red"]"Sheet1"[/COLOR])
'Stel de range in wat doorzocht moet worden.
Set Range_Namen = ws.Range([COLOR="red"]"A2:A10"[/COLOR])

On Error Resume Next    'Als Highest_version niet ingesteld wordt door de functie, kan er geen offset genomen worden

'Nu is het simpelweg Zoekrange en Criterium opgegeven (optioneel de offset van versie nr t.o.v. naam/zoekcriteria
'Standaard wordt aangenomen dat het een kolom rechts van de zoekcriteria ligt (kan anders opgegeven worden).
SearchFor = "Control philosophy"
Set Highest_version = Version_Search(Range_Namen, SearchFor)
MsgBox "Zoektocht naar : " & SearchFor & " levert " & Highest_version.Offset(0, 1) & " als hoogste versienummer op."

'Herhaal kunstje met ander criteria.
SearchFor = "Proces calculations"
Set Highest_version = Version_Search(Range_Namen, SearchFor)
MsgBox "Zoektocht naar : " & SearchFor & " levert " & Highest_version.Offset(0, 1) & " als hoogste versienummer op."

'enz.
SearchFor = "Layout"
Set Highest_version = Version_Search(Range_Namen, SearchFor)
MsgBox "Zoektocht naar : " & SearchFor & " levert " & Highest_version.Offset(0, 1) & " als hoogste versienummer op."

'enz.
SearchFor = "PI&D"
Set Highest_version = Version_Search(Range_Namen, SearchFor)
MsgBox "Zoektocht naar : " & SearchFor & " levert " & Highest_version.Offset(0, 1) & " als hoogste versienummer op."

End Sub