voorwaarden

[Niels747]

Ik heb een rapport dat binnen komt in excel vanuit een ander programma.
En ziet er als volgt uit.
Kolom A Kolom B
G-SNSB/L-271016/25HRS G-SNSB
G-SNSB/L-271016/DYE-PEN DAMPER G-SNSB
PH-EUF/L-271016-2/COLL FAIL PH-EUF
PH-EUF/L-271016/25HRS PH-EUF

Nu wil ik graag dat de waarde van kolom A verschijnt in een cel op tabblad 2. Deze cel is altijd voor de waarde in Kolom B.
Dit kan ik doen door ==Sheet1!A2

Echter de dag erna kan het rapport er als volgt uit zien.
Kolom A Kolom B
PH-SHL/L-271016/25HRS PH-SHL
G-SNSB/L-271016/DYE-PEN DAMPER G-SNSB
PH-EUF/L-271016-2/COLL FAIL PH-EUF
PH-EUF/L-271016/25HRS PH-EUF

Hoe kan ik nu de voorwaarde zo maken dat de cel bestemt voor de waarde G-SNSB op tabblad 2 alleen gevuld wordt als de cel B2 op tabblad 1 ook G-SNSB is?

 

[AlexCEL]

Een representatief voorbeeldbestandje (EXCEL) vooraf zou welkom zijn...

Hierbij een voorstel mijnerzijds, werkt met INDEX en VERGELIJKEN.

PS Wat als er meerdere registratienummers (zoals PH-EUF in het voorbeeld) zijn?

 

[Niels747]

AlexCEL,

bedankt voor je antwoord.
Helaas ben ik totaal niet bekent met de functies die jij gebruikt hebt dus het lukt me niet om jouw functie na te bouwen zodat het werkt voor mij.
Bij gevoegd mijn bestandje zodat je misschien ziet wat de bedoeling is.

op tabblad 3 zie je de registraties staan.
Het is de bedoeling dat we op tabblad 1 een rapport binnen halen en dat dan automatisch de waardes van sheet1 kolom A ingevult worden in het juiste van op tabblad 3
Ik hoop dat je me verder kan helpen en ook wat uitleg wilt geven hoe de functies te bouwen.

Bekijk bijlage registratienummers (AC).xlsx

 

[Cobbe]

Zo iets?

Nu nog iemand vinden die meer als 1 resultaat kan verwerken met bv kleinste als en rijen.
Snap er de b...... van

 

[Niels747]

Bedankt Cobbe,

Inderdaad werkt het nu nog niet indien er meer velden in kolom b het zelfde zijn.
ik krijg dan alleen het eerste resultaat te zien.

indien er 2 of meer resultaten zijn voor de zelfde waarde in kolom b Mogen die best op tabblad 3 een regel lager verschijnen.
Wie kan me daarmee verder helpen zodat op iedere regel een uniek resultaat staat.

 

[AlexCEL]

Zie formule + bijlage. Werkt wel weer wat anders... maar bij meerdere gevonden resultaten worden deze nu onder elkaar geplaatst.

H5: =ALS.FOUT(INDEX(Sheet1!$A:$A;KLEINSTE(ALS(ISFOUT(VIND.SPEC(F$2;Sheet1!$B$1:$B$500));9E+99;RIJ($1:$500));RIJ()-4));"")

Is dat wat je zoekt?

PS het betreft matrix-functies dus na overtypen/kopiëren/wijzigen etc. afsluiten met Control+Shift+Enter. (meer info: http://www.h2o-betterwin.nl/know-how/tutorials/excel-matrixformules-basis/)

 

[Niels747]

Zie formule + bijlage. Werkt wel weer wat anders... maar bij meerdere gevonden resultaten worden deze nu onder elkaar geplaatst.

H5: =ALS.FOUT(INDEX(Sheet1!$A:$A;KLEINSTE(ALS(ISFOUT(VIND.SPEC(F$2;Sheet1!$B$1:$B$500));9E+99;RIJ($1:$500));RIJ()-4));"")

Is dat wat je zoekt?

PS het betreft matrix-functies dus na overtypen/kopiëren/wijzigen etc. afsluiten met Control+Shift+Enter. (meer info: http://www.h2o-betterwin.nl/know-how/tutorials/excel-matrixformules-basis/)

Dit is inderdaad wat ik zoek. Zo werkt het perfect.
Alleen kan je me nog wel uitleggen wat het nu precies doet? delen snap ik maar het meeste niet.

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000),9E+99),ROW()-ROW(H$4))),"")

dit snap ik;
Sheet1!$A:$A excel kijkt op sheet 1 naar de kolom A
F$2,Sheet1!$B$1:$B$1000 als de waarde F2 gelijk is aan de waarde in gebied B1 tot B1000

en de rest dus niet.
Zou het wel graag begrijpen zodat ik dit in de toekomst ook zelf kan bouwen.

 

[AlexCEL]

Dit soort complexere formules kun je altijd beste van "binnen naar buiten lezen". Een kleine toelichting...

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000) ,9E+99),ROW()-ROW(H$4))),"")

1. Hier wordt per rij (dat maakt het een matrixformule) gezocht (SEARCH) of het registratienummer in F2 voorkomt ergens in kolom B op sheet 1. Zo nee, levert dit een foutmelding op wat ondervangen wordt met de ISERROR.

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000) ,9E+99),ROW()-ROW(H$4))),"")

2. Als het geen (NOT) fout is wordt via de IF functie het kolomnummer genomen, anders komt er een heel groot getal te staan in de matrix. Resultaat is een matrix die bijvoorbeeld lijkt op: 9E99, 9E99, 9E99, 4, 5, 9E99, etc.

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000) ,9E+99),ROW()-ROW(H$4))),"")

3. Via de SMALL-functie wordt de kleinste waarde uit deze matrix gezocht, oftewel het eerste rijnummer waarop de registratiecode voorkomt. In dit geval dus 4.

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000) ,9E+99),ROW()-ROW(H$4))),"")

4. Via de INDEX-functie wordt nu in kolom A op sheet1 de 4e rij getoond.

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000) ,9E+99),ROW()-ROW(H$4))),"")

5. Het rode gedeelte hierboven zorgt ervoor dat je de formule kunt doortrekken, het levert een reeks 1,2,3 op etc. Dit argument gebruikt de SMALL functie om de kleinste, de op 1 na kleinste, de op 2 na kleinste etc. op te zoeken. Als de formule in een andere rij begint dan de 5e moet de verwijzing H4 aangepast worden zodat ROW()-ROW(X) weer 1 is...

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(NOT(ISERROR(SEARCH(F$2,Sheet1!$B$1:$B$1000))),ROW($B$1:$B$1000) ,9E+99),ROW()-ROW(H$4))),"")

6. Als er niets gevonden wordt, en de kleinste waarde dus 9E99 is, levert de INDEX een foutmelding op die met de eerste IFERROR wordt afgevangen. Deze zorgt er dan voor dat de regel leeg blijft.

Hopelijk verduidelijkt dit iets...