Dichtstbijzijnde datum & 2 voorwaarden

[rozebooz]

Goedemiddag,

Al enige tijd ben ik met dit excel bestand bezig en hoef enkel de laatste loodjes nog waar ik helaas niet door heen kom. Ik hoop dat iemand mij uit de brand kan helpen.
Zelf ben ik nogal een leek met excel (al wordt ik inmiddels steeds handiger)

Wat ik graag zou willen:


Ik zoek in blad "Bonnen" of in blad "item sales" de rij die precies match met het bedrag, met een rij in blad "Bank"
Op de rij in Blad "Bonnen" of Blad "item sales" moet wel het woord: Card vermeld staan bij betaaltype.
Het probleem is dat alleen de datum in blad "bank" kan afwijkend, ik zoek de lijn met een datum die het dichtste bij ligt met deze 2 voorwaarden.

Bij de bank komt het bedrag nagenoeg altijd zelfde dag of later binnen. Een code voor in het blad "bank" om de juiste bijpassende datum te vinden voor in iedergeval 1 ander blad is: {=MAX((Bonnen!$A$2:$A$500<=B1)*Bonnen!$A$2:$A$500)}

Nu wil ik een formule maken met deze voorwaarden
(al durf ik niet te zeggen wat je juiste volgorde is):
- Bedrag van blad "Bank"kolom F precies overeenkomt met blad "Bonnen"kolom H Of Blad "Item Sales"kolom P
- Het woord "Card" moet worden vermeld bij Blad "Bonnen" kolom F of bij blad "Item Sales" kolom E het woord Card staan.
- De datum van blad "Bonnen"kolom A of de datum van blad "item Sales"kolom B zo dicht mogelijk van die van blad "Bank"kolom B liggen
Als deze rij is gevonden wil ik uit die rij de btw gegevens in blad "Bank"kolom G zetten ("Bonnen" rij G of "Item sales" rij S)
Uit eindelijk wil ik nog meer gegevens er naast zetten, maar dan moet ik ze eerst kunnen vinden


Als het niet kan om uit 2 bladen op informatie te zoeken via een formule of te ingewikkeld is voor een formule, dan kan ik het blad "Bonnen" & "Item Sales" samen voegen zodat de formule op 1 blad de gegevens hoeft te vergelijken, maar liever niet.

Ik hoop dat iemand mij vooruit kan helpen. Alvast bedankt voor de moeite.

Bekijk bijlage Bestand gegevens.xlsx

 

[rozebooz]

Inmiddels heb ik deze code gevonden die werkt voor de datum:

{=MAX((Bonnen!$A$2:$A$500<=B1)*Bonnen!$A$2:$A$500)}

Hiermee vind hij de datum die precies match of als er geen match de dichtstbijzijnste datum voor de opgegeven datum.

Nu zou ik graag nog willen dat er wordt gecontroleerd op "Card" en het bedrag dat matcht. Enkel kom ik er niet uit hoe ik dat in een code moet verwerken. Waar wat moet staan haakjes cijfers. mijn excel staat momenteel vol met probeersels die niet werken op een paar halve codes na.

Wie kan mij op weg helpen?

Bekijk bijlage Bestand gegevens.xlsx

 

[Cobbe]

Rij E, rij H, rij F enz... bestaan allemaal niet.
Uw vraag is een warrige bedoening,
lees uw vraag nog eens door en formuleer eens in logische volgorde wat de bedoeling is, liefst een voor een de problemen aanpakken.
En belangrijk: vóór het posten - uw vraag nog eens goed doorlezen wil wel eens helpen.

 

[rozebooz]

Goedemiddag Cobbe,

Bedankt voor je feedback. Ik zie nu in dat ik de geheel onjuiste benamingen heb gebruikt voor de rijen en kolommen. Ik hoop dat het zo iets duidelijker is, ik heb mijn bericht aangepast.

 

[rozebooz]

een kleineer bestand toegevoegd

Ik hoop met dit bestand duidelijker te kunnen overbrengen wat ik zoek:

wat ik bij btw en categorie zou willen zien verschijnen
zijn de gegevens uit Blad2 & blad3 (de btw en de catogorie gegevens) van de dichtstbijzijnde datum in het verleden
voorwaarden is dat de prijs overeenkomt en betaalmethode Card is geweest.

Bekijk bijlage voorbeeld.xlsx

 

[sylvester-ponte]

dit soort dingen doe ik meestal in vba is dat een probleem?

ps je schrijft dat de datum er zo dicht mogelijk bij moet liggen, dat kan dus eerder en ook later zijn, klopt dat?

in je eenvoudige voorbeeld staan de prijs datum btw en pay methode steeds in de zelfde kolom.
is dat straks in de praktijk ook zo?
of verschild dat per blad?

 

[rozebooz]

