Macro voor unieke waarden

[90benjamin90]

Hallo,

Ik zit al enkele dagen met een probleempje en ik kom er niet uit! Heb google afgezocht en ook hier op dit forum heb ik verschillende topics behandeld maar het lukte mij niet.

Aan de hand van de bijlage moet er meer duidelijkheid worden verschaft.
Bekijk bijlage dubbele waarden.xls

Nu wil ik het volgende:

In kolom U wordt de totale lengte van datzelfde profiel in die rij opgeteld. Dit wordt gedaan door het aantal maal de lengte te doen.

Nu wil het vaak voorkomen dat er dezelfde profielen zijn, zoals in de bijlage de St120 x 10.

In kolom V heb ik een formule bedacht die de lengtes van dezelfde profielen optelt. Alleen nu zijn er in dit geval 6 dezelfde profielen (namelijk St120 x 10) maar daarachter staat wel in dus 6 rijen, de totale lengte van de profielen.

Wat wil ik nu dan? Dat deze data wordt gefilterd. Met Data>filter>uitgebreid filter geeft mijn sheet een foutmelding. "De veldnaam in het ophaalbreik ontbreekt of is ongeldig.

De volgende invoer heb ik in het menu van het uitgebreid filter gezet:
Kopiëren naar andere locatie aangevinkt.
Lijstbereik: $V$6:$V$17
Criteriumbereik: $V$6:$V$17
Kopiëren naar: W6
Alleen unieke records worden aangevinkt.

Dan komt de boven vermelde foutmelding dus te voorschijn.

Heb dus gezocht naar een macro maar omdat ik daar (nog) geen kaas van heb gegeten snap ik daar erg weinig van. Ik kwam op deze topic met een daarbij lijkende simpele macro van HSV. Heb geprobeerd om dit aan te passen maar dat lukt mij dus niet.

Heeft iemand hier een oplossing voor of een idee hoe ik dit het beste aan kan passen?

Alvast bedankt!

 

[sa11]

@90benjamin90

Het kan ook met een formule zie kolom W.

succes...

 

[90benjamin90]

Bedankt sa11!

Het werkt inderdaad. Alleen nu ik de formule overkopieër in de sheet waarin ik werk doet hij het niet meer. Ik heb uiteraard de matrix aangepast zodat deze in het goede gebied zoekt.

Ik denk dat er iets verkeerds is met de celverwijzing A1 na de RIJ code. Waar moet deze precies naar verwijzen?

=ALS(SOMPRODUCT((Criteria<>$V$43:$V$54)*1)<RIJ(A1);"";INDEX(Criteria;KLEINSTE(ALS(VERGELIJKEN(Criteria;Criteria;0)=RIJ(Criteria)-5;RIJ(Criteria)-5);RIJ(A1))))

Het eerste getal wordt wel getoond maar de daaropvolgende getallen (die door jou formule worden weergegeven zouden moeten worden) staat #getal in de cel. Hoe kan dit?

 

[sa11]

@90benjamin90

het is een matrix formule vergeet niet af te sluiten met 2 accolades {}.
dat doe je door op ctrl+shift+enter af te sluiten ipv enter.

[COLOR="darkred"]{[/COLOR]=ALS(SOMPRODUCT((Criteria<>$V$43:$V$54)*1)<RIJ(A1);"";INDEX(Criteria;KLEINSTE(ALS(VERGELIJKEN(Criteria;Criteria;0)=RIJ(Criteria)-5;RIJ(Criteria)-5);RIJ(A1))))}

Mvg

 

[90benjamin90]

Dit is ook zo. Heb ik aangepast. Nu komt er #N/B te staan.

Ik heb dus het matrixbereik aangepast. Wel vreemd overigens dat deze matrixbereik niet de eerste rij meeneemt van de tabel maar wel een extra rij aan de onderkant. Voor de rest staat er bij de criteria nog steeds cel A1, en daar staat al wat in. Denk dat hier de fout in zit. Naar welke cel moet ik deze laten verwijzen?

 

[Roncancio]

De A1 staat wel goed omdat de rijnummer wordt gebruikt en niet de celinhoud.
Het kan zijn dat je foutmelding komt omdat het aantal rijen in het bereik niet overeenkomt.

Simpel voorbeeld:

