Welke getallen komen meerdere malen voor

[eddy_v]

Hallo,

Ik zit met het volgende probleem
In een kolom heb ik een aantal call nummer staan.
Een call nummer bestaant uit 6 cijvers gevolgt door een vervolg nummer van 2 cijvers.

Nu wil ik graag kijken welke call nummer meerdere keren voorkomen en hoeveel keer dan.

Voor de duidelijkheid heb ik een bestand toegevoegd.

Gr Eddy.

 

[Cobbe]

Via Autofilter is dit makkelijk op te lossen.

Cobbe

 

[eddy_v]

Nu val ik een beetje door de mand..
Er staan fouten in mijn lijst.

Er kan nooit 2x het zelfde call nummer voorkomen
Een call nummer bestaat zoals ik al zei uit 6 cijvers met een vervolg nummer van 2 cijvers.
\
Als ik het call nummer 460123-01 en we moeten nog eens terug naar deze call wordt het dus 460123-02. Nu wil ik dus in de lijst testen of en hoevaak het zelfde call nummer voorkomt (dus de eerste 6 cijvers) als het goed is komt er dus nooit 2x 460123-01 voor maar wordt het dus 460123-01 en 460123-02 en dan komt dus 460123 2x voor. En dat is wat ik dus wil zoeken.

Gr Eddy.

 

[Cobbe]

Dan doe je toch de test met Links(A:A;6)

Cobbe

 

[eddy_v]

Dat is inderdaad een mooie oplossing.

Dan kan ik in iedergeval zien welke calls meerdere keren en hoeveel keren ze voorkomen.
Maar hoe kan ik nu met de gegevens vanuit kolom C er voor zorgen dat bv op blad2 een mooi rijtje kom te staan met de calls die 1x voorkomen. Met de calls die 2x voor komen en met de calls die 3x voor komen??

Gr Eddy.

 

[bandito bob]

Hier een oplossing met een hulpkolom (B).

 

[danny147]

Beste eddy_v

Heb ook een poging gewaagd.

Eerst heb ik alle lege rijen verwijderd met een code, toegangkelijk via een knop.

Sub verwijderen()

Dim c As Range
For Each c In Range("A1:A1140")
If c = "" Then
c.Rows.EntireRow.Delete

End If
Next
End Sub

Dan met een formule in kolom C naar unieke records zoeken.
En in kolom D het aantal , keer voorkomnd nummer.

Groetjes Danny. :thumb:

 

[trucker10]

Beste eddy_v

Heb ook een poging gewaagd.

Eerst heb ik alle lege rijen verwijderd met een code, toegangkelijk via een knop.

Sub verwijderen()

Dim c As Range
For Each c In Range("A1:A1140")
If c = "" Then
c.Rows.EntireRow.Delete

End If
Next
End Sub

Dan met een formule in kolom C naar unieke records zoeken.
En in kolom D het aantal , keer voorkomnd nummer.

Groetjes Danny. :thumb:

@danny voor als het ietsjes sneller mag gaan

Sub lege_rijen()
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
End Sub

 

[eddy_v]

Sorry voor mijn wat late reactie...

Maar ben er nog niet helemaal uit.
In de kolomen naast het call nummer staat ook gevens die de call aangaan.

Wat ik graag zou willen is dat ik in bv blad2 een overzicht kreeg van alle call die 2x voorkomen. Op blad3 alle call die 3x voorkomen.

Het is ook geen optie om de lege cellen in de kolom te verwijderen omdat daar weer andere gevens in de kolommen ernaast staan.

Ik kwam een heel eind met de hulp kolom B. Maar dan kwam alleen elke keer de call met laatste item nummer in het overzicht te staan.

Ik blijf stoeien..

 

[bandito bob]

Hier een methode met formules, al kan ik me goed voorstellen dat het met VBA makkelijker kan. Maar ja, ik ben beter met formules, vandaar deze oplossing.

 

[Jack Nouws]

En hier een methode met VBA
(als het nog van toepassing is)

Met vr gr
Jack

 

[eddy_v]

Ik ga hier eens even mee aan de slag.

Moet wel zeggen dan ik van de VBA nog niet veel kaas gegeten heb..

Maar afvast mijn hartelijke dank..

 

[Jack Nouws]

