Automatische nummering gestuurd door meerdere kolommen

[swanwil]

Hallo,

Ik ben op zoek naar een formule die in kolom A automatisch nummert afhankelijk van wat er in kolom B, C en D staat.
Staat er iets in kolom B dan wordt dit genummerd met 1,2 enz.
Staat er iets in kolom C dan wordt dit genummerd met 1b, 1c, enz
Staat er iets in kolom C dan wordt dit genummerd met 1a1, 1a2, enz
Als de kolommen B, C en D leeg zijn wordt er niet genummerd en blijft de cel leeg

In bijgevoegd voorbeeld staat in kolom A wat het zou moeten zijn en in kolom F van wat ik er tot nog toe van gemaakt heb.
Probleem is dat iedere kolom vanaf rij 2 telt en niet vanaf waar het zou moeten.

Hopelijk kan iemand me de juiste richting in sturen?Bekijk bijlage nummering voorbeeld.xlsx

groeten, swanwil

 

[Jezet]

Hoi Swanwil,

Lastige opgave, maar niet onmogelijk met matrix-formules.

{=SOMPRODUCT(ISTEKST(B$2:B2)*1)&ALS(ISLEEG(C2);"";INDEX($T$1:$T$99;SOMPRODUCT(ISTEKST(C$2:C2)*(RIJ(C$2:C2)>MAX(ISTEKST(B$2:B2)*(RIJ(B$2:B2))))))&ALS(ISLEEG(D2);"";SOMPRODUCT(ISTEKST(D$2:D2)*(RIJ(D$2:D2)>MAX(ISTEKST(C$2:C2)*(RIJ(C$2:C2)))))))&"."}

Je vergelijkt steeds waar in de vorige kolom al tekst stond, die rijen schakel je uit en telt de rijen met tekst vervolgens op. dat doe je per kolom (dus 3x) vandaar een lange formule.

Groet,
Jeroen

 

[sylvester-ponte]

of deze met vba: (matrixformule) het werkt ook met ander aantal kolommen
zie kolom I

 

[swanwil]

Hallo,

Bedankt voor beide oplossingen.
Maar omdat ik dit aan een collega moet uitleggen die weinig Excel kennis heeft en het in diverse bestanden wil toepassen had ik gehoopt dat dit wel met een "normale"formule te maken was ...

 

[OctaFish]

Als je iets ingewikkelds wilt (en dat is dit) dan krijg je ofwel een ingewikkelde formule, ofwel een UDF (User Defined Function). Bovendien: als het simpel had gekund met een 'normale' formule, had je die waarschijnlijk zelf al wel gevonden Overigens doet de formule volgens mij nog steeds niet wat je wilt...

 

[sylvester-ponte]

Michel, u schrijft:

Overigens doet de formule volgens mij nog steeds niet wat je wilt...

???
bedoelt u het puntje er achter?

 

[swanwil]

Hallo,

Ik ben alweer een stukje verder ...
Ik heb vóór kolom B een kolom toegevoegd waarin het rijnummer afgebeeld wordt.
Daarna heb ik onderstaande formule die de kolommen C en D nu juist nummert.
=ALS(C21<>"";AANTALARG($C$21:$C21);ALS(D21<>"";TEKST.SAMENVOEGEN(AANTALARG($C$21:$C21);"-";VERT.ZOEKEN(AANTALARG(INDIRECT("$D"&VERT.ZOEKEN(AANTALARG($C$21:$C21);$A$21:$B21;2;ONWAAR)):$D21);$R$1:$S$26;2;ONWAAR));""))

Ik moet echter kolom E nog verwerken maar valt nog niet mee ...

Bijgevoegd mijn oplossing tot nu toe (rij 21 t/m 37)Bekijk bijlage nummering voorbeeld 2.xlsx

met vriendelijke groeten, Swanwil

 

[Jezet]

Hoi Swanwil,

De formule even in het bestand gezet.

Bekijk bijlage nummering voorbeeld 2.xlsx

De uitleg:
De formule gestaat uit 3 delen die aan elkaar geplakt worden:
ALS(AANTALARG(C32:E32)=0;"";SOMPRODUCT(ISTEKST(C$21:C32)*1)&
ALS(AANTALARG(D32:E32)=0;"";"-"&INDEX($S$1:$S$99;SOMPRODUCT(ISTEKST(D$21: D32)*(RIJ(D$21: D32)>MAX(ISTEKST(C$21:C32)*(RIJ(C$21:C32))))))&
ALS(ISLEEG(E32);"";"-"&SOMPRODUCT(ISTEKST(E$21:E32)*(RIJ(E$21:E32)>MAX(ISTEKST(D$21: D32)*(RIJ(D$21: D32))))))))

De eerste regel is niet heel spannend, deze telt het aantal regels dat teksten bevat, zou je ook met AANTALARG kunnen doen.

De tweede en derde zijn een beetje hetzelfde, zij het dat de tweede regel een letter moet weergeven.
(ISTEKST(C$21:C32)*(RIJ(C$21:C32)))
Deze regel kijkt in kolom C (de kolom met Omschrijving 1) in welke rij er tekst staat
Het resultaat is {21;0;0;0;0;0;0;0;0;0;0;0} als je dat met F2 en vervolgens F9 controleert.
Het MAX hiervan is 21 dat is duidelijk denk ik.
Stet dat in cel C25 ook tekst had gestaan, dan was het resultaat 25 geweest.
RIJ(D$21: D32) geeft als resultaat {21;22;23;24;25;26;27;28;29;30;31;32}

dan wordt (RIJ(D$21: D32)>MAX(ISTEKST(C$21:C32)*(RIJ(C$21:C32)))) dus {ONWAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR} (alleen RIJ(C21) is niet groter dan 21)

Datzelfde doe je voor de cellen met tekst ISTEKST(D$21: D32) wordt dan {ONWAAR;WAAR;WAAR;ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR}

Vervolgens worden deze twee vermenigvuldigd waarbij WAAR*WAAR gelijk wordt aan 1 en de rest wordt 0
Dus ISTEKST(D$21: D32)*(RIJ(D$21: D32)>MAX(ISTEKST(C$21:C32)*(RIJ(C$21:C32))) wordt {0;1;1;0;0;1;1;0;0;0;0;0}

Tel je dat bij elkaar op in rij32 dan is de som dus 4 en dat gebruik je dan weer om de letter te bepalen.

Hoop dat het een beetje te volgen is.

Groet,
Jeroen

 

[swanwil]

Hallo Jezet,

Ik kan het niet helemaal volgen maar de formule werkt perfect!
Bedankt!

Blijkbaar toch te complex om zonder matrix-formule op te lossen...
Iedereen bedankt voor zijn bijdrage !

groeten Swanwil

 

[sylvester-ponte]

in de gele cellen staat een udf zonder matrix formule hij gebruikt wel een nummer als invoer
als je van die nummerinvoer af wil is dat wel aan te passen .

 

[swanwil]

Hallo Sylvester,

Deze oplossing ziet er ook gelikt uit!
Ik moet wel even bestuderen hoe het werkt want ben niet thuis in vba, maar ga ik zeker gebruiken...

groeten Swanwil

 

[sylvester-ponte]

deze udf berekend steeds de laatste code van het gebied dat je opgeeft:
zie gele cellen