Dynamische array in combinatie met index + match

[miso1995]

Beste,

Sinds kort is er vanuit office 365 de mogelijkheid om dynamische array functies te gebruiken, die vaak maar in 1 cel ingevoerd hoeven te worden.

Dat werkt bijna overal helemaal perfect mee, behalve bij de combi index+match bij zoeken naar meerdere criteria.

Op zich is het niet nodig dat dit werkt, maar het zou heel fijn zijn. Het voorbeeld bestandje is een selectie van een bestand met soortgelijke setup, maar dan met 40 tabbladen.

Idee is om alle tabbladen te groeperen met dezelfde layout en dan gebruik te maken van deze array formules, die automatisch de lengte bepalen van de gegevensreeks. Dan heb ik in 1x 40 conversietabellen.

Het mooiste zou zijn om dit zonder macro's te hoeven doen.

In het voorbeeld bestandje, de foute formule, de stam formule en een index functie met 1 criteria waarbij het gewenste mechanisme wel werkt.

Deze vraag is dus alleen voor Office 365 gebruikers waarin deze functionaliteiten al aan uitgeleverd zijn.

Alvast bedankt!

Groetjes,

Miso

 

[JEC.]

Als je er met multiple criteria voor zorgt dat er maar 1 waarde overblijft per rij, kan er nooit een matrix ontstaan

Als je alleen kijkt naar het orientatienummer kan het wel (dan mogen er voor de rest geen dubbele waarden voorkomen)

=FILTER(Centraal!$D$2:$D$9;Centraal!$C$2:$C$9<>"B";"")

 

[miso1995]

Het gaat hier om verschillende rekeningschema's voor verschillende administraties.
Het stukje voorwerk wat hier aan vooraf gaat, is alle schema's onder elkaar plaatsen in de centrale sheet, dan met duplicaten verwijderen op basis van gb en omschrijving de lijst schonen. de codes voor verschillende administraties zijn dus gelijk aan de sheetnaam en is dus steeds in kolom C opgenomen.

VErvolgens zijn alle codes ook op de kopregel geplaatst en is met combinatie van indirect en een beetje juist de verwijzingen relatief maken, in 1 keer door alle sheets gezocht of de combinatie gb rek + omschrijving voorkomen. Zo ja, resultaat is 1 en anders 0.
Op basis van de nullen en enen is bepaald wat de orientatie is. Ofwel All ofwel een specifieke.

Daarna stuk handmatig koppelen aan nieuw rekening schema wat uniform moest zijn. Maar het kan dus bijvoorbeeld ook zijn dat in het nieuwe schema een extra dimensie is toegevoegd waardoor combinatie gb + omschrijving van administratie A op nieuwe gb nummer 1000 wordt gezet terwijl administratie B op nummer 1001 moet. Bij alles met All, is gekeken of er handmatig extra regels toegevoegd moesten worden.

Maar met de wijze waarop de sheet is opgebouwd houd je dus dubbele combinaties met alleen een verschil in orientatie.

Aangezien ik het zie gebeuren, dat dit vaker gaat voorkomen, zou het mooi zijn, als er een formule is om meteen die mooie spreiding te krijgen.

Ik had gehoopt dat dit met index + match zou kunnen.

In dit geval zou ik een kolom in de centrale sheet kunnen toevoegen die van het nieuwe gb schema numerieke waardes maakt. Vervolgens zou ik de functie sumifs in combinatie met offset zo kunnen formuleren dat ie hem over het hele bereik heen gooit voor alle sheets in 1 keer.

Echter dit werkt alleen als de uitkomst nummeriek moet zijn en dat de combinatie gb + omschrijving + orientatie slechts 1 maal voorkomt.

Vooruitkijkend op andere vergelijkbare situaties met uitkomst Text, zou het super zijn om dit met dynamische multi-cel arrays te doen met behulp van index + match.

Maar ik wil eigenlijk alleen weten of wat ik in mijn hoofd heb, kan (ik kan het op internet namelijk niet terug vinden). Met VBA is er uiteraard wel een oplossing voor te verzinnen, maar ja dat is mijn laatste alternatief.

In ieder geval thanks voor je uitleg JVeer waarom mijn idee niet werkt.

 

[JEC.]

Ik zou in dit geval voor VBA gaan, zonder inzicht te hebben in je bestand. Misschien dat r nog iets aan je opzet te doen is, waardoor je makkelijker kunt rekenen