Zoeken met index en verschuiving

[excelyeshead]

Hallo Excel oplossers,

Ik heb een vraag en toch al een tijdje rond gezocht op dit forum maar ik kom er niet uit.
In bijgevoegd bestandje staan 3 kolommen. In kolom C staan doosnummers (soms vaker voorkomend) en in kolom A staan de een lijst met hoofd artikelnummers waar deze dozen voor gebruikt worden. In kolom E staat een vereenvoudigde lijst van kolom C

Nu wil ik eigenlijk een dynamische index formule hebben. Ik wil een lijst zoals in Kolom A hebben met een zoekfunctie die Kolom E gebruikt als zoekwaarde.

Dus als 1e doosnummer in kolom E niet meer voorkomt dat doorspringt naar 2e doosnummer in kolom E

Wie kan mij ermee helpen.

Alvast bedankt.

Martin

 

[AlexCEL]

Welkom. Dank voor het voorbeeld, wordt vaak vergeten. Ik kan er helaas geen chocola van maken. Licht dit eens toe: "Ik wil een lijst zoals in Kolom A hebben met een zoekfunctie die Kolom E gebruikt als zoekwaarde. Dus als 1e doosnummer in kolom E niet meer voorkomt dat doorspringt naar 2e doosnummer in kolom E".

WAT moet WAARIN opgezocht worden? Kun je handmatig voor een aantal rijen eens de gewenste uitkomst aangeven (incl. WAAROM die uitkomst)?

 

[excelyeshead]

Hallo AlexCel,

Bedankt voor je reactie,

Ik begrijp het al je zegt er is geen chocola van te maken is. Ook niet zo handig omschreven.

Ik heb een nieuw bestandje geupload met in kolom G1 de omschrijving van de gewenste formule. In kolom Q staat het resultaat dat ik graag zou zien.

Alvast bedankt voor het meedenken.

Martin

 

[excelyeshead]

Iemand nog een idee?

Kom er niet verder mee.

Martin

 

[AlexCEL]

Ik ook niet, vandaar dat ik even wachtte op reacties van anderen, maar die kunnen net als ik dus ook nog niets met het (aangepaste) voorbeeld.

"Als E2 => zoeken in kolom A (tot er niets meer met E2 = kolom C te vinden is )" Wat bedoel je hier nu mee? In E2 staat dit "12.99.12.01 1/1". WAT moet hiervan opgezocht worden in kolom A? De waarde komt niet voor in kolom A. WAAROM is de uitkomst dan in Q3 dan 12.93.12.00. Het lijkt erop dat dit je per doos de hoofdartikels wilt weten in kolom Q. Maar hoe precies? Nogmaals: je zult wat mij betreft duidelijker moeten zijn in de uitleg.

Op dit moment zou ik zeggen: kopieer kolom A en je bent klaar, want kolom Q is precies gelijk aan kolom A.

 

[jkpieterse]

Zo iets?

 

[AlexCEL]

Goedemorgen JK,

dat is een hele mooie formule om kolom A te kopiëren! :thumb:

 

[jkpieterse]

Dat dacht ik ook Kan vervangen worden door =A2. Werkt ook "iets" sneller bij herberekenen

 

[excelyeshead]

Beste AlexCel / JkPieterse,

Bedankt voor het meedenken. We komen al een heel eind. Alleen nog net niet wat ik eigenlijk nodig heb.

De gemaakte formule van Pieterse verwijst niet naar cel E2. De zoekformule moet eerst naar E2 verwijzen. De formule moet als volgt eruit zien:

Zoek met Cel E2 in kolom "A" naar het hoofartikel. In kolom C staat dezelfde voorwaarde (als in E2) waar hij aan moet voldoen. In dit geval 3 artikelen. Als niet meer dezelfde voorwaarde geldt voor Cel E2 en kolom C dan moet de zoekfunctie verspringen naar cel E3 en dan opnieuw gaan zoeken in kolom "A". Hier komen er weer 3 artikelen naar voren. Daarna verspringt hij naar cel E4. Hier komen dan 12 hoofdartikelen te voorschijn.

Ik kan wel een functie schrijven met "als fout" dat dan de formule naar E3 moet springen maar er zijn heel veel dozen en zoek dus iets met een verschuiving.

Ik zie graag jullie oplossing tegemoet!

Martin

 

[jkpieterse]

Ik snap er niets van.

 

[AlexCEL]

Ik ook niet.

 

[excelyeshead]

Hallo Alex en Pieterse,

Ik zal het nog een keer proberen het te omschrijven. Normaal gesproken zou ik deze formule in Cel F2 zetten:

{=INDEX($A$2:$A$28;KLEINSTE(ALS($C$2:$C$28=E$2;RIJ($A$2:$A$28)-RIJ($C$2)+1;"");RIJ()-RIJ(F$2)+1))}

