[EXCEL] ontbrekende getallen in een reeks zoeken en opslaan

[djkael]

Voor een strip verzameling had ik het volgende in gedachten met Excel.

titel	nrs in bezit	ontbrekende nrs	begin	einde
jommeke	1,2,4,6,7,8,10	3,5,9	1	10

Dus als ik de nrs in bezit invul in cel A2, zou Excel automatisch de ontbrekende moeten invullen in A3 rekening houdend met de begin en eindwaarde van de reeks.
Ik zie er geen begin aan, wie kan mij in de goede richting sturen met een voorbeeld? Eventueel kunnen de nrs. ook in aparte kollomen komen indien dit eenvoudiger zou werken.

[ Voor 21% gewijzigd door djkael op 29-01-2010 23:14 ]

[sewer]

Met een macro, quick & dirty:

code:

Sub Macro2()
    Dim i, j, OwnedVarSet, NotOwned, NextOwned

    OwnedVarSet = Split(Range("A1").Value, ",")
    j = 0
    For i = OwnedVarSet(0) To OwnedVarSet(UBound(OwnedVarSet))
      NextOwned = OwnedVarSet(j)
      If i = Val(NextOwned) Then
        j = j + 1
      Else
        NotOwned = NotOwned & "," & i
      End If
    Next i
    
    Range("A2").Value = Mid(NotOwned,2)
End Sub

Ik ga hier vanuit dat je lijst opeenvolgende nummers heeft: bijvoorbeeld 1,3,5,8,12. De macro zal dan 2,4,6,7,9,10,11 teruggeven.

Kleine uitleg over de macro:
Ik verwacht in A1 de reeks met welke nummers je hebt. Daarna maak ik een array (beginnende met index 0) van jouw reeks, dat wordt zoiets als:
OwnedVarSet(0) = 1
OwnedVarSet(1) = 3
OwnedVarSet(2) = 5
OwnedVarSet(3) = 8
OwnedVarSet(4) = 12

Daarna loop ik met i van 1 t/m 12, en zet ik NextOwned op OwnedVarSet(0) = 1.
Dus in de eerste loop geldt dat i = 1 en NextOwned = 1. Dan hoog ik j eentje op en NextOwned wordt dan 3.

In de tweede iteratie geldt i = 2 en NextOwned = 3, dus dan kom ik in de else tak en zet ik NotOwned op ",2"

Daarna geldt i = 3 en NextOwned = 3, dus hoog ik j op naar 2 en NextOwned naar 5.

Etc.

Op het eind zet ik het resultaat van NotOwned, zonder de eerste komma, in A2

---

Deze oplossing werkt iig als je het vanuit het macro-omgeving runt. Maar aangezien ik niets van VBA weet (wel wat van VB6), weet ik niet precies hoe je dit vanuit je excel cel moet aanroepen. Maar je zal er een functie van moeten maken waarbij die A1 en A2 flexibel zijn, en dat ergens in je worksheet zien in te voegen ofzo. Moet je maar even googlen...

[ Voor 6% gewijzigd door sewer op 29-01-2010 22:46 ]

[pedorus]

offtopic:
Ik had niet verwacht dat iemand anders ook zou reageren en was een saaie film aan het kijken. Even snel bedacht:

Je kan de volgende functie in een module zetten (alt-f11 om de VBA-editor te openen, rechts klikken in het project om een module toe te voegen):

Visual Basic:

Public Function Ontbrekende(aanwezige As String, _
    Optional begin As Integer = -32768, Optional einde As Integer = -32768)

    Dim result As String, i As Integer
    If einde = -32768 Then
        einde = Mid(aanwezige, InStrRev(aanwezige, ",") + 1)
    End If
    If begin = -32768 Then
        begin = Left(aanwezige, InStr(aanwezige, ",") - 1)
    End If

    aanwezige = "," & aanwezige & ","
    For i = begin To einde
        If InStr(aanwezige, "," & i & ",") = 0 Then
            result = result & "," & i
        End If
    Next
    Ontbrekende = Mid(result, 2)
