Google sheets - voertuig en boekingsnummer koppelen

[Niekleair]

Het bedrijf waar ik werk gebruikt Google Sheets voor het bijhouden van een boekingskalender.
Naast de boekingskalender hebben we een lijst voor elke vestiging met de boekingen die per dag vertrekken. Deze lijst wordt automatisch gegenereerd, ongeveer 2 weken vooruit maar wijzingingen moeten handmatig doorgevoerd worden.

Hieronder een voorbeeld van hoe de kalender er uit ziet (namen zijn gemaskeerd voor privacyredenen).



Ik zie dat de kolom letters weggevallen zijn. De 'kalender' begint bij kolom G.

Er zijn meerdere van deze kalenders voor verschillende categorieen.
De voertuignummers staan in het voorbeeld in Kolom D, in sommige gevallen kan dit ook Kolom E zijn.

Iedere boeking is een aantal samengevoegde (is dat het Nederlandse woord voor merge?) cellen en de rij waarin deze boeking zich bevind, bepaald welk voertuig is toegewezen.

Nu ben ik op zoek naar een formule waarmee ik automatisch het boekingsnummer (de 2024xxxxx codes) aan een voertuignummer kan koppelen.

Simpelgezegd, als het boekingsnummer voorkomt in de kolommen G - NH, bepaal rijnummer (n) en geef de waarde in cel D(n)

Dit is volgens mij een vrij basale functionaliteit van iedere spreadsheet; maar het lukt mij niet om de formule te vinden.

Om meerdere tabbladen te doorzoeken, kan ik waarschijnlijk met "IF" uit de voeten; dat is voor nu buiten de scope van mijn vraag.

Ik gebruik:
Google Sheets
Excel (als dit alleen in Excel werkt, dan kan ik mijn sheets exporteren naar excel)

[pagani]

Het sheetsequivalent van hlookup of vlookup. Ze heten er ook hetzelfde, net even gecontroleerd.

[ Voor 37% gewijzigd door pagani op 06-06-2024 18:30 ]

[Niekleair]

Bedankt.

IK dacht dat VLookup maar 1 kolom tegelijkertijd kon opzoeken; maar ik was waarschijnlijk in de war met XLookup.

VLookup werkt alleen binnen 1 tabel; het neemt de waarde in de eerste kolom en zoekt de waarde in kolom (N) erbij. En VLookup kan alleen maar naar "rechts" kijken. Ik heb een formule nodig die omgekeerd werkt.

[ Voor 46% gewijzigd door Niekleair op 06-06-2024 19:14 ]

[g0tanks]

pagani schreef op donderdag 6 juni 2024 @ 18:29:
Het sheetsequivalent van hlookup of vlookup. Ze heten er ook hetzelfde, net even gecontroleerd.

Volgens mij is VLOOKUP of XLOOKUP hier niet de juiste functie. Het zou ook niet nodig moeten zijn aangezien alle informatie op dezelfde regel staat.

@Niekleair dat het allemaal samengevoegde cellen zijn maakt het nogal een zooitje. Ik zou eerst opzoek gaan naar een formule die per rij bepaalt wat het boekingsnummer is.

[Niekleair]

Ik heb het uiteindelijk opgelost door de sheet in te laden in een nieuw tab; en er 'Unique' op losgelaten:

=UNIQUE(IMPORTRANGE("[sheet key]","[sheet range]"&row(A1)+47&":NH"&row(A1)+47),true)

Deze tab noem ik UniqueValues.
Hiermee is iedere sticker in een kolom geplaatst. Dit maakt de data al een stuk meer handelbaar, uiteindelijk waren er iets van 20 kolommen. Ik liet een stuk of 10 extra rijen over voor ieder tabblad en plaatste alles op een pagina.

Maar de string in iedere kolom bevat een hoop data die ik niet wil. Gelukkig bevind het boekingsnummer zich altijd op dezelfde plaats in de 'stickers'. Dus met een simpel 'MID' commando, kon ik het opschonen. Voor de overzichtelijkheid laad ik dit in in een nieuw tabblad, die ik Trimmedvalues noem:

=MID(UniqueValues!D2,9,9)
Nu heb ik een tabpagina met de voertuignummers en de boekingsnummers die bij die voertuigen horen.

Om nu een boekingsnummer aan een voertuignummer te koppelen, is het een kwestie van een paar geneste XLookups:

code:

=IF(
    REGEXMATCH(
        B1,"Proceed"
        ),XLOOKUP(
            TRIM(A1),Trimmedvalues!D:D,Trimmedvalues!A:A,XLOOKUP(
                TRIM(A1),Trimmedvalues!E:E,Trimmedvalues!A:A,XLOOKUP(
                    TRIM(A1),Trimmedvalues!F:F,Trimmedvalues!A:A,XLOOKUP(
[...]
                                                                            TRIM(A1),Trimmedvalues!T:T,Trimmedvalues!A:A,XLOOKUP(
                                                                                TRIM(A1),Trimmedvalues!U:U,Trimmedvalues!A:A,"Not Found"
                                                                            )
                                                                        )
                                                                    )
 [...]

            )
        ),
    IF(
        REGEXMATCH(
            B1,"Does not exist"
        ),"Does not exist",
        "CHECK"
    )
)

De Regexmatch is om dubbele entries (bijvoorbeeld als een boeking pech krijgt, dan geven we de boeking een nieuwe unit) eruit te filteren.

Het is een beetje flaky, maar de meeste tijd werkt het zoals ik wil.

Bedankt dat jullie me de goede oplossingsrichting uit stuurden!

[sig69]

Plannen in Excel/Sheets is in het begin misschien handig maar uiteindelijk loopt het altijd uit de hand tot dit soort onhandelbare meuk. Tijd voor een echte planning tool.

[Niekleair]

Helemaal mee eens; helaas wordt onze ICT afdeling kapotbezuinigd en ik heb vooralsnog de tijd niet om er vol in te duiken.