Help, heeft te maken met als functie denk ik

[perrieh]

Beste iedereen,

Ik zit een beetje in de knoop met Excel. Ik heb in kolom A van sheet 1 tekst staan. In kolom A van sheet 2 staat een hele lijst van tekst.
Nou is de bedoeling dat als de tekst van kolom A sheet 2 voorkomt in Kolom A van sheet 1 dat dan de gegevens van kolom D van sheet 1 zichtbaar worden (die waarden moet ik uiteindelijk optellen). Zie bijlage.
Alvast bedankt voor jullie hulp.

 

[VenA]

Met een hulpkolom om het 'wegnummer' te bepalen.

 

[perrieh]

VenA, volgens mij klopt het niet helemaal, want sommige worden niet meegenomen. In het bestand hierboven zie je de daadwerkelijke wegen. Ik wil graag kijken hoeveel kilometer er op die wegen is gereden.

 

[VenA]

Bekijk eerste eens wat de functie LINKS() doet en hoe jouw gegevens in elkaar steken.

 

[perrieh]

VenA, ja ik weet wat het inhoud. Ik ben alleen bang dat hij bijvoorbeeld bij E19 ook andere wegen meepakt zoals A59, maar die hoort daar niet bij. In dit bestand zie je de wegen waar het werkelijk omdraait.

Uiteindelijk wil ik dus het aantal gereden kilometers van de wegen in sheet 2. In sheet 1 staan alle wegen waar gereden zijn.
iemand die me verder kan helpen?

 

[perrieh]

Misschien dat het zo duidelijker is:


Ik wil dus de kilometers weten van de wegen in sheet 2. In sheet 1 staan alle wegen waaruit de gegevens gehaald moeten worden. Iemand enig idee hoe ik dat moet doen?

Bij voorbaat dank

 

[VenA]

Als het laatste bestand het juiste is dan doet deze het bijna.

=SUMPRODUCT((LEFT(Blad1!$A$2:$A$5184;LEN(TRIM(Blad2!A2)))=IF(LEN(TRIM(A2))>4;SUBSTITUTE(SUBSTITUTE(TRIM(MID(A2;FIND("(";A2;1);LEN(A2)-FIND("(";A2;1)));"(";"");")";"")&" ("&LEFT(A2;FIND(" ";A2;1)-1)&")";A2))*(Blad1!$B$2:$B$5184))

Als in het doorzoeken gedeelte 'E19 (A12)' staat en je wil zoeken op 'A12 (E19)' ga je dat nooit vinden. Dmv de formule wordt jouw zoektekst omgezet naar dat wat ook in het te doorzoeken bereik kan staan. Als je alle Km's van de A12 wil hebben moet je deze opnemen in jouw blad2.
Dus A12, A12 (E19) etc anders werkt het niet.

Je hebt dan overal aan de linkerkant A12 staan en dat kan je dan in tweede tabel weer groeperen. Je kan ook beter al die nutteloze spaties in blad2 weghalen. Dit maakt de formule alleen maar langer.

 

[HSV]

Of:
Eng.

=SUMPRODUCT((LEFT(Blad1!$A$2:$A$5184,LEN(TRIM(A2)))=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(A2,LEN(A2)-FIND(" ",A2))&" "&LEFT(A2,FIND(" ",A2)-1)&")"),"(",""),") "," ("),A2))*(Blad1!$B$2:$B$5184))

Ned.

=SOMPRODUCT((LINKS(Blad1!$A$2:$A$5184;LENGTE(SPATIES.WISSEN(A2)))=ALS.FOUT(SUBSTITUEREN(SUBSTITUEREN(SPATIES.WISSEN(RECHTS(A2;LENGTE(A2)-VIND.ALLES(" ";A2))&" "&LINKS(A2;VIND.ALLES(" ";A2)-1)&")");"(";"");") ";" (");A2))*(Blad1!$B$2:$B$5184))

 

[VenA]

@HSV,
Altijd lol met dit soort bestanden. Deelprobleem1, deelprobleem2, etc en dat weer samenvoegen in één formule. En dan de formule weer vereenvoudigen. Het lijkt wel teamwork:d

 

[HSV]

Altijd leuk, maar je zal verbaast staan te kijken dat de criteria per ongeluk verkeerd is neergezet door @perrieh.

 

[perrieh]

@HSV @VenA,

Hij pakt nu nog wel bijvoorbeeld bij de N7 dan pakt hij ook de N70 mee? En bij de N2 pakt hij ook N213 etc. Is hier nog een oplossing voor?
En de R wegen worden ook niet meegenomen, terwijl die wel in de lijst staan. De A en de E wegen kloppen wel, bedankt daarvoor

Is hier nog een oplossing voor?

 

[perrieh]

@HSV en @VenA

Ook zie ik nu dat A19 niet is meegenomen. Dat vind ik raar want A12 is wel meegenomen en je zou toch zeggen dat dan de formule klopt.

 

[VenA]

Je kan hier eens mee beginnen. Het staat er niet voor niets.

Je kan ook beter al die nutteloze spaties in blad2 weghalen

Of dit opnemen in de formule.

 

[perrieh]

@VenA,

Zojuist gedaan. Alleen de wegen N1 t/m de N9 pakt hij nog niet correct. Hij somt nog steeds bij bijvoorbeeld N2 ook de N213 en de N09 etc op

 

[VenA]

Het meest eenvoudig is om deze ook mee te nemen in het overzicht. Van N1 trek je dan het totaal van N1? af.

 

[perrieh]

@VenA,

Hoe bedoel je? Ben niet echt een ster in excel

De rest werkt nu wel nu ik die spaties verwijderd heb, maar bij N1 t/m N9 niet. Eerst dacht ik dat ik daar juist dan wel een spatie moest hebben anders gaat die doortellen dus achter N1 een spatie en zo ook bij N2 t/m N9, maar dit werkt ook niet

 

[perrieh]

@VenA

Ik heb het zelf weer geprobeerd, maar het klopt weer niet. Zo zie ik bijvoorbeeld bij de A19 dat er 81,55 km op is gereden, maar met die formule komt er maar 19,39 te staan. (zie bijlage)
(heb nu een wat groter bestand gebruikt)

Hulp is welkom.

 

[VenA]

Moet je wel het bereik aanpassen.

 

[perrieh]

@VenA,

Met de formule van @HSV lukt het nu wel. Bedankt voor jullie hulp!:thumb: