Excel lijst splitsen

[Crxtreem]

Ik heb in Excel een lijst met positieve en negatieve getallen.
Deze wil ik op een ander tabblad splitsen in 2 lijsten 1 met de negatieve en de andere met de positieve.

Ik heb dit Ik heb dit al gedeeltelijk werkend gekregen met de ALS functie en anders volgende rij.
Maar heeft de ALS functie ook een next functie?

De rest van de rij vul ik aan met vert.zoeken. (Of kan ik met de Als functie ook de complete rij plakken?)

Nu wil ik dit het liefst zonder macro's doen omdat ik thuis Libreoffice gebruik maar het ook op Excel wil gebruiken.

[F_J_K]

Zou het kunnen voldoen om te filteren? bij de een filteren op <0 en de ander op >= 0. Dat zal niet voldoen als je er ook nog mee wilt rekenen trouwens.

Makkelijkste daarvoor: voeg op het positieve tab een hulpkolom toe: als A1 >= 0 dan A1, anders "".
En nog steeds de lege regels filteren zodat het mooi lijkt aan te sluiten.

[KabouterSuper]

Je kunt een tellertje maken die de positieve en negatieve getallen nummert. En in je andere tabbladen daarnaar verwijzen met een vlookup of index/match.
Dus

code:

1    => 1, 0
5    => 2, 0
-2    => 2, 1
5    => 3, 1
-4    => 3, 2

[heuveltje]

Crxtreem schreef op maandag 6 juli 2020 @ 08:52:
Ik heb in Excel een lijst met positieve en negatieve getallen.
Deze wil ik op een ander tabblad splitsen in 2 lijsten 1 met de negatieve en de andere met de positieve.

Ik heb dit Ik heb dit al gedeeltelijk werkend gekregen met de ALS functie en anders volgende rij.
Maar heeft de ALS functie ook een next functie?

De rest van de rij vul ik aan met vert.zoeken. (Of kan ik met de Als functie ook de complete rij plakken?)

Nu wil ik dit het liefst zonder macro's doen omdat ik thuis Libreoffice gebruik maar het ook op Excel wil gebruiken.

Sorteer op 2 dat getal. kies alles tot de 0 en knip en plak dat naar een 2e blad ?

[Crxtreem]

heuveltje schreef op maandag 6 juli 2020 @ 09:15:
[...]


Sorteer op 2 dat getal. kies alles tot de 0 en knip en plak dat naar een 2e blad ?

Maar dat is niet te automatiseren zonder macro?
Mijn wens is dat ik dit met een formule automatisch kan doen zodat de nieuwe lijsten altijd bijgewerkt zijn.

[heuveltje]

Crxtreem schreef op maandag 6 juli 2020 @ 09:17:
[...]


Maar dat is niet te automatiseren zonder macro?
Mijn wens is dat ik dit met een formule automatisch kan doen zodat de nieuwe lijsten altijd bijgewerkt zijn.

Je kunt iets met verticaal zoeken zoals @KabouterSuper zegt.
Maar dan moet je filters instellen die automatisch opnieuw toegepast worden zodra er iets gewijzigd is.
Maar mij niet bekend dat dat mogelijk is zonder een (zeer simpel) macro.
(wat niet wil zeggen dat het niet bestaat )

[KabouterSuper]

heuveltje schreef op maandag 6 juli 2020 @ 11:36:
[...]
Je kunt iets met verticaal zoeken zoals @KabouterSuper zegt.
Maar dan moet je filters instellen die automatisch opnieuw toegepast worden zodra er iets gewijzigd is.
Maar mij niet bekend dat dat mogelijk is zonder een (zeer simpel) macro.
(wat niet wil zeggen dat het niet bestaat )

De oplossing die ik voorstel, heeft juist geen filters nodig.
Even wat pseudo code.
Als in kolom A de reeks met positieve en negatieve getallen staan met een header, dan:
kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A2

kolom H (formule is voor H2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,B:D,3,FALSE),"")
kolom I (formule is voor I2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,C:D,2,FALSE),"")

Je krijgt dan in kolom H en I netjes een rijtje met de positieve en negatieve getallen zonder witte velden er tussen.

[ Voor 9% gewijzigd door KabouterSuper op 06-07-2020 12:13 . Reden: Geklooi met headers, soms begon ik in rij 2, soms in rij 1 ]

[heuveltje]

KabouterSuper schreef op maandag 6 juli 2020 @ 12:03:
[...]

De oplossing die ik voorstel, heeft juist geen filters nodig.
Even wat pseudo code.
Als in kolom A de reeks met positieve en negatieve getallen staan, dan:
kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A1

kolom H (formule is voor H1, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A1),B:D,3,FALSE),"")
kolom I (formule is voor I1, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A1),C:D,2,FALSE),"")

Je krijgt dan in kolom H en I netjes een rijtje met de positieve en negatieve getallen zonder witte velden er tussen.

Ik heb hem nu 3 keer gelezer, maar ik snap het nog steends niet.
als regel 3 een negatief getal is, dan is H toch gewoon leeg.
Dus wat je wil werkt wel, (mits aantal regels gelijk blijft). maar vervolgens zijn alle regels met negatieve waarde op je positive blad toch gewoon blank ?