End Function

Dan kun je in Excel zelf gewoon Ontbrekende aanroepen als functie. Met de standaard-Excelfuncties is dit een vrij kansloze missie, dus zonder macro's gaat het niet lukken.

Mocht je ooit gaan bedenken dat 1,2,5,6-10 ook een handige notatie is, dan is de aanpak die hierboven staat iets makkelijker daaraan aan te passen.

[djkael]

Inderdaad die macro werkt! Ik had het zelf nooit gevonden in elk geval, erg bedankt in elk geval.
Een aantal zaken zijn nog niet duidelijk zoals die split en ubound, maar dat zoek ik wel eventjes op.
Ik heb de layout van mijn eerste post wat aangepast met een tabel nu (hoe je daar aan uit kon eerst?!?)

Een iets is nog niet verwerkt. De begin en eindwaarde wordt nu bepaald door het laagste en hoogste nr in de gekende reeks maar dat is niet bruikbaar want dan wordt er van uitgegaan dat je altijd de eerste en laatste strip in de reeks reeds in je bezit hebt. Vandaar dat ik ook een Begin en Eind waarde kolom voorzie.
Waar komen die 2 waardes dan ergens in de macro best?

En zou ik dan een macro per strip reek moeten maken, of kan 1 macro een hele reeks afwerken?

[ Voor 6% gewijzigd door djkael op 29-01-2010 23:46 ]

[djkael]

Pedorus, je kan mijn werkwijze raden, inderdaad, nu had ik reeds om plaats te sparen, die notatie 1,2,5,6-10 gebruikt. Wat komt er dan bij als ik het zo wil laten ipv. 1,2,5,6,7,8,9,10
En heb je ook een idee wat betreft die aparte kolom met begin en eindwaarde waar de nrs moeten tussen vallen? (ipv de eerste en laatste uit de reeks te gebruiken).

[pedorus]

Gisternacht had ik hier iets voor bedacht, maar het viel me op hoe verschrikkelijk veel regels het was geworden. Vandaag zie ik enkel niet direct ruimte voor enorme verbetering, doordat tal van handige functies ontbreken in VBA (misschien dat een oplossing met split wel handiger was geweest)...

Over eerste en laatste van een serie: je kan ze opgeven als parameters. In die kolom zelf kun je eventueel een formule zetten (een leuke uitdaging lijkt me, je kan zelf een functie schrijven aan de hand van Ontbrekende).

Visual Basic:

Option Explicit
Public Function StrEndsWith(s As String, s2 As String) As Boolean
    StrEndsWith = Right(s, Len(s2)) = s2
End Function

Private Function NextValue(s As String, Optional pos As Integer = 0, _
    Optional skip As Boolean, Optional oldvalue As Integer = -32768) As Integer

    Dim c As String, oldpos As Integer
    skip = False
    If pos > 0 Then
        skip = Mid(s, pos, 1) = "-"
    End If
    oldpos = pos
    Do
        pos = pos + 1
        If pos > Len(s) Then
            c = "-"
        Else
            c = Mid(s, pos, 1)
        End If
    Loop Until c = "-" Or c = ","
    NextValue = Mid(s, oldpos + 1, pos - oldpos - 1)
    If oldvalue >= NextValue Then
        Err.Raise 93281232, "Ontbrekende", "aanwezige is niet oplopend"
    End If
End Function