Maar als ik deze formule door kopieer naar beneden dan krijg je in Cel F5 een foutmelding "#GETAL!" Als deze foutmelding komt moet dan zou het volgende moeten gebeuren in de formule:

- cel E2 naar cel E3
- cel F$2 zal dan naar het nummer van de cel waar de foutmelding begint verspringen. Dan zou je zoiets als dit krijgen in cel F5:

=INDEX($A$2:$A$28;KLEINSTE(ALS($C$2:$C$28=E$3;RIJ($A$2:$A$28)-RIJ($C$2)+1;"");RIJ()-RIJ(F$5)+1))

Ik heb de formules in het bijgevoegde bestandje geplaatst!

Ik hoop dat dit meer duidelijk maakt.

Ik zie jullie reactie graag tegemoet.

Martin

 

[VenA]

Er is niet veel van te maken. Misschien is jouw voorbeeld te perfect waardoor je iedereen op een dwaalspoor zet. Wat heb je altijd? kolom A en kolom C? Waarom is kolom B dan leeg? Kolom E kan variabele doosnummers bevatten en op basis van deze nummers wil je de onderliggende artikelen zoeken?

 

[excelyeshead]

Hallo VenA,

Ja misschien wel te complex omschreven. Maar kolom A en C staan normaal op een ander tabblad maar nu op 1 tabblad gezet voor het voorbeeld.

"Kolom E kan variabele doosnummers bevatten en op basis van deze nummers wil je de onderliggende artikelen zoeken?"

Ja correct dit is wat ik zoek.

Ben benieuwd of je hier mee verder kan.

Alvast bedankt voor het meedenken.

Martin

 

[VenA]

Plaats dan een relevant voorbeeldbestand.

 

[HSV]

Sub hsv()
Dim cl As Range, c As Range, firstaddress As String
  For Each cl In Columns(5).SpecialCells(2).Offset(1).SpecialCells(2)
    Set c = Columns(3).Find(cl.Value)
     If Not c Is Nothing Then
       firstaddress = c.Address
     Do
       Cells(Rows.Count, 8).End(xlUp).Offset(1) = c.Offset(, -2)
       Set c = Columns(3).FindNext(c)
     Loop While Not c Is Nothing And c.Address <> firstaddress
     End If
  Next cl
End Sub

Idem:

Sub hsv()
Dim sv, c As Range, firstaddress As String, i As Long, n As Long
sv = Cells(1).CurrentRegion.Resize(, 8)
 n = 2
  For i = 2 To Cells(Rows.Count, 5).End(xlUp).Row
    Set c = Columns(3).Find(sv(i, 5))
     If Not c Is Nothing Then
       firstaddress = c.Address
     Do
       sv(n, 8) = c.Offset(, -2).Value
       n = n + 1
       Set c = Columns(3).FindNext(c)
     Loop While Not c Is Nothing And c.Address <> firstaddress
     End If
  Next i
 Cells(1, 9).Resize(UBound(sv), 8) = sv
End Sub

 

[excelyeshead]

Beste HSV,

Bedankt voor je VBA code. Deze werkt prima.
Ik ga hem nu ombouwen naar mij excel sheet en hopelijk krijg ik hem aan de praat.

Als ik hier uitkom dan zal ik deze topic sluiten.

Alvast bedankt allemaal voor het meedenken!

Martin

 

[excelyeshead]

Beste helpers,

Ik dacht dat ik de formule wel kon ombouwen naar mijn bestand. Dit ging gedeeltelijk maar bij het bronbestand kreeg ik toch weer een aantal vragen. Dus heb ik het bronbestand maar geupload en dit had ik veel eerder moeten doen (sorry en je had gelijk VenA)

Laat ik maar weer bij het begin beginnen.....
In tabblad Artikelen staat 2 kolommen. Kolom A de Hoofdartikelen. In kolom C alle aanwezige artikelnummers (hoofdartikelen, subartikelen,doosnummers)

In Blad1 filter ik aan de hand van rij 5 de aanwezige doosnummers. Dus dit artikel kan in deze doosnummer voorkomen!

Omdat dit doosnummer een Sub- nummer kan zijn van een hoofdartikel ga ik deze loop nog een keer doorlopen in Blad2
In Blad2 staat het eindresultaat wat ik zou willen zien!. Ik heb hier ook extra uitleg bij gedaan.

Sorry voor de moeite die al in de vorige sessie is gestopt maar ik hoop dat jullie hier nog een keer naar willen kijken.
Het resultaat mag ook in 1 keer worden berekend (zonder Blad1)

Bedankt alvast.

Martin

 

[excelyeshead]

Nog iemand een idee?

 

[VenA]

Over welke formule heb je het? Of gaat het om de code in #16 die je niet aangepast krijgt?