"Uitvullen" van data naar 't rij-nummer waar ze bij horen (data in Excel

[Excelaration]

Hallo,
Om m'n Excel handelingen een beetje te 'excelaraten' (accelerate) zoek ik naar een manier om (al vóór-gesorteerde) data met 'een druk op de knop' (of een macro..?) te verdelen naar het rij-nummer waar ze bij horen. Dit klinkt misschien onduidelijk, maar het volgende eenvoudige voorbeeld (ATTACHED, ook met uitgebreider voorbeeld) spreekt hopelijk voor zichzelf.
De volgende data wil ik 'uitvullen' (ze zijn al gesorteerd, dat is het probleem niet) naar het rij-nummer waar ze bij horen. Dus:
Rij Data
1 1
2 3
3 4
4 7
5
6
7
Bovenstaande wil ik omzetten naar:
Rij Data
1 1
2
3 3
4 4
5
6
7 7
Met de gewone sorteerfunctie van Excel lukt dit me niet.
Aangezien ik grote hoeveelheden data heb (tot 900 rijen) waarbij ik dan (in de 2e kolom) ca 200 data heb die 'verspreid' moeten worden over die 900 rijen in de 1e kolom (dus eigenlijk gewoon verdelen over het rij-nummer dat Excel zelf helemaal links al geeft), zou ik geholpen zijn als deze (zich herhalende) handeling niet steeds één voor één met de hand hoeft te worden uitgevoerd. Je zou zeggen dat hier een oplossing voor is..
Thanx ! Excelerator

 

[Cobbe]

Hier is een mogelijkheid maar niet voor de dubbels.
Hoe bepaal je dat 13 op rij 13 moet komen en 13 op rij 14?

Cobbe

PS: Ik heb ook een oplossing voor de dubbels. Wel met hulpkolommen en de waarden komen achter elkaar op de juiste rij .
Zeg het maar.

 

[Excelaration]

mooie oplossing maar 1) lukt nog niet om toe te passen en 2) snap niet wat er gebeurt

Hallo Cobbe, allereerst bedankt voor de moeite!
Een prachtige formulie, die blijkbaar ook werkt! Dus bedankt.

ECHTER, 't lukt mij nog niet er mee te werken; ik heb twee grote problemen:
1) allereerst zou ik uiteraard graag snappen waar ik kan leren om deze formule te maken, en wat deze formule eigenlijk voor opdracht geeft.
2) belangrijker nog is dat het natuurlijk al heel mooi zou zijn om met jouw formule te kunnen werken, ook als ik niet snap wat ie precies voor 'opdracht' geeft. Maar dat lukt me nog niet! Het volgende gaat fout namelijk als ik jouw formule copieer naar een andere cel (hetgeen toch nodig is om met deze formule in een andere data set te gaan werken):
* De formulie blijft WEL precies hetzelfde als dat ie in eerst in DIE cel stond, MAAR DESALNIETEMIN komt er de foutmelding "#NAAM?". De helpinfo voor deze fout zegt onder andere dat ik het Analysis Toolpak moet installeren, hetgeen ik dus gedaan heb. Het blijft daarna toch gaan. Dus er gaat iets anders onduidelijks fout..
Ik ATTACH voorbeeld 2 weer, zie de geel gemarkeerde velden (rij 4,9,13) waar deze fout optreed.
Alle advies is welkom.

Wat betreft "de dubbels" - ik was vergeten om die uit het voorbeeld te halen. Dat leek me namelijk te ingewikkeld worden (het komt inderdaad wel voor in mijn data sets).
Als je daar ook een oplossing voor zou hebben, zou dat helemaal perfect zijn! - als de waarden achter elkaar op de juiste rij komen, is dat precies wat ik wil..

Hopelijk gaat 't me nog duidelijk worden hoe dan wel met jouw formule te werken na nog wat verder advies.

Thanx, Excelarator

 

[Cobbe]

Bij mij zit er uiteraard geen fout in.
Ik heb in je bestand in de eerste rij de formule op tekst gezet en er de nodige uitleg bij gedaan.
Tenminste hoe ik denk ze te moeten uitleggen.

Als je dit doorgrondt kan je deze formule ook toepassen in een ander bestand.

Die dubbels zal ik dan toch maar even laten wachten totdat je die kunt implementeren.

Succes, Cobbe

 

[Excelaration]

Does not get it, yet...

Hallo Cobbe,
Thanks weer, ook voor de uitleg!
Met jouw uitleg begin nu een beetje te begrijpen wat de formule behelst / wat de bedoeling is van de formule.
Ik geloof uiteraard geheel dat er geen fout zit in de formule - bij jou werkt ie ook. ECHTER, bij mij gaat het meteen fout, whatever ik ook verander.
TEVENS valt het me op dat je in jouw "uitleg/instructie-regel" een andere formule schrijft dan er staat in de cellen van de actuele tabel (kolom J); namelijk:
De volgende formule staat er bijvoorbeeld in Cel J35 (Engelse versie Excel, vanavond thuis kan ik de Nederlandse versie copieren):
=_xlfn.IFERROR(IF(ROW()=VLOOKUP($I35;$B:$B;1;0);VLOOKUP($I35;$B:$B;1;0);"");"")
Deze werkt dus.
Maar in jouw instructie-formule staat:
=ALS.FOUT(ALS(RIJ()=VERT.ZOEKEN($I1;$B:$B;1;0);VERT.ZOEKEN($I1;$B:$B;1;0);"");"")
Zeker het begin van de laatste formule is anders.

Belangrijker nog is dat ik nog steeds niks kan veranderen met de werkzame formule van jou (de 1e hierboven dus), of het gaat meteen al fout en de foutmelding #NAME? komt.
Ik kan dus bijvoorbeel ook niet een waarde in kolom B weghalen, want dan komt in ALLE cellen van kolom J de #NAME? foutmelding te staan. En die foutmelding blijft staan OOK als ik de bewuste weggehaalde waarde weer PRECIES zo TERUG PLAATS. Vreemd niet waar(?)

ZOU DIT misschien ALLEMAAL KUNNEN LIGGEN aan mijn Excel instellingen? Maar dat zou dan betekenen dat de Excel op twee verschillende PC's niet goed is voor dit soort formules. Tevens heb ik op beide PC's de 'Add-ins" (onder Tools) allemaal geladen/geactiveerd. Dus het lijkt me toch onwaarschijnlijk dat het aan de Excel instelling ligt..

Is het voor jou eventueel mogelijk om mij een mailtje te sturen op: jan.k@live.nl ? Ik heb namelijk nog een Excel-related vraag, die wel wat erg ver voert voor dit forum, en misschien kunnen we een dealtje sluiten als je snapt wat ik bedoel (de oplossing van deze vragen is erg relevant voor mijn huidige werk; ik ben bio-medisch onderzoeker). Ik heb nogal het idee dat je een behoorlijke Excel deskundige bent, en die zoek ik!

Groet, Jan 'Excelerator' (nou ja, nog niet bepaald)

 

[Cobbe]

Ik gebruik maar één formule die telt voor de hele kolom J
Natuurlijk wijzigt $I1 naar $i2 naar $I3 enz....., voor de rest is het overal idem.
Deze formule heeft niks te maken met welke invoegtoepassing dan ook. Dus moet werken met alle invoegtoepassingen uitgeschakeld.
Staan de waarden(rijnummers) bij u in kolom B vanf rij 1 en moet deze waarde in kolom J vanaf rij 1, komen te staan?

Als dat zo is moet het perfect lopen.
Indien niet sein dan nog maar even dan stuur ik u een mailtje.

Succes, Cobbe

 

[popipipo]

Een kortere weg naar Rome:

eng:  =IF(COUNTIF(B:B;I1)>0;I1;"")
ned:  =als(aantal.als(B:B;I1)>0;I1;"")

Of kom ik nu niet in Rome aan.

 

[Excelaration]

In Rome maar wil nog verder reizen...

Zeer bedankt Popipipo!
Jouw formule werkt goed, zonder foutmeldingen! Dus ben inmiddels in Rome...
Maar eigenlijk wil ik de formule toepassen niet alleen om één rijtje waardes uit te verdelen over de rijen (zoals je nu met jouw formule =ALS(AANTAL.ALS(B:B;E1)>0;E1;"") doet, maar om twee of zelfs meer waardes uit te verdelen. Ik attach Voorbeeld-3. Daar is duidelijk wat ik bedoel. De waardes in kolom B en C horen bij elkaar, dus ik wil ze zo uitverdelen als ze rechts staan (kolom F en G). Met jouw formule lukt het om ze in kolom F te krijgen, maar de koppeling met de waardes in kolom G heb ik met de hand gedaan. => IS ER EEN MOGELIJKHEID om die cellen gekoppeld te houden / maken?? Dat zou mooi zijn!

Cobbe, krijg jouw formule toch niet aan de praat, helaas. Ik snap dat de $i1 in de cel eronder naar $i2 wijzigt - en dat doet ie ook gewoon bij mij - maar ik krijg gewoon bij alles wat ik maar met jouw formule doe, de foutmelding #NAAM? Ook als ik bijvoorbeeld iets stoms doe als het worksheet copieren: dan komt die foutmelding overal te voorschijn waar jouw formule staat. Ik ben nog steeds geïnteresseerd om h’m aan de praat te krijgen overigens, ook omdat je een oplossing voor die ‘dubbels’ had – dat gaat met bovenstaande formule van Popipipo weer niet geloof ik…je kan me ook mailen inderdaad op jan.k@live.nl .... Thanks anyway voor alle moeite!

Excelarator

 

[Cobbe]

Test deze formule in kolom F:

=ALS.FOUT(VERT.ZOEKEN(F1;$B$1:$C$200;2;0);"")

Cobbe

 

[Cobbe]

Hier is de juiste oplossing , ook voor de dubbels.
De formule gaf als result. Naam# omdat er een versieverschil is.
Mijn oplossing was aangereikt in xl2007 en werkt niet in .xs2000,xl2003

Cobbe

dealtje sluiten als je snapt wat ik bedoel

Daar doe ik het zeker niet voor, heb genoeg, heb mijn carriére gemaakt.
't is een hobby hé en dat moet het blijven.

 

[Excelaration]

Perfect - nou ja, bijna dan..

Cobbe, thanks!
Het werkt als een trein nu - dus een versie-verschil leverde alle frustratie op!
Ook de dubbels heb je great opgelost.
[PS. trouwens, weet je misschien een goede manual / boek waar je het toepassen van deze formules een beetje kan leren.]

Het 'enige' waar ik nu nog mee zit, en 't is eigenlijk behoorlijk belangrijk voor me, is dus om de 'waardes in de kolom ernaast' (zie "voorbeeld-3, gepaarde waardes.xls" attached bij m'n vorige bericht) ook ‘mee te sorteren / uit te vullen’. Dat dan eventueel zonder de 'dubbele waardes' - anders wordt het te ingewikkeld waarschijnlijk!
Dus als je daar nog een oplossing voor kan verzinnen, zou 't helemaal prachtig zijn.
Of was jouw formule =ALS.FOUT(VERT.ZOEKEN(F1;$B$1:$C$200;2;0);"") daarvoor bedoelt? Maar als ik die in kolom F (voorbeeld-3 zie boven) paste, gaat het niet goed – dus dat werkt nog niet. Is er een oplossing mogelijk..?!