Public Function Ontbrekende(aanwezige As String, _
    Optional begin As Integer = -32768, Optional einde As Integer = -32768)

    Dim result As String, i As Integer
    Dim np As Integer, nextval As Integer, skip As Boolean

    If einde = -32768 Then
        einde = Mid(aanwezige, WorksheetFunction.max( _
            InStrRev(aanwezige, ","), _
            InStrRev(aanwezige, "-")) + 1)
    End If
    If begin = -32768 Then
        begin = NextValue(aanwezige)
    End If

    nextval = -32768
    If aanwezige <> "" Then
        nextval = NextValue(aanwezige, np, skip)
        While nextval < begin And np < Len(aanwezige) + 1
            nextval = NextValue(aanwezige, np, skip, nextval)
        Wend
        If nextval > begin And skip Then
            begin = nextval
        End If
    End If
    For i = begin To einde
        If i = nextval Then
            If np = Len(aanwezige) + 1 Then
                nextval = -32768
            Else
                nextval = NextValue(aanwezige, np, skip, nextval)
                If skip Then
                    i = nextval - 1
                End If
            End If
        Else
            If StrEndsWith(result, "-" & i - 1) Or _
                StrEndsWith(result, "," & i - 2 & "," & i - 1) Then
                result = Left(result, Len(result) - Len(i - 1 & "-")) & "-" & i
            Else
                result = result & "," & i
            End If
        End If
    Next
    Ontbrekende = Mid(result, 2)
End Function

[djkael]

Heel knap dat je dit zomaar eventjes uit je mooi kan schudden; ik vermoed dat daar heel wat jaren (programeer) ervaring aan vooraf gegaan zijn? Had ik eventjes geluk dat het een saaie film was ;-)
Wat bedoel je met ontbrekende VBA? Het lijkt me reeds vrij compleet zo?
Ook een makro heb ik hier toch niet meer nodig?
Ik ging er eventjes mee aan de slag en dat gaat heel aardig zo.
Ik kwam nog met een probleemtje. Het totaal aantal tussen begin en einde is snel gevonden, maar het aantal in bezit en het aantal nog gezocht, hoe zou ik die kunnen achterhalen? lijkt me niet evident met die streepjes tussen de getallen.

[sewer]

Andere manier:

Visual Basic:

Function GetSeparateItemsString(ByVal OrgString As String) As String
   'If OrgString is 1,2,3-5,7 then the result will be 1,2,3,4,5,7
   Dim ArrayItems, ArrayRange, i, j
   ArrayItems = Split(OrgString, ",")
   For i = LBound(ArrayItems) To UBound(ArrayItems)
     ArrayItems(i) = Trim(ArrayItems(i))
     If InStr(1, ArrayItems(i), "-") > 0 Then
        ArrayRange = Split(ArrayItems(i), "-")
        ArrayItems(i) = ""
        For j = ArrayRange(0) To ArrayRange(1)
          ArrayItems(i) = ArrayItems(i) & "," & j
        Next j
        ArrayItems(i) = Mid(ArrayItems(i), 2)
     End If
   Next i
  GetSeparateItemsString = Join(ArrayItems, ",")
End Function

Function GetGroupedItemsString(ByVal OrgString As String) As String
  'If OrgString is 1,2,3-5,7,8,9,11 then the result will be 1-5,7-9,11
  OrgString = GetSeparateItemsString(OrgString)
  Dim ArrayItems, ArrayRange, i, GroupMin, GroupNext, GroupMax
  ArrayItems = Split(OrgString, ",")
  
  GroupMin = ArrayItems(0)
  GroupMax = GroupMin
  GroupNext = GroupMin + 1
  For i = (LBound(ArrayItems) + 1) To UBound(ArrayItems)
    If Val(ArrayItems(i)) = GroupNext Then
        GroupMax = GroupNext
        GroupNext = GroupNext + 1
    Else
      If GroupMin = GroupMax Then
        GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin
      Else
        GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin & "-" & GroupMax
      End If
      GroupMin = ArrayItems(i)
      GroupMax = GroupMin
      GroupNext = GroupMin + 1
    End If
  Next i
  If GroupMin = GroupMax Then
    GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin
  Else
    GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin & "-" & GroupMax
  End If
  GetGroupedItemsString = Mid(GetGroupedItemsString, 2)
End Function

