Excel: + of - waardes sorteren in kolommen

[JVDE]

Goedendag!

Ik heb een hele hoop excel sheets met inkomsten en uitgaven.



Ik zou deze graag sorteren in 2 kolommen zodat ik een totaal kan opmaken van inkomsten en uitgaven.
Het lukt me echter niet om de juiste formule hiervoor te vinden.

Wie kan me hierbij helpen?

Bedankt!
J

[MAX3400]

Als je nou de hele kolom eens sorteert op groot naar klein (of andersom), daarna verplaats je alle positieve of negatieve waarden naar de volgende kolom, klaar?

Je laat ook niet echt zien welke formule je wel al getest had en waarom dat niet (exact) voldoet aan je vraagstelling; dan is het niet heel handig om je hierin te adviseren waar het beter/anders kan in de formule.

[JVDE]

MAX3400 schreef op maandag 2 april 2018 @ 15:38:
Als je nou de hele kolom eens sorteert op groot naar klein (of andersom), daarna verplaats je alle positieve of negatieve waarden naar de volgende kolom, klaar?

Je laat ook niet echt zien welke formule je wel al getest had en waarom dat niet (exact) voldoet aan je vraagstelling; dan is het niet heel handig om je hierin te adviseren waar het beter/anders kan in de formule.

Bedankt voor je reactie!

De waardes staan willekeurig verspreid in de excel sheets, dus de sorteren op die 1ne kolom gaat niet omdat de waardes in verschillende kolommen staan.

[MAX3400]

En is het heel lastig als ik zeg dat je per sheet dus die sortering toepast, afsplitst en dan verder gaat?

Bot gezegd; je maakt jezelf het wel lastig door "willekeurig" spul in sheets te gooien en dan te hopen dat het met een formule anders kan. Ik ben geen held met Excel maar als je 20 sheets hebt en op elke sheet is je uitgaven-patroon niet in dezelfde kolom (dus in totaal ook 20 verschillende kolommen), moet je dus 20 verschillende formules gaan maken.

Iets zegt me dat je sneller bent door eerst zelf het overzicht/inzicht van zaken strakker te regelen; daarna kan je (ook op langere termijn) formules toepassen en sheets kopieren per maand / jaar / bedrijf / kostenpost / whatever.

[F_J_K]

Inderdaad is het dan zaak om e.e.a. beter te structureren.

Als het niet anders kan dan zet je ze met een formule in een of twee kolommen. Je moet sowieso een of andere structuur hebben om te automatiseren.

[JVDE]

Ik begrijp jullie wel maar deze excel sheets zijn van mijn bank, deze heb ik niet gemaakt. Het is een convertie van een pdf. Hierdoor staat alles wat random door elkaar.

Het boeltje zelf sorteren kan, maar ik dacht dat ik een formule kon gebruiken om dan aan de hand van *,* waardes te zoeken en mooi in de + of - kolom te zetten.

Mvg,
J

[MAX3400]

JVDE schreef op maandag 2 april 2018 @ 15:55:
Het boeltje zelf sorteren kan, maar ik dacht dat ik een formule kon gebruiken om dan aan de hand van *,* waardes te zoeken en mooi in de + of - kolom te zetten.

Dat kan zeker wel maar dan moet je wel 100% zeker weten dat elke waarde dezelfde opmaak heeft.