Met deze code zou het eventueel ook lukken
(= wat korter en overzichtelijker)

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

   With Sheets("Blad1").Range("A2", [A65536].End(xlUp))
        On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
            .Offset(, 1).FormulaR1C1 = "=LEFT(RC[-1],6)"
            .Offset(, 2).FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
    End With
        
        With [Blad1!A1].CurrentRegion
            .AutoFilter 3, "1"
            .Copy [Aantal1!A1]
            .AutoFilter 3, "2"
            .Copy [Aantal2!A1]
            .AutoFilter 3, "3"
            .Copy [Aantal3!A1]
            .AutoFilter
        End With

Application.ScreenUpdating = True
End Sub

Met vr gr
Jack

 

[danny147]

Beste eddy_v

Hier een betere versie.

Versie van bandito bob klopt niet omdat er geen cijfers zijn die 3x voorkomen en er zijn er teveel die 2x voorkomen.

Als controle heb ik het aantal cijfers genomen die voorkomen in de lijst nl. 557
Dan de som genomen van aantal 1x voorkomend en 2x voorkomend enz.. en dit klopt.

Zie bestandje.

Groetjes Danny. :thumb:

PS. Er zit nog een foutje in nl. Cijfers die 2x voorkomen laat hij ook 2x zien terwijl het maar 1 keer mag zijn.
Ga dit proberen op te lossen later op de dag.

De versie van bandito bob heeft gesorteerd op de vervolgnummers, vandaar dat het er zoveel zijn.

 

[Haije]

Draaitabel?

Eddy,

kan een draaitabel helpen?

 

[RvanGalenLast]

Beste Eddy,

Je vraag puzzelde mij ook. Vandaar dat ik er mee aan de gang ben gegaan en het geheel via VBA heb geprobeerd. Ik denk een werkbare oplossing te hebben. Omdat ik thuis met een andere versie van Excel werk, zijn er 2 macro's die dezelfde oplossing moeten bieden. Ik hoop dat dit is wat je zoekt. Zie het bijgevoegde bestand.

Ik realiseer mij dat de code her en der wellicht wat korter zou kunnen. Als anderen daar een betere oplossing voor hebben houd ik mij aanbevolen.

Succes ermee.

Groet,

Ronald

 

[snb]

Je zou zoiets moeten automatiseren lijkt me:

Sub tst()
  With Sheets(1).Columns(1)
     .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
     sq = .SpecialCells(xlCellTypeConstants).Resize(, 2)
     sn = WorksheetFunction.Transpose(.SpecialCells(xlCellTypeConstants))
    
    For j = 1 To UBound(sq)
       sq(j, 2) = UBound(Filter(sn, Left(sq(j, 1), 6))) + 1
    Next
    .SpecialCells(xlCellTypeConstants).Resize(, 2) = sq
End Sub

En als je per kolom de nummers met 1, 2 en 3 als volgnummer wil:

Sub tst2()
  Sheets(1).Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  sn = WorksheetFunction.Transpose(Sheets(1).Columns(1).SpecialCells(xlCellTypeConstants))
    
  For j = 9 To 1 Step -1
    st = Filter(sn, Format(j, "00"))
    For jj = 0 To UBound(st)
       If Right(st(jj), 2) = Format(j, "00") Then
          sn = Filter(sn, Left(st(jj), 6), False)
       Else
          st(jj) = ""
       End If
    Next
    st = Filter(st, Format(j, "00"))
    If UBound(st) > -1 Then
       Sheets(1).Cells(1, 5 + j) = j
       Sheets(1).Cells(2, 5 + j).Resize(UBound(st) + 1) = WorksheetFunction.Transpose(st)
    End If
  Next
End Sub

Maar als het erom gaat een nieuw volgnummer voor een 'call'nummer te krijgen kan dat veel simpeler met een UDF:

Function volgnr(c0)
  volgnr = UBound(Filter(Split(Join(WorksheetFunction.Transpose(Sheets("Blad1").UsedRange.Columns(1)), "|#"), "|"), "#" & Left(c0, 6))) + 2
End Function

Zet deze funktie in een aparte macromodule
Zet dan bijv in cel C2 de eerste 6 cijfers van het 'call'nummer.
In D2 zet je dan =volgnr(C2).
dan krijg je in D2 meteen het eerstvolgende volgnummer voor dit 'call'nummer.