Meerdere data regels zoeken

[Shery]

Kan iemand mij helpen met het volgende probleem

Ik wil graag uit een lijst met data alle gegevens kopiëren naar een aparte lijst (Blad: Data niet in vaste nr voorkomt) Maar alleen de regels waarvan het G1-00XXX NIET in de lijst (Blad: vaste Nummers) voorkomt. Dit kunnen ook meerdere regels met het zelfde nummer zijn.
ik weet even niet hoe ik dit voor elkaar kan krijgen

Ik hoop dat de bijlagen wat duidelijkheid brengt

 

[VenA]

Gebruik een extra kolom en gebruik de functie AANTAL.ALS(). Met het autofilter kan je dan eenvoudig de waarden die niet voorkomen filteren.

 

[JEC.]

Of met 1 matrix formule

=ALS.FOUT(INDEX('Vaste Nummers'!$B$2:$B$96;KLEINSTE(ALS(ISNB(VERGELIJKEN('Vaste Nummers'!$B$2:$B$96;Data!$B$2:$B$201;0));RIJ($1:$95));RIJ(A1)));"")

Afsluiten met control shift enter en doortrekken

 

[AlexCEL]

Ik lees de vraag zo: als het G1-nummer op blad "data" niet in de lijst op blad "vaste nummers" voorkomt, dan de gegevens uit data overnemen op laatste tabblad... En laat nu elk nummer op tabblad data gewoon voorkomen in de lijst (check via aantal.als methode uit #2). Er is dus niets over te nemen in het voorbeeld?

 

[JEC.]

Zo las ik het ook. Ik ging er vanuit dat TS een foutje maakte in de vraagstelling

 

[Shery]

VenA en Jveer
bedankt voor het mee denken
Maar wat ik eigenlijk bedoel is dat de hele regel van Bald DATA op het blad Data niet in vaste nr voorkomt wordt gekopieerd als het nummer niet in bald Vaste Nummers staat
Ik denk dat het alleen goed kan werken met vba en een opdracht knop

 

[AlexCEL]

Zoals ik al stelde in post #4 dus. Als je met Excel 365 werkt dan kan deze oplossing. Tabelkopje toevoegen op rij 1 op alle bladen. Daarna kun je daarna doortrekken naar rechts. Excel 365 maakt er zelf een lijst van.

=FILTER(Data!A2:A201;AANTAL.ALS('Vaste Nummers'!$B$2:$B$96;Data!$B$2:$B$201)=0;"")

Zie ook bijlage. Ik heb handmatig een paar nummers toegevoegd in datalijst die niet voorkomen in de lijst met vaste nummers, om wat resultaten te kunnen produceren. In je voorbeeld zijn er geen waarden die niet voorkomen.

 

[VenA]

Het is allemaal niet geheel duidelijk inderdaad. Zijn beide bereiken dynamisch? Of staat de lijst in de tab 'Vaste nummers' vast? Als het een eenmalige actie is waar het op lijkt dan heb je voldoende aan de suggesties in #2.

Als je een macro wil dan wordt het zoiets.

Sub VenA()
  Range("H2").FormulaR1C1 = "=COUNTIF('Vaste nummers'!R2C2:R96C2,RC[-6])=0"
  Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, Range("H1:H2"), Sheets("Data niet in vaste nr voorkomt").Range("L1"), False
  Range("H2").Clear
End Sub

@AlexCEL, Heb jij de nieuwe functies zoals FILTER(), SORTEREN() al eens getest op een grote dataset en werken deze dan ook nog 'lekker'? Het blijven natuurlijk matrixfuncties en die zijn over het algemeen niet zo heel snel. (Kan het zelf natuurlijk ook wel een keer testen)

 

[AlexCEL]

@VenA: ik heb de indruk dat de functies sneller zijn, maar bewijs heb ik niet. Echt grote datasets werk ik dan ook niet mee. UNIEK, FILTER, SORTEREN, SORTEREN.OP zijn m.i. wel zeer nuttige toevoegingen aan Excel. Als je dezelfde functionaliteit in eerdere versies van Excel wilt hebben moet je toch aardig meer ingewikkelde formules gebruiken.

 

[VenA]

@AlexCEL, Ok bedankt voor het antwoord. Wel nuttig dat jij regelmatig deze nieuwe functies gebruikt en hier plaatst.:thumb:

 

[Shery]

VenA en AlexCEL jullie zijn wel toppers
ik heb alle twee de mogelijkheden geprobeerd en het werkt formidable

Ik geef zelf toch de voorkeur aan vba en niet de formule (die is echt nog veel te hoog gegrepen voor mij )
en met vba kan ik stap voor stap nog even kijken wat er gebeurd

bedankt voor de snelle reactie

 

[Shery]

VenA,

De macro werkt prima. heel knap dat je met zo weinig regels zo veel kan doen. Alleen zou ik het fijn vinden als na het uitvoeren van de macro de Sheets("Data niet in vaste nr voorkomt").
geselekteerd zou worden. als ik dat nu prober werkt je macro gelijk niet meer ;-(

ik probeerde het met:
Sheets("Data niet in vaste nr voorkomt").Select

 

[cow18]

activate ipv select

 

[Shery]

Graag nog een keer hulp bij de volgende stap
Alles werkt goed zoals in de bijlagen te zien is maar ik heb toch nog een wensje
Graag wil ik dat als je op de sheet “data” de button gebruikt en de macro activeert na het uitvoeren van de macro de sheet “SBK Off” geselecteerd wordt.
Ik heb de opmaak wat aangepast zodat het wat netter eruit ziet.
ik heb zelf een paarkeer naar de formule gekeken maar zoals jullie dat gedaan hebben VenA en AlexCEL lukt het me niet deze te doorgronden.
Nog even voor de duidelijkheid
Sheet BackBone bevat een vaste lijst met nummers
Sheet data bevat de data waaruit de units gezocht moeten worden welke niet in de sheet Backbone staan (hier kopieer ik de data met de hand in)
Sheet data off bevat het resultaat (hier heb ik een button gemaakt om het blad weer te wissen als ik de volgende data stroom wil controleren)