Overigens alle begrip en respect voor je opstelling over ‘dealtje’ – kwam misschien niet zo sympathiek over, maar ik voelde me beetje bezwaard om hier steeds verder te vragen…en voor m’n werk zit er nogal belang bij de oplossing van deze vragen. Gelukkig is dit forum daar juist voor bedoelt: inderdaad een leuke, en nuttige bovendien, hobby is het om deze formules allemaal te verzinnen!

Getting to Excelerate

 

[Cobbe]

Ik heb die waardes voor u op een rijtje gezet.
Er zit nog een klein foutje in door het neerzetten van een 0 in de rijen die geen waarde hebben. Maar daar is zeker ook een oplossing voor.
Maar zo kun je even nagaan of het zo is zoals je bedoelde;

Cobbe

En ja een boek, Ik heb het gedaan met een zweetdoek en aspirine en uuuuuren testen enz...

 

[Excelaration]

Great

Da's h'm wel zo ongeveer! - ingewikkelde formule trouwens.
Die 0 die er nu extra staat, is volgens mij niet zo'n probleem. Ik moet zelf maar gaan experimenteren even, want heb eigenlijk nóg twee getallen (in weer twee extra kolommen daarnaast weer) die gekoppeld zijn aan de waarde die nu zo mooi 'uitgesorteerd' wordt.

Had zelf ondertussen al een simpele huis-tuin-en-keuken & hakkie-takkie manier bedacht, zonder formules, om de gekoppelde waardes 'mee te sorteren'. Maar dat gaat alleen zónder de dubbele waardes. Alsvolgt (in jouw vorige rar file): eerst selecteer je kolom i samen met kolom J en dan sorteer je eerst op de waardes in kolom J (dan komen de waardes weer in de oorspronkelijke volgorde bij elkaar bovenaan te staan), vervolgens kan je dan de 'gekoppelde waarde' er weer naast zetten (zoals ze er oorspronkelijk naast stonden), en vervolgens sorteer je die drie kolommen weer op kolom i, zodat alles weer mooi van 1 -> 200 staat.

Ik geloof dat ik 't probleem nu wel zo ongeveer opgelost is! Ga één en ander proberen te implementeren. Thanks Cobbe en Popipipo.
Excelerator

 

[Excelaration]

PS. kan me trouwens voorstellen dat het heel wat zweetdruppels en aspirine heeft gekost voor je zo met deze formules kan rond-darren & spelen!

 

[WHER]

Cobbe, Excelerator,

Er zit nog een klein foutje in door het neerzetten van een 0 in de rijen die geen waarde hebben

Om die "0" te vermijden, probeer eens de formule in cel N1 aan te vullen met: &""

=IF(ISERROR(VLOOKUP(ROW();$B:$F;5;0));"";VLOOKUP(ROW();$B:$F;5;0))&""

En vervolgens naar beneden slepen natuurlijk.

 

[Cobbe]

WHER,
Bedankt voor de tip, deze geeft in dit geval voldoening.
Maar kan niet overal omdat het bij een visueel-lege cel een niet-lege cel geeft als antwoord, dat kan soms problemen leveren.

Toch bedankt,

Cobbe