[KabouterSuper]

heuveltje schreef op maandag 6 juli 2020 @ 12:14:
[...]


Ik heb hem nu 3 keer gelezer, maar ik snap het nog steends niet.
als regel 3 een negatief getal is, dan is H toch gewoon leeg.
Dus wat je wil werkt wel, (mits aantal regels gelijk blijft). maar vervolgens zijn alle regels met negatieve waarde op je positive blad toch gewoon blank ?

No problem, het is niet een voor de hand liggende truc. Zie hieronder de sheet die ik net gemaakt heb. Er zijn geen lege velden in kolom H en I.

De vlookup condenseert de lijst zodat er geen lege velden zijn. Dat doe ik door het rijnummer (of eigenlijk rijnummer -1) op te zoeken in kolom B en C. Bij een vlookup wordt altijd de eerste teruggegeven, wat in dit geval precies degene is die we nodig hebben.

[ Voor 20% gewijzigd door KabouterSuper op 06-07-2020 12:24 ]

[Crxtreem]

Ik heb hem even naar Nederlands omgezet en dit stukje werkt.
Vanavond eens kijken of ik dit in mijn eigen bestand ook zo werkend krijg.

[KabouterSuper]

Crxtreem schreef op maandag 6 juli 2020 @ 12:34:
Ik heb hem even naar Nederlands omgezet en dit stukje werkt.
Vanavond eens kijken of ik dit in mijn eigen bestand ook zo werkend krijg.

Mooi. Je kunt waarschijnlijk wel kolom D vermijden met een index/match als je dat zou willen.

[heuveltje]

KabouterSuper schreef op maandag 6 juli 2020 @ 12:03:
[...]

kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A2

Ah ik miste *het gebrek aan) 2e $ in de countif.
Dan werkt het inderdaad wel zoals je zegt.

Interessante oplossing !

[g0tanks]

KabouterSuper schreef op maandag 6 juli 2020 @ 12:03:
[...]

De oplossing die ik voorstel, heeft juist geen filters nodig.
Even wat pseudo code.
Als in kolom A de reeks met positieve en negatieve getallen staan met een header, dan:
kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A2

kolom H (formule is voor H2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,B:D,3,FALSE),"")
kolom I (formule is voor I2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,C:D,2,FALSE),"")

Je krijgt dan in kolom H en I netjes een rijtje met de positieve en negatieve getallen zonder witte velden er tussen.

Mooie oplossing. Volgende uitdaging: automatisch sorteren.

[KabouterSuper]

g0tanks schreef op maandag 6 juli 2020 @ 13:14:
[...]


Mooie oplossing. Volgende uitdaging: automatisch sorteren.

Challenge accepted

Eerst de rank bepalen. Voor de positieve getallen bijv in kolom G: <G2>=IFERROR(RANK.EQ(H2,H:H,TRUE),-1)
Dan in kolom L de gesorteerde lijst: <L2>=IF(G2=-1,"",IFERROR(VLOOKUP(ROW(A2)-1,G:H,2,FALSE),M1))

Het lastigste is om de dubbele waarden ook terug te laten komen, aangezien een rank alleen de hoogste rank teruggeeft (of het average, maar daar heb je nog minder aan).

Update: binnen een kwartier opgelost. Anyone else ?

[ Voor 4% gewijzigd door KabouterSuper op 06-07-2020 13:30 ]

[Lustucru]

g0tanks schreef op maandag 6 juli 2020 @ 13:14:
[...]


Mooie oplossing. Volgende uitdaging: automatisch sorteren.

Die is alleen maar makkelijker (als je de route neemt via matrixformules):



De matrixformules voor ongesorteerd / gesorteerd:

code:

=ALS.FOUT(INDEX($A$1:$A$10;KLEINSTE(ALS($A$1:$A$10<0;RIJ($A$1:$A$10);"-");RIJ()));"")
=ALS.FOUT(INDEX($A$1:$A$10;KLEINSTE(ALS($A$1:$A$10>0;RIJ($A$1:$A$10);"-");RIJ()));"")
=ALS.FOUT(GROOTSTE(ALS($A$1:$A$10<0;$A$1:$A$10;"-");RIJ());"")
=ALS.FOUT(KLEINSTE(ALS($A$1:$A$10>0;$A$1:$A$10;"-");RIJ());"")

Ongesorteerd moet hij eerst een matrix opbouwen van rijnummers van getallen die kleiner resp groter zijn dan 0, of "-", en dan met index het getal ophalen.
gesorteerd bouwt hij een matrix op van getallen of "-" en pakt daar de k-grootste resp de k-kleinste van.

[Lustucru]

KabouterSuper schreef op maandag 6 juli 2020 @ 13:29:
[...]
Update: binnen een kwartier opgelost. Anyone else ?

Yep, en zonder hupkolommen.

[Crxtreem]

Gisteravond werkend gekregen op de manier van @KabouterSuper in de lijst die ik zelf had.

Het is toch nog best een geworstel om de formule's in Libreoffice werkend te krijgen.
Maar ik ben weer een stap verder.

Volgende keer de matrix eens uit proberen.