Function GetMissingItems(ByVal OrgString As String, ByVal BeginValue As Long, ByVal EndValue As Long) As String
   'If OrgString is 1-3,5,8 and the range is from 1 to 10, this function will return 4,6,7,9
   Dim i, NotOwned, FirstValue, LastValue

    OrgString = GetSeparateItemsString(OrgString)
    FirstValue = Left(OrgString, InStr(1, OrgString, ",") - 1)
    LastValue = Mid(OrgString, InStrRev(OrgString, ",") + 1)
    
    If BeginValue <= 0 Then
      BeginValue = FirstValue
    End If
    If EndValue <= 0 Then
      EndValue = LastValue
    End If
      
    For i = BeginValue To EndValue
      If (InStr(1, OrgString, "," & i & ",") = 0) And (i <> Val(FirstValue)) And (i <> Val(LastValue)) Then
        NotOwned = NotOwned & "," & i
      End If
    Next i
    
    GetMissingItems = Mid(NotOwned, 2)
End Function

Function GetItemCount(ByVal OrgString As String) As Long
    'If OrgString is 1-3,5,7,9-11, this function will return 8
    GetItemCount = UBound(Split(GetSeparateItemsString(OrgString), ",")) + 1
End Function

Hier zijn 4 functies:
GetSeparateItemsString: converteert 1-3,5,8,9-11 naar 1,2,3,5,8,9,10,11
GetGroupedItemsString: converteert 1,2,3-5,8,9,11,13 naar 1-5,8-9,11,13
GetMissingValues: antwoord van het originele probleem (met pedoras instr), kan ook streepjes aan en converteert naar komma-gescheiden items. Als BeginValue en EndValue groter dan nul zijn, dan worden die gebruikt, anders het eerste en laatste item van de reeks.
GetItemCount: bij 1-4,6,8,11 krijg je als antwoord 7 terug.

Die GetGroupedItemsString is een beetje brak geimplementeerd, maar het is me te laat om die te herschrijven Maar hij lijkt wel het goede op te leveren.

Als A1 je collectie bevat, kun je dus bijv. in B1 "=GetMissingValues(A1,1,100)" zetten voor de missende nummers met komma's er tussen, maar je kan ook "=GetGroupedItemsString(GetMissingItems(A1,1,100))" neerzetten als je er ook streepjes tussen wilt.

Als je in C1 "=GetItemCount(A1)" zet, krijg je het aantal items (werkt ook bij streepjes-notatie), en "=GetItemCount(GetMissingValues(A1))" geeft het aantal ontbrekende waardes terug.

[ Voor 155% gewijzigd door sewer op 01-02-2010 20:09 ]

[djkael]

