[Excel 2003] Lijst ontbrekende nummers in reeks

[Houston3]

Ik heb een lange lijst met ordernummers (100.000 tm 200.000). Deze lijst (kolom A) bevat echter niet alle ordernummers. Ik heb hierop de lijst gesorteerd en heb handmatig meerdere als formules gemaakt die elke keer A1 met A2 vergelijkt en als deze niet hetzelfde zijn in B1 A1+1 doet (het ontbrekende nummer) dit vervolgens ook weer in C1 maar nu als vergelijking B1=A2. Op deze manier kreeg ik echter een hele brede lijst met ontbrekende nummers.
Nu vroeg ik me af of iemand tips had of dit sneller kon.
Nadeel van de lijst is ook dat ik een eindeloze rij kreeg als een ordernummer hetzelfde was, want mijn vergelijk was steeds 1 ordernummer hoger. Als iemand suggesties heeft hoor ik ze graag.
De gewenste output is een splitsing in ontbrekende nummers (lijst) en de dubbele nummers (lijst).

[Dirk]

Maak een reeks 100000-200000, tel met FREQUENCY hoe vaak elke waarde in de lijst voorkomt en gebruik een filter om te filteren op 0 en >1.

[Houston3]

goede tip, bedankt ik was nog niet bekend met die formule
heb je misschien ook een oplossing om een lijst te genereren van alle ontbrekende orders ? Zoals ik al zei heb ik middels als formules wel iets gemaakt maar dit geeft horizontaal veel waarden die ik liever verticaal en direct onder elkaar heb. Hier heb ik zelf nog geen oplossing voor kunnen bedenken.

[Verwijderd]

-voer in een lege cel, bv. A10 (ontbrekend order) de formule in : =A9+1
-kopieer deze cel (A10 dus)
-selecteer kolom A (of beter tot de laatste te vullen cel)
-F5 (ga naar) > speciaal > lege cellen
-plakken & klaar
-eventueel hele kolom nogmaals kopïeren & plakken speciaal>waarden om de formules te verwijderen

[Houston3]

ik denk dat ik jouw uitleg niet helemaal snap of jij de mijne niet.
Ik begrijp nu dat jij simpelweg zegt dat je het ordernummer +1 doet.

De lijst heb ik echter al. Alleen in mijn huidige lijst ontbreken nummers, ik weet alleen niet welke.

in Kolom A heb ik per rij een getal. Wat ik dus wil is dat ik kan zien welke getallen in de reeks ontbreken in kolom A. Even heel kort door de bocht:
A1 = 1
A2 = 3

dan zou 2 dus ontbreken en die zou ik graag eruit willen halen middels een formule. Op dit moment heb ik een als formule in B2, maar op het moment dat ik 2 opeenvolgende nummers heb die ontbreken voldoet de alsformule niet meer. Dus heb ik in C2 nog een formule etc. De gegenereerde nummers wil ik nu echter in 1 lijst in 1 kolom omdat het nu niet meer overzichtelijk is.

[Verwijderd]

ok, niet opeenvolgende ordernummers staan vlak onder mekaar, dat is een ander verhaal.
om dat zo meteen op te lossen heb je m.i. toch wat vba nodig.
om je voorbeeld aan te halen, daar moet dus 1 extra rij tussen (a2-a1-1)
als je zin hebt om dit zelf al wat te zoeken, ga je gang, ik zal morgen wat code posten

[Houston3]

nee er hoeft geen extra rij tussen, ik wil graag dat de ontbrekende nummers niet worden toegevoegd maar apart in een andere lijst komen. Want alle nummers die ik niet weet heb ik nodig.
MAargoed als het gaat om VBA dan houdt het voor mij wel op want daarh eb ik weinig kennis van....toch bedankt voor je hulp

[F_J_K]

Houston3 schreef op dinsdag 19 oktober 2010 @ 23:32:
goede tip, bedankt ik was nog niet bekend met die formule
heb je misschien ook een oplossing om een lijst te genereren van alle ontbrekende orders ? Zoals ik al zei heb ik middels als formules wel iets gemaakt maar dit geeft horizontaal veel waarden die ik liever verticaal en direct onder elkaar heb. Hier heb ik zelf nog geen oplossing voor kunnen bedenken.

Dat is toch precies wat Dirk al aangeeft: maak een hulptabel/kolom met alle mogelijke getallen, tel (met bijv. aantal.als) hoe vaak per getal het voorkomt en filter alles dat >0 is er uit.

[Lustucru]

Ik zie een klein probleempje; een reeks van 100000-200000 is 100000 items groot en excel 2003 trekt maar 65000 rijen. Wordt verdelen over meerdere kolommen.

[F_J_K]

Goed punt, niet aan gedacht 2x 50k of 4x 25k getallen in 1 of meer tabbladen (of zelfs bestanden) verandert trouwens verder niets aan de methode.

Maar sowieso is het een erg groot aantal getallen. Het aantal zou een reden kunnen zijn om het, afhankelijk van de frequentie (no pun intended) waarin je dit gebruikt, niet in Excel te doen maar bijv. een scriptje bakken die een csv uitleest. Met 100k records kan dat qua performance misschien handiger zijn - maar het vereist natuurlijk wel basale kennis van scripting of 'programmeren'.

[Houston3]

nou het is geen probleem om bijv meerdere reeksen te maken in meerdere kolommen. Zolang ik maar een goede manier heb om de ontbrekende nummers eruit te vissen. Dan zou ik gewoon meerdere bestanden met dezelfde formule kunnen maken bij wijze van dus dat zal geen probleem zijn.

[Dirk]

Sheet1: jouw data
Sheet2:
Cel A1 'Ordernummer'
Cel A2 'Aantal'
kolom A reeks 100000-150000
Cel B2: =COUNTIF('Sheet1'!A:A;A2)
Kolom B: cel B2 doorvoeren tot cel B50002 (formule in B50002 is dan dus =COUNTIF('Sheet1'!A:A;A50002))
Menu data, filter, autofilter
In de dropdown bij cel B1 kiezen voor '0'

In Sheet3 hetzelfde voor de reeks 150001 tot 200000

[F_J_K]

Houston3 schreef op woensdag 20 oktober 2010 @ 12:03:
Zolang ik maar een goede manier heb om de ontbrekende nummers eruit te vissen.

Dat is toch al lang aangegeven

Een volledig uitgeschreven voorbeeld lijkt me op GoT niet nodig. Of zien we iets over het hoofd: wat lukt er niet / met welke formules en methoden loop je waar vast?

edit:
Zegt spuit 11, ik zie dat hierboven dat helemaal uitschrijven al is gedaan.

[ Voor 10% gewijzigd door F_J_K op 20-10-2010 12:17 ]

[Dirk]

offtopic:
Ach ja, mijn collega's, die toch ook echt niet dom zijn, hebben ook veel moeite om zoiets om te zetten in formules, schijnbaar is er nog redelijk wat inzicht voor nodig om zoiets te begrijpen. Dus ik dacht, ik schrijf het maar even uit.

[ Voor 9% gewijzigd door Dirk op 20-10-2010 12:22 ]

[Houston3]

Bedankt Dirk (en anderen voor het meedenken!) jouw manier is idd de oplossing voor mijn probleem.


edit: ik heb idd die frequency fout begrepen. Kan gebeuren

[ Voor 25% gewijzigd door Houston3 op 20-10-2010 12:22 ]