Somproduct((A1:A10="Test")*(B1:B10="Oke")*(C1:C[B][COLOR="red"]9[/COLOR][/B]))

Je ziet dat de bereiken in het voorbeeld niet hetzelfde aantal rijen hebben.

Met vriendelijke groet,


Roncancio

 

[sa11]

@benjamin

het ligt niet aan rij(a1). formule verwijst niet naar de inhoud van A1 maar deze verwijst naar rij(A1) dat is rijnr 1. rij(a2) is rijnr 2. rij(a3) is rijnr 3. enz...


vervang de formule door de onderstaande formule en kijk of het beter is. eerste is ook goed alleen heb je niet goed toegepast denk ik.

{=ALS(SOMPRODUCT((Criteria<>"")/AANTAL.ALS(Criteria;Criteria&""))<RIJ(A1);"";INDEX(Criteria;KLEINSTE(ALS(VERGELIJKEN(Criteria;Criteria;0)=RIJ(Criteria)-5;RIJ(Criteria)-5);RIJ(A1))))}

succes..

 

[90benjamin90]

Hij geeft weer een foutmelding! Snap er niks meer van. Kan ik jou het gehele bestand sturen? Het excel bestand is meer dan 1 MB, en in een ZIP file is hij nog steeds 180 kb.

Weet niet wat ik fout doe!

 

[cow18]

maak een draaitabel en je hebt geen last van formules

 

[sa11]

Probeer eens de volgende site's om een bestand te uploaden (ook grotere bestanden):

Mijnbestand
File-Upload.net
MegaUpload


succes...

 

[90benjamin90]

Hier dan het bestand: http://www.mijnbestand.nl/Bestand-AZQ74UFUH87L.rar

Het gaat om de cel W43 van blad invoer. Hopelijk lukt het!

 

[sa11]

@benjamin

verander -5 naar -42. dan werkt de formule wel goed.de bovenste 42 rijen doen niet mee
in deze formule.

{=ALS(SOMPRODUCT((Criteria<>"")/AANTAL.ALS(Criteria;Criteria&""))<RIJ(A1);"";INDEX(Criteria;KLEINSTE(ALS(VERGELIJKEN(Criteria;Criteria;0)=RIJ(Criteria)[COLOR="darkred"]-42[/COLOR];RIJ(Criteria)[COLOR="darkred"]-42[/COLOR]);RIJ(A1))))}

succes

 

[90benjamin90]

Erg bedankt sa11! Hij werkt eindelijk. Bedankt! Ik zet hem op opgelost.

 

[90benjamin90]

Heb ik toch nog een klein vraagje. Met Criteria wordt het bereik van de matrix gezegd. Maar nu wil ik dit bereik, die matrix veranderen omdat 100 cellen daaronder precies hetzelfde moet gebeuren. Tevens moet het criteria wat jij hebt gemaakt nog 1 cel groter worden, namelijk van cel 43 tot 54.

 

[sa11]

@benjamin

ik heb office 2007 maar volgens mij is dat ongeveer dezelfde als bij office 2003.
ga naar naam beheren en daar de bereik van criteria aanpassen. zie bijlage.

misschien ligt aan mij maar de eerste vraag snap ik niet zo goed.

succes...

 

[90benjamin90]

Erg bedankt Sa11!!

Wat ik wilde...
Ik wilde dat in een andere tabel (die 100 cellen lager staat) precies hetzelfde doen als wat ik in de eerste post (probeer) te vragen. Door die criteria die voor cel 43 tot cel 54 staat, te wijzigen in criteria 2 die van cel 103 tot 114 gaat is dit nu gedaan. Moest de-42 veranderen in -102 omdat de bovenste cellen niet mee doen. De formule wordt dan:

=ALS(SOMPRODUCT((Criteria2<>"")/AANTAL.ALS(Criteria2;Criteria2&""))<RIJ(A1);"";INDEX(Criteria2;KLEINSTE(ALS(VERGELIJKEN(Criteria2;Criteria2;0)=RIJ(Criteria2)-102;RIJ(Criteria2)-102);RIJ(A1))))

 

[sa11]

@benjamin

is het nu gelukt met vraag2(criteria2 )of heb mijn hulp nog nodig.

Mvg

 

[90benjamin90]

Het is opgelost en heb de topic op opgelost gezet. Nogmaals, erg bedankt!