"=GetItemCount(GetMissingValues(A1))"
dat gaat goed zolang het optel resultaat onder de 88 blijft (soms ook 87 afhankelijk hoe A1 opgebouwd is, veel streepjes of komma's). Maar eens er boven krijg ik #WAARDE !?!
Gebruik ik 2 stappen A2="GetMissingValues(A1) en dan A3=GetItemCount(A2) gaat het wel goed. Zou er daar ergens variable fout staan of zo?

[sewer]

Sorry, ik had die BeginValue en EndValue van die functie GetMissingItems de dag erna nog erin gepropt, maar die waren niet optioneel. Dus mijn uitleg eronder klopte niet meer.

Nieuwe functie met optionele parameters:

code:

Function GetMissingItems(ByVal OrgString As String, Optional ByVal BeginValue As Long, Optional ByVal EndValue As Long) As String
   'If OrgString is 1-3,5,8 and the range is from 1 to 10, this function will return 4,6,7,9
   Dim i, NotOwned, FirstValue, LastValue

    OrgString = GetSeparateItemsString(OrgString)
    FirstValue = CLng(Left(OrgString, InStr(1, OrgString, ",") - 1))
    LastValue = CLng(Mid(OrgString, InStrRev(OrgString, ",") + 1))
     
    If BeginValue <= 0 Then
      BeginValue = FirstValue
    End If
    If EndValue <= 0 Then
      EndValue = LastValue
    End If
       
    For i = BeginValue To EndValue
      If (InStr(1, OrgString, "," & i & ",") = 0) And (i <> Val(FirstValue)) And (i <> Val(LastValue)) Then
        NotOwned = NotOwned & "," & i
      End If
    Next i
     
    GetMissingItems = Mid(NotOwned, 2)
End Function

GetMissingItems("2,50,120") geeft alle missende waardes van 2 tot 120.
GetMissingItems("2,50,120",1,250) geeft alle missende waardes tussen 1 en 250.

Mocht dat niet de oplossing zijn, stuur me dan maar even een mailtje met je excel-sheet, dan debug ik mijn functie wel even (zie profiel voor adres) en post ik het resultaat hier.

edit:
Ik zie dit ik in mijn vorige post overal over GetMissingValues heb, maar dat had dus GetMissingItems moeten zijn

[ Voor 4% gewijzigd door sewer op 02-02-2010 22:56 ]

[djkael]

Ja de code en de functie namen zijn al wat gewijzigd geweest en het wordt zo wat verwarrend.
Maar er blijft een soort overflow fout bestaan:
=GetGroupedItemsString(GetMissingItems("1,2,3";1;90)) werkt goed maar verander je 90 naar 91 is het #WAARDE!
De 2 functie apart gaan wel goed?!?

[sewer]

Vreemd, bij mij gaat het pas mis vanaf "1,2,3",1,6780, alles onder 6780 items gaat wel goed. Maar dat is onder win7 (32-bit) en excel 2010.

Het lullige bij vba is dat er geen strong typing is, hij maakt er maar wat van. Dus bij zo'n variabele als i weet je bijvoorbeeld nooit of hij er een int of long van maakt. klopt niet, dank aan _heretic_
Als ik mijn foutsituatie (bij 6780 items) debug, de functie in vba wel de goede string lijkt terug te geven, terwijl in de cel #value staat. Dat komt omdat de cel max 32768 tekens mag bevatten, terwijl de vba-string functie ongeveer 2 miljard karakters kan bevatten. De inhoud van de cel is dus beperkender dan wat vba-string kan teruggeven.

Maar jouw versie van excel loopt tegen een grens aan waar mijn versie niet tegen aan loopt. Ik gok op een string die maar 255 karakters mag zijn (87 * ongeveer 3 karakters inclusief de komma).

Geen idee waarom het in 2 stappen wel werkt, en in 1 stap niet. Ik heb wel mijn programma aangepast met wat errorhandler (nou ja, sort of, het is blijkbaar niet mogelijk om een messagebox te tonen in vba???) en wat meer typing erin (typed declaraties, en wat meer conversies mbv de val-functie), misschien dat dat helpt?

Visual Basic:

Function GetSeparateItemsString(ByVal orgstring As String) As String
    On Error GoTo errorhandler
   'If OrgString is 1,2,3-5,7 then the result will be 1,2,3,4,5,7
   Dim ArrayItems() As String
   Dim ArrayRange() As String
   Dim i As Long
   Dim j As Long
   ArrayItems = Split(orgstring, ",")
   For i = LBound(ArrayItems) To UBound(ArrayItems)
     ArrayItems(i) = Trim(ArrayItems(i))
     If InStr(1, ArrayItems(i), "-") > 0 Then
        ArrayRange = Split(ArrayItems(i), "-")
        ArrayItems(i) = ""
        For j = Val(ArrayRange(0)) To Val(ArrayRange(1))
          ArrayItems(i) = ArrayItems(i) & "," & j
        Next j
        ArrayItems(i) = Mid(ArrayItems(i), 2)
     End If
   Next i
  GetSeparateItemsString = Join(ArrayItems, ",")
  Exit Function
errorhandler:
  GetSeparateItemsString = "Error in GetSeparateItemsString: " & Err.Number & ". " & Err.Description
End Function

Function GetGroupedItemsString(ByVal orgstring As String) As String
  On Error GoTo errorhandler
  'If OrgString is 1,2,3-5,7,8,9,11 then the result will be 1-5,7-9,11
  orgstring = GetSeparateItemsString(orgstring)
  Dim ArrayItems() As String
  Dim i As Long
  Dim GroupMin As Long
  Dim GroupNext As Long
  Dim GroupMax As Long
  ArrayItems = Split(orgstring, ",")
     
  GroupMin = Val(ArrayItems(0))
  GroupMax = Val(GroupMin)
  GroupNext = Val(GroupMin) + 1
  For i = (LBound(ArrayItems) + 1) To UBound(ArrayItems)
    If Val(ArrayItems(i)) = GroupNext Then
        GroupMax = GroupNext
        GroupNext = GroupNext + 1
    Else
      If GroupMin = GroupMax Then
        GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin
      Else
        GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin & "-" & GroupMax
      End If
      GroupMin = Val(ArrayItems(i))
      GroupMax = Val(GroupMin)
      GroupNext = Val(GroupMin) + 1
    End If
  Next i
  If GroupMin = GroupMax Then
    GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin
  Else
    GetGroupedItemsString = GetGroupedItemsString & "," & GroupMin & "-" & GroupMax
  End If
  GetGroupedItemsString = Mid(GetGroupedItemsString, 2)
  Exit Function
errorhandler:
  GetGroupedItemsString = "Error in GetGroupedItemsString: " & Err.Number & ". " & Err.Description

End Function

Function GetMissingItems(ByVal orgstring As String, Optional ByVal BeginValue As Long, Optional ByVal EndValue As Long) As String
   On Error GoTo errorhandler
   'If OrgString is 1-3,5,8 and the range is from 1 to 10, this function will return 4,6,7,9
    Dim i As Long
    Dim NotOwned As String
    Dim FirstValue As Long
    Dim LastValue As Long

    orgstring = GetSeparateItemsString(orgstring)
    FirstValue = Val(Left(orgstring, InStr(1, orgstring, ",") - 1))
    LastValue = Val(Mid(orgstring, InStrRev(orgstring, ",") + 1))
       
    If BeginValue <= 0 Then
      BeginValue = FirstValue
    End If
    If EndValue <= 0 Then
      EndValue = LastValue
    End If
         
    For i = BeginValue To EndValue
      If (InStr(1, orgstring, "," & i & ",") = 0) And (i <> CLng(FirstValue)) And (i <> CLng(LastValue)) Then
        NotOwned = NotOwned & "," & i
      End If
    Next i
       
    GetMissingItems = Mid(NotOwned, 2)
    Exit Function
errorhandler:
    GetMissingItems = "Error in GetMissingItems: " & Err.Number & ". " & Err.Description

End Function

Function GetItemCount(ByVal orgstring As String) As Long
   On Error GoTo errorhandler
    'If OrgString is 1-3,5,7,9-11, this function will return 8
    GetItemCount = UBound(Split(GetSeparateItemsString(orgstring), ",")) + 1
    Exit Function
errorhandler:
    GetItemCount = -1
End Function

Ik gok dat =GetMissingItemsGrouped("1,2,3";1;150) nu wel bij jou goed gaat


vvv Ah, bedankt. Ik heb niet eens geprobeerd om expliciet te declareren, omdat ik dacht dat het taaltje hetzelfde was als vb-script. Ik heb de routines een beetje aangepast hierop.

[ Voor 14% gewijzigd door sewer op 04-02-2010 10:38 ]

[Verwijderd]

wat de exacte beperkingen zijn in excel qua tekstlengte in cellen enz. weet ik niet, maar waarschijnlijk is dit aangepast.
een messagebox kan wel gebruikt worden, maar niet binnen een functie die vanaf een werkblad opgeroepen wordt.
en je kan natuurlijk expliciet declaren as long, as string, as double ... zodat bewerkingen op deze variabelen beter voorspelbaar zijn. momenteel heb je deze variabelen enkel impliciet als variant gedeclareerd, behalve de functieargumenten.

[djkael]

Geen verschil te merken.
Maar ik meldde het wel verkeerd gisteren; het is de functie GetMissingItems die de fout veroorzaakt:
dit gaat net wel nog goed

code:

=GetMissingItems("14,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,78,79,81,82,84,85,86,88,89, 90,92,93,100")

maar nog de waarde '94' erbij en dan komt de fout: 'De formule is te lang'
Zoals je al zei, zal het allicht een 255 string beperking zijn van Excel 2003. Ik zal het hier in 2 keer moeten blijven doen.
Het lijkt alsof een output naar een +255 String wel lukt (zoals bij GetSeparateItemsString), maar een +255 string als input meegeven lukt niet.

[ Voor 10% gewijzigd door Verwijderd op 04-02-2010 23:39 ]

[sewer]

Ik had gisteren nog een functie gemaakt, maar die is vandaag bij het editen weer weggevallen

Visual Basic:

Function GetMissingItemsGrouped(ByVal orgString As String, Optional ByVal BeginValue As Long, Optional ByVal EndValue As Long) As String
    GetMissingItemsGrouped = GetGroupedItemsString(GetMissingItems(orgString, BeginValue, EndValue))
End Function

Deze moet gewoon werken als je GetMissingItemsGrouped("3,4,5";1;150) doet

[djkael]

Ja die doet het wel, maar die input "3,4,5" is een string kleiner dan 255. Als je die >255 maakt dan terug 'formule is te lang'

[pedorus]

Tja, dat is gewoon een limitatie in Excel 2003, waarschijnlijk deze.

In excel 2007 is deze lengte denk ik 32766. Als ik test met =LEN(txt("a",32767)) krijg ik een foutmelding:

Visual Basic:

Public Function txt(a As String, e As Integer) As String
    Dim i As Integer
    For i = 1 To e
       txt = txt & a
    Next i
End Function

Vreemd genoeg kan ik met de ingebouwde rept er eentje meer bij doen (dus dit faalt pas bij 32768: =len(rept("a",32768)) ).

Waarschijnlijk krijg jij al bij 256 een foutmelding?

[djkael]

Nee, =LENGTE(txt("a";32765)) lukt ook bij mij met Excel 2003

[djkael]

Pedorus, ik wil toch nog eens terugkomen op de functie Ontbrekende uit je 2de post; deze gebruikt Private Function NextValue maar die controle daar op opeenvolgende nrs. lijkt niet te werken?
Als ik vertrek van 1,3,4,4,5 krijg ik WAARDE!
Wat betekent trouwens die Err.Raise 93281232?
Soms heb ik 2x dezelfde waarde na elkaar staan in de begin string, misschien zou het dan beter zijn om dan gewoon die dubbele over te slaan tijdens het berekenen van de ontbrekende ipv. van een foutmelding.
Die gecombineerde tekst in die fourmelding klopt dat? Ik begrijp ze niet.

[pedorus]

djkael schreef op zaterdag 06 februari 2010 @ 22:40:
Function NextValue maar die controle daar op opeenvolgende nrs. lijkt niet te werken?
Als ik vertrek van 1,3,4,4,5 krijg ik WAARDE!

Dat lijkt mij kloppen: 4 is niet groter dan 4 Dit is de >= -test op regel 24, anders had dat alleen '>' moeten zijn.

Wat betekent trouwens die Err.Raise 93281232?

Een willekeurig gekozen getal, maar fout omdat het getal in de range 513–65535 had moeten zijn om te werken als ik zo in de help kijk en het uittest. Het idee was om het makkelijk op te zoeken, en dat werkt nu al als je gaat zoeken. Helaas zie je die code enkel niet vanuit een formule, maar vanuit het immediate-venstertje kun je hem bijvoorbeeld wel zien. Ik zou zeggen, verander 93281232 in 51232 - ook willekeurig gekozen en wel in de juiste range..

Die gecombineerde tekst in die fourmelding klopt dat? Ik begrijp ze niet.

Het ene is de bron (kopje in een eventueel dialoogje), het andere is de omschrijving (tekst in een eventueel dialoogje). Helaas zie je ze niet als je de functie als formule gebruikt, je ziet ze alleen in VBA. Zie verder de help van Err.Raise.

djkael schreef op zaterdag 06 februari 2010 @ 20:36:
Nee, =LENGTE(txt("a";32765)) lukt ook bij mij met Excel 2003

Mm. =LENGTE(txt(txt("a";32765);1)) dan?

Als je hem uitschrijft kan het ook in 2007 niet ("Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function of the concatenation operator (&).")

[djkael]

Die error afhandeling heeft niet veel zin dan op die manier? Je krijgt de error dialoog niet te zien in Excel maar gewoon WAARDE!
Hou zou je dubbels in de originele string gewoon kunnen negeren bij het berekenen van Ontbrekende ipv. van die WAARDE! ?

[djkael]

Ik gebruik de besproken code nu al een poosje maar ik vraag me af als er een soort convertor bestaat die VBA kan omzetten naar Google Apps Script (GAS)? Ik had graag die spreadsheet op Google docs gekregen met dezelfde functionalteit.

[pedorus]

Vertalen lijkt me lastig, maar dit is de functie in js; ik zou hem nog wel even flink testen:

JavaScript:

function addResult(first, diff) {
  if (diff > 0)
    if (diff > 2)
      return "," + first + "-" + (first + diff - 1)
    else if (diff == 1)
      return "," + first
    else //diff == 2
      return "," + first + "," + (first + 1)
  return ""
}
function missingItems(numbers, first, last) {
  var groups = (numbers+"").split(",")
  if (first == undefined) first = groups[0].replace(/-.*/, "") * 1    
  if (last == undefined) last = groups[groups.length - 1].replace(/.*-/, "") * 1
  var group
  while ((group = groups.shift()) && (group.replace(/.*-/, "") < first));
  if (group == undefined || group == "")
    return addResult(first, last - first + 1).slice(1);
  if (group.match(/-/)) first = group.replace(/-.*/, "")
  var result = "";
  do {
    var current = group.replace(/-.*/, "")
    if (first > current) throw "numbers not ordered"
    result += addResult(first, Math.min(last + 1, current) - first)
    first = group.replace(/.*-/, "") * 1 + 1;
  } while (group = groups.shift());
  if (first <= last) result += addResult(first, last - first + 1)
  return result.slice(1)
}

[djkael]

Heel mooi, en het werkt nog goed ook!
Ik had niet gedacht hier een antwoord op te krijgen, erg bedankt in elke geval. Het lijkt nog simpelder dan in VBA.
Eerst hielp je me met VBA nu in Java, ruime kennis in elk geval.....
Zou je ook een oplossing hebben voor de GetItemCount van hierboven? Waarmee ik de aantallen kan achterhalen van de ingevulde en berekende string? De Count van GAS lukt niet met stings.
Enige kans op wat verklarende tekst in de code? Nu kan ik ze niet helemaal volgen.

[pedorus]

djkael schreef op zondag 14 november 2010 @ 23:40:
Eerst hielp je me met VBA nu in Java, ruime kennis in elk geval.....

offtopic:
Ik ken ook wel Java, maar Javascript is geen Java

Zou je ook een oplossing hebben voor de GetItemCount van hierboven?

JavaScript:

function countItems(numbers) {
  var result = 0
  var t = (numbers + "").replace(/([0-9]+)(?:-([0-9]+))?/g, 
    function($0, $1, $2) { result += $2 ? $2 - $1 + 1 : 1 })
  return result
}

Enige kans op wat verklarende tekst in de code? Nu kan ik ze niet helemaal volgen.

Ik ook niet, het is 12 dagen later

Het idee is in elk geval dat de boel opgesplitst wordt in groepjes aan de hand van de komma's, en dat met replace(/-.*/, "") het eerste nummer en replace(/.*-/, "") het laatste nummer van een groepje wordt verkregen. Vervolgens worden de groepjes doorgelopen (do) en wordt steeds het verschil tussen [vorige laatste nummer] en [dit eerste nummer] aan het resultaat toegevoegd.

De countItems hierboven werkt met regular expressions, als je die kent is het te volgen. Als je een ander scheidingsteken dan komma gebruikt werkt die functie trouwens ook, omdat er geen enkele controle op fouten is.