[YouTube: https://www.youtube.com/watch?v=iMQ2E5RDQkM]
^ alvast een voorzetje

[breew]

Als de cijfers allemaal in dezelfde kolom staan, kan dat met een simpele ALS(), lijkt me...

Zo niet, dan met een vba'tje. Je zult de code nog wat aan moeten passen, maar dat lukt vast als je voldoende motivatie hebt ;-)

Visual Basic:

Option Explicit

Sub PosNeg()

  Dim c As Range    'bereik voor cellen
  
  With ActiveSheet
    'voor elke cel in de huidige selectie op het actieve werkblad
    For Each c In Selection
      'kijk naar de waarde van de cel
      Select Case c.Value
      'positief getal?
      Case Is > 0
        'en numeriek? zet dan twee kolommen naar rechts
        If IsNumeric(c.Value) Then c.Offset(0, 2).Value = c.Value
      'negatief getal
      Case Is < 0
        'en numeriek? zet dan drie kolommen naar rechts
        If IsNumeric(c.Value) Then c.Offset(0, 3).Value = c.Value
      Case Else
      End Select
    Next c
  End With

End Sub

[JVDE]

Ja zoiets is prachtig MAX en Breew, waarvoor dank!

Maar het jammere is dat de polariteit van het getal er achter staat bvb 0,70- en niet -0,70.
Voor het extra leuk te maken staat er her en der nog een spatie tussen ook. bvb 0,70 -

[ Voor 23% gewijzigd door JVDE op 02-04-2018 19:05 ]

[MAX3400]

JVDE schreef op maandag 2 april 2018 @ 19:03:
Maar het jammere is dat de polariteit van het getal er achter staat bvb 0,70- en niet -0,70.
Voor het extra leuk te maken staat er her en der nog een spatie tussen ook. bvb 0,70 -

Dan heeft iemand (ik wijs geen vingers) de PDF gewoon ruk ontleed. Met enige nuance; een export van mijn bank levert overal dezelfde waarden op. Zoals ik eerder al aangaf, begin eerst eens met een cleanup / organisatie van de data in zijn algemeenheid.

[F_J_K]

Dat kan worden opgelost door met rechts() te kijken of het laatste karakter een - is. Er is sowieso geen vba nodig, wat ALS-en zouden moeten voldoen. Of makkelijker (na strippen van de - achteraan) een min() en max(). Immers zijn de andere cellen leeg en heeft max de daadwerkelijke waarde


Maar: ik zou eerst zoeken naar of een fatsoenlijke export ipv de pdf, of anders maar een betere ocr, liefst met formulier-herkenning.

[JVDE]

MAX3400 schreef op maandag 2 april 2018 @ 19:11:
[...]

Dan heeft iemand (ik wijs geen vingers) de PDF gewoon ruk ontleed. Met enige nuance; een export van mijn bank levert overal dezelfde waarden op. Zoals ik eerder al aangaf, begin eerst eens met een cleanup / organisatie van de data in zijn algemeenheid.

Fortis... Een gelockte PDF die je zonder wachtwoord niet kan converteren naar excel.
Dus eerst print to PDF, wachtwoord is er dan af. Daarna kan je deze wel converteren naar excel en krijg je en kluwen van data.

grts

[sypie]

Wat als je bij de instellingen van je bank eens gaat kijken of de boel ook te downloaden is als een CSV bestand? Die kun je makkelijk in excel gieten waarna je er flink mee aan de bak kunt.

[JVDE]

sypie schreef op maandag 2 april 2018 @ 19:18:
Wat als je bij de instellingen van je bank eens gaat kijken of de boel ook te downloaden is als een CSV bestand? Die kun je makkelijk in excel gieten waarna je er flink mee aan de bak kunt.

Dat is er! Enkel van de laatste 6 maand. Wil je een langere CVS dan moet je er 5euro voor betalen.
Op zich valt dat goed mee maar dit is het tweakers forum voor iets toch

[IceStorm]

JVDE schreef op maandag 2 april 2018 @ 19:03:
Maar het jammere is dat de polariteit van het getal er achter staat bvb 0,70- en niet -0,70.

Dat kun je vrij makkelijk fixen door de kolom met getallen met 'tekst naar kolommen' te bewerken. Gewoon de kolom selecteren, 'tekst naar kolommen' kiezen en meteen voor 'ok' kiezen. Werkt prima en dan is het tenminste bruikbaar

[breew]

Als je voor vba gaat, kun je met de functie instr() kijken of een karakter in een string voorkomt... daarmee kun je dus ook positieve en negatieve getallen zoeken.

Meteen even omzetten naar valuta.. altijd handig ;-)

zoiets:

Visual Basic:

Option Explicit

Sub PosNeg()

  Dim c As Range    'bereik voor cellen
  
  With ActiveSheet
    'voor elke cel in de huidige selectie op het actieve werkblad
    For Each c In Selection
      If InStr(1, c.Value, "+") <> 0 Then
        c.Offset(0, 2).Value = Format(Left(CStr(c.Value), Len(CStr(c.Value)) - 1), "Currency")
      End If
      If InStr(1, c.Value, "-") <> 0 Then
        c.Offset(0, 3).Value = Format(-1 * Left(CStr(c.Value), Len(CStr(c.Value)) - 1), "Currency")
      End If
    Next c
  End With

End Sub

[ Voor 60% gewijzigd door breew op 02-04-2018 20:05 . Reden: code + screenshot aangepast. vorige versie bevatte een bugje ]

[JVDE]

Aha! gelukt

Stap 1: lees laatste karakter met de =RECHTS functie (dit resulteert dan in + of -)
Stap 2: voeg dit karakter toe voor je waarde dan krijg je iets zoals -0,70-
Stap 3: verwijder het laatste karakter met =LINKS(J26;LENGTE(J26)-1)

Dan zit ik nog met het probleem van de spatie die achter sommige getallen nog staan

[F_J_K]

Spaties zijn vervolgens weer te verwijderen met trim(). Als het je puur om getallen gaat kan het makkelijker: zoek&vervang.

offtopic:
Ik heb je topic aangepast naar een vraag-topic. Ajb zelf de volgende keer als zodanig aanduiden

[ Voor 31% gewijzigd door F_J_K op 02-04-2018 20:25 ]

[JVDE]

Top! Het is helemaal gelukt.

De punten en spaties heb ik kunnen verwijderen met de =subsituteren functie

Dus helemaal geen vba scripts nodig. Gewoon de juiste functie weten.

Bedankt iedereen voor jullie inbreng!