Hallo Sylvester ponte,

Bedankt voor je reactie. Ik heb zelf geen ervaring met vba, maar mocht dit eventueel kunnen werken dan wil ik het zeker proberen en mijzelf inlezen hoe het werkt. Ik vermoed dat het mij wel moet lukken als ik weet wat voor codes ik moet invoeren.

De datum dat het bedrag op de bank (het 1ste blad waar ik de gegevens voor wil vinden) binnenkomt zal nagenoeg altijd later zijn of dezelfde dag. De 2 bladen waar de gegevens staan zijn de bonnetjes/facturen en behandelingen. Misschien dat het een enkele keer anders is maar dat vermoed ik niet. Mijn bedoeling was om ook nog een cel een kleur te geven als hij geen match kan vinden.

De kolommen zullen zijn zoals in het eerste bestand die ik heb geupload met 4 bladen. Ik het 2de blad staat btw in kolom G. in blad3 staat Btw in kolom S.

Paymethode: staat in blad 2 "bonnen" in kolom F. In blad 3 "item sales" staat het in kolom E.

 

[rozebooz]

Helaas kom ik er naar verschillende pogingen nog steeds niet goed uit. Vba en excel heb ik weinig kennis van en kom ik helaas niet mee vooruit wat voor combinatie/code ik moet gebruiken. Ik hoop dat misschien hier iemand mij vooruit kan helpen.

In dit bestand is alles zoals ik de gegevens in het bestand zal zetten, door middel van kopiëren plakken. Geel zijn kolommen met codes waar ik zelf mee aan de slag ben geweest. deze werken niet omdat als er geen match is gevonden, deze niet naar een andere rij(volgende dichtstbijzijnde datum) gaat om verder te zoeken naar voorwaarden die wel voldoen.

De geheel oranje gekleurde kolommen zijn de datums en voorwaarden waar aan voldaan moet worden. In blad "bonnen"en "item Sales" staan de kolommen met bijpassende kleur aangegeven, datum, bedrag, betaalmethode voor de voorwaarden "Card" .

Blauw Groen en paars zijn de kolommen waar ik gegevens zou terug willen zien in blad Bank. In blad "bonnen" en "item Sales" staan de Blauwe Groene en paarse kolom waar de gegevens staan die ik wil matchen. Ik weet dus niet zeker of het mogelijk is om uit 2 bladen tegelijk te zoeken/vergelijken. Het zou super zijn als het uit 2 bladen zou kunnen, maar als het uit 1 al kan (vanuit blad "item sales") ben ik ook heel mee.

Bekijk bijlage Gegevens.xlsx

 

[sylvester-ponte]

kijk eens of je hier iets me kan zie rode kolommen.

ps ik ben vergeten de formules zo aan te passen dat de prijs precies klopt.
maar ja dan heb je bijna geen raakpunten meer.

deze aanpassing komt later.

 

[rozebooz]

Dank je wel voor je moeite en tijd.

Ik moest even kijken hoe dit werkte en het ziet er mooi uit. Enkel loop ik nog tegen het probleem aan dat het bedrag in blad"Bank" niet matcht met het bedrag van de gevonden Rij. Nu heb ik zitten kijken en puzzelen of ik hier iets in kan aanpassen, maar het gaat mij de pet te boven. Waar zou eventueel iets kunnen worden aangepast zodat ook wordt gekeken naar het bedrag uit de eerst gevonden rij? Als dit bedrag niet matcht hij dan naar de eerstvolgende rij kijkt of de voorwaarden kloppen, enzovoort en als er geen matcht dat hij dan gewoon niks aangeeft of een (-)?

 

[sylvester-ponte]

kijk eens of de btw bedragen en berekende rijnummers ed kloppen.

 

[rozebooz]

Ik ben alweer enige tijd aan het stoeien met het bestand. Wat je gemaakt heb ziet er goed uit en heb nog niet gemerkt dat het niet werkt. Super blij mee!:d

Wat ik nog wilde aanpassen is:

Onder blad ItemSales staat de kolom "Invoice Number". Er zijn bonnen die in meerdere delen zijn opgesplitst (rood aangegeven), deze bonnen zou ik ook graag zo opgesplitst houden(of het moet niet anders kunnen). Nu dacht ik met een draaitabel de bedragen van de opgesplitste rijen samen te voegen door middel van invoice number en de formule hier op aan te passen. Maar vervolgens loop ik tegen de lege cellen aan in de draaitabel onder de kolom "Datum" (dacht ik omheen te kunnen) en de formule.. Ik krijg het niet voor elkaar, waar gaat het fout?

Bekijk bijlage Gegevens 4.xlsx

 

[rozebooz]

Ik zie dat ik een fout over het hoofd heb gezien die ik heb gemaakt.! Het gaat uiteindelijk wel lukken verwacht ik.