Zoeken in kolom, en optellen

[GurbY{nl}]

Ik heb een vrij ingewikkelde:
Allereerst een kolom waar we een klantcluster in tikken, dat kan er 1 uit 7 zijn. B.v. klant01, klant02 enzovoorts.

Wat ik dan wil, is een ordernummer wat oploopt per klant. Dus stel, ik kies uit keuzelijst klant01, dan moet er in de kolom daarnaast een ordernummer komen wat automatisch wordt opgehoogt.

Alle klantclusters hebben een uniek voorloopnummer, maar dat mag ook anders. Bijvoorbeeld:
klant01: ordernummers beginnende met 110
klant02: ordernummers beginnende met 210
t/m
klant07: ordernummers beginnende met 710.

Als ik nu uit de lijst klant03 kies, wil ik dat het ordernummer automatisch 310001 wordt (bijvoorbeeld.
Die rij vul ik verder in met specificaties.

Dan kies ik uit de volgende rij, opnieuw uit de keuzelijst klant03. Het ordernummer moet dan 310002 worden.
Ga ik naar de volgende rij en kies ik dan klant01, dan moet het ordernummer 110001 worden. Daarna klant03 weer, met als ordernummer 310003.

Met andere woorden: afhankelijk wat er in de eerste kolom als klant wordt gekozen, moet het ordernummer uit de bijbehorende ordernummerreeks van die klant worden opgehoogd.

Ben al een tijd met .ALS en .ZOEK enzovoorts bezig, maar heb em nog niet kunnen oplossen..

[MrAngry]

Ten eerste moet ik even melden dat ik (afhankelijk van de context) niet snap waarom je dit in excel doet. Excel is geen order tracking systeem en niet handig om voor elke order automatische traceerbare ID's te maken met koppelingen naar bestellingen of weet ik wat.
Maar er vanuit gaande dat je er over nagedacht hebt en een goede reden hebt om dit in excel te doen:
=RIGHT(A1;1)&0&0&COUNTIF(Klant;A1)+1

Right pakt het laatste nummer van je klantnummer, &0&0 is om wat random nulletjes of wat je er dan ook tussen wilt toe te voegen, countif telt het aantal keer dat je desbetreffende klant al in je range hebt staan. Die range heb ik Klant genoemd en die moet je definieren met een OFFSET zodat die dynamisch wordt (google).
Nadeel van deze methode is dat het nummer steeds langer wordt, maar dat kan je vast wel opvangen met een slimmigheid waar ik even te lui voor ben (aantal nullen in het midden afhankelijk van de countif maken oid, ik roep maar wat).


Een hele andere aanpak is het met een vba script te doen, die de onderste waarde van uit de lijst haalt en +1 doet, maar dat is meer werk.

Edit: @ pedorus, ja er zijn meerdere manieren om het op te schrijven. De oplossing gaat eigenlijk alleen even om het deel waarin je bedenkt dat je countif kan gebruiken om de nummers per klantengroep te tellen ipv moeilijk doen met lookups en ifs.

[ Voor 10% gewijzigd door MrAngry op 21-04-2010 13:14 ]

[pedorus]

Over welke applicatie gaat deze vraag eigenlijk?
Ik denk dat excel niet de juiste applicatie hiervoor is (desnoods access).

Verder denk ik dat MrAngry meer =RIGHT(A1,1)&TEXT(COUNTIF(OFFSET(A1,0,0,ROW(A1)),A1),"0000") of zoiets bedoelt. Let overigens op dat factuurnummers doorlopend moeten zijn (per jaar).

[GurbY{nl}]

Alvast dank voor de reacties!

Gaat inderdaad over excel; de keuze is meer gebaseerd op de overige toepassing: er wordt per opdracht bijgehouden hoeveel tijd er per soort opdracht is besteed per klant waarna weer diverse rekenfuncties losgelaten worden. De berekeningen zijn het belangrijkste en is hetgeen waar het om draait, vandaar de keuze voor excel, maar degenen die ermee werkten kwamen met de vraag of het ordernummer ook automatisch ingevuld kon worden, vandaar. Ik zal de functies eens gaan testen!

[GurbY{nl}]

Ik heb het even getest maar het lost niet op wat ik bedoel. Ik zal het beter uitleggen.

Stel, we hebben 3 klanten: klant ABCD, klant EFGH en klant IJKL. We beginnen met twee opdrachten binnen te krijgen voor ABCD, daarna 1 voor EFGH, dan weer 1 voor ABCD en dan 1 voor IJKL. Dan komt dit in het sheet te staan:

Klant Ordernummer
ABCD
ABCD
EFGH
ABCD
IJKL

Wat nu de bedoeling is, is dat in de kolom Ordernummer een oplopend nummer per klant wordt gegenereerd. Dat mag met voorlooptekst ABCD, of met een nummerreeks zoals b.v. 110, of andere nummering.
De 1 staat voor klant 1, de 10 voor het jaar. Maar nogmaals: voorloop van de klantnaam (ABCD) zou ook mogen. Als het maar uniek is. Nu moet er dus dit gebeuren:

Klant Ordernummer
ABCD ABCD001 (of, alternatief: 110001)
ABCD ABCD002
EFGH EFGH001
ABCD ABCD003
IJKL IJKL001

Ik hoop dat ik zo iets heb verduidelijk. Als ik met bovenstaande oplossing aan de slag ga, komt het niet goed..

[MrAngry]

Wat snap je precies niet aan de formule? Want dat is precies wat hier gebeurt. De voorloper kan jezelf aanpassen door in het deel voor de COUNTIF aanpassingen te maken. De COUNTIF zorgt voor een oplopend nummer door het aantal keren dat de klant al in de lijst staat te tellen.

[CeesKees]

SOM.ALS zou je ook nog kunnen misbruiken in deze.

[GurbY{nl}]

Ten eerste hebben we de NL versie van Excel, en klakkeloos de formules vertaald overnemen resulteerd in een cel die denkt dat er een hele lange rij tekst staat.

Verder heb ik die van jou geprobeerd, maar als er dan later in de rij opnieuw een klantcode b.v. 210 werd toegevoegd, dan veranderen alle ordernummers met dat voorloopnummer.

Ik heb nu overigens dit ingevuld:
=RECHTS(B3,3)&TEKST(SOM.ALS(VERSCHUIVING(B3,0,0,RIJ(B3)),"0000"

en dat ziet ie dus niet als formule (en doet er dus ook niks mee).
Uiteindelijk moet ie dus tellen hoevaak de tekst ABCD voorkomt in het bereik, en daar dan een ophoging bij doen... ik krijg het maar niet voorelkaar!

Als ik dit doe:
=SOM(LENGTE('1e Kwartaal'!B3:B500)-LENGTE(SUBSTITUEREN('1e Kwartaal'!B3:B500;H2;"")))/LENGTE(H2)
dan geeft ie keurig het aantal keer dat die string voorkomt in het bereik, maar ja, als ik dan dat bereik inclusief nummer doe, dan worden alle occurences met 1 verhoogt ipv alleen de laatste...

EDIT
Vul ik dit in:
=RECHTS('1e Kwartaal'!B3;3)&TEKST(SOM.ALS(VERSCHUIVING('1e Kwartaal'!B3;0;0;RIJ('1e Kwartaal'!B3));'1e Kwartaal'!B3);"0000")
dan krijg ik ABCD000. Dat werkt nu dus wel. Maar nu nog ophogen..

[ Voor 9% gewijzigd door GurbY{nl} op 21-04-2010 15:06 ]

[MrAngry]

OK, we trekken het even uit elkaar.
Eerst de voorloper, daar kom je zelf wel uit, je kan naar het hele klantnummer verwijzen of een deel ervan nemen met functies als LINKS, RECHTS of MID. Extra dingen toevoegen kan met &"extra". Klaar
De rest:
COUNTIF(OFFSET($A$1;0;0;ROW(A1));A1)
In het COUNTIF(Som.ALs) deel, wil je het aantal keer dat je referentie (hier de laatste A1 in het rijtje) voorkomt in de range boven je huidige cel. Dat doen we door naar die range te verwijzen met OFFSET(Verschuiving in NL?). We maken een OFFSET die begint bij $A$1 (Vaste cel verwijzing met $$ tekens, we willen dat hij altijd bij A1 begint) van 0 rows en 0 columns vanaf het beginpunt met een hoogte van ons huidige regelnummer (ROWS(A1)) (dit zal wel RIJ(A1) zijn in NL).
Nou verschuift er niks meer door de vaste begin verwijzing met een variabele hoogte.
Bij mij leidt dit tot precies wat je wilt in excel.

[GurbY{nl}]

Toch krijg ik het niet voorelkaar.. jouw formule ingetikt:

=RECHTS(B3;3)&SOM.ALS(VERSCHUIVING($B$3;0;0;RIJ(B3));B3)

En die geeft, per rij, gewoon "ABCD0".
Dus als er staat

ABCD
ABCD
EFGH
ABCD

dan krijg ik in de kolom bij opdrachtnummer:

ABCD ABCD0
ABCD ABCD0
EFGH EFGH0
ABCD ABCD0

Terwijl het dus ..1, ..2, ..1 en ..3 moesten zijn..

[Reptile209]

Je moet geen SOM.ALS gebruiken, maar AANTAL.ALS:

=RECHTS(B3;4)&AANTAL.ALS($B$3:B3;B3)

Ik vind dat gedoe met VERSCHUIVING geen porum, een half vaste/half dynamische reeks is veel mooier

Resultaat:

ABCD ABCD1
ABCD ABCD2
EFGH EFGH1
ABCD ABCD3

gyedema schreef op woensdag 21 april 2010 @ 14:50:
Ten eerste hebben we de NL versie van Excel, en klakkeloos de formules vertaald overnemen resulteerd in een cel die denkt dat er een hele lange rij tekst staat.
[...]

Let even op het subtiele verschil tussen de puntkomma en de komma als scheidingsteken in de formules (puntkomma voor NL, komma voor EN), dat helpt denk ik wel.

[ Voor 5% gewijzigd door Reptile209 op 21-04-2010 15:40 ]

[MrAngry]

Daar snap ik echt niks van. Hij werkt hier prima en het is echt dezelfde formule. Kan je een stukje van je sheet online zetten?
Ok, vertaal probleem blijkbaar. Zullen we afspreken dat we allemaal gewoon de Engelse gebruiken
edit: Je hebt gelijk zo is ie nog mooier dan met de Offset, ik wist niet dat je hem zo kon opschrijven
edit2: het scheidingsteken is gewoon instelbaar, niet vast per taal.

[ Voor 56% gewijzigd door MrAngry op 21-04-2010 15:42 ]

[Reptile209]

offtopic:
Voor mensen die (ook) moeite hebben met het vinden van de juiste vertaling voor Engelse/Nederlandse formulenamen, er is een geniale toolbar die je als lookup kunt gebruiken: * klik *, ik ben er @work erg blij mee (thuis EN, werk NL).

[GurbY{nl}]

Reptile209 schreef op woensdag 21 april 2010 @ 15:39:
Je moet geen SOM.ALS gebruiken, maar AANTAL.ALS:

=RECHTS(B3;4)&AANTAL.ALS($B$3:B3;B3)

Ik vind dat gedoe met VERSCHUIVING geen porum, een half vaste/half dynamische reeks is veel mooier

Resultaat:

ABCD ABCD1
ABCD ABCD2
EFGH EFGH1
ABCD ABCD3

[...]

Let even op het subtiele verschil tussen de puntkomma en de komma als scheidingsteken in de formules (puntkomma voor NL, komma voor EN), dat helpt denk ik wel.

DEZE WERKT!
Damn.. inderdaad dus gewoon verschil tussen count / som / aantal. Stom van me. Som was Sum.. hoe komt een mens zo dom (**schaamt zich diep**)

Dat van de ; en , had ik inmiddels ook ontdekt. Iedereen vooral bedankt voor het meedenken!!!!

[engelbertus]

[edit] tja... dan is het al opgelost he....

[ Voor 82% gewijzigd door engelbertus op 21-04-2010 15:57 ]