07. december 2018 - 09:36Der er
34 kommentarer og 3 løsninger
funktion hvis tekst streng indeholder
Har i kolonne A nogle betegnelser der er ca 4000 linier 1./IDBM1a1234 3./ADPM5b1458 2./ADBM2c4589 4./HDCM1a4781 2./IDPM3a1222 7./ADDE1a1444 3./ADENT3a4555
Jeg vil gerne i kolonne B have Hvis A indeholder BM , så skal den skrive BM osv Der skal søges på BM, UM, CM,DE,ENT, SD,KD;VF,RD Nogle der kan hjælpe mig med denne formel
Og hvis ikke hvis ikke der kommer et resultat skal der stå bulp
Jeg tænker, at dette er bedst gjort med en makro/VBA-kode, hvor du i en tabel tilføjer alle de forskellige koder/søgetekster, som så loop'es igennem linje for linje. Ellers kommer du ud i en lang og mindre overskuelig formel med mange indlejrede HVIS-formler, som er besværlig at vedligeholde, når/hvis der kommer nye koder/søgetekster.
Du kunne med støttekolonner slippe lidt for det. Hvis de bare skal markeres så lav en kolonne for hver søgestreng. Så kan du lave en sumformel yderst og sætte betinget formattering på tal over nul. Eller blot søge/filtrere på der hvor der er noget over nul.
VBA vil kunne f.eks. kopiere dem over til et sted, eller blot igen markere/skrive i kolonne B, men det virker som lidt meget for "kun" 4000 linjer, medmindre der er andre som skal bruge udvælgelsen.
#2 kim1a - Jeg har forsøgt med en SUMPRODUKT-formel, men jeg kan kun få den til at "konstatere", om en eller flere af søgeteksterne indgår - men ikke hvilken tekst, det præcis er. Har du et bud på sådan en type løsning?
#7 xl-Ent Det er lækkert det der som jeg ser det. Kan du forklare så vi andre forstår? Blandt andet hvorfor "--isnumber" hvis jeg fjerner de to minus så virker den ikke.
Mit forslag var mere lavpraktisk. Lav en formel som angiver et tal hvis det står i teksten, så skær x karakterer derfra, men det kræver jo en kolonne til hver. Du har sat dem alle i en.
Hvis du har filen åbnet så prøv for eksempel at vælge cell B5. Marker præcist følgende del af formlen: ISNUMBER(SEARCH(Crit;A5)) og tryk på F9. Du vil se: {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, som er TRUE i 3 position, Nu har jeg lavet en MATCH funktion hvor første argument er 1, så jeg har brug for at konvertere FALSE/TRUE til 0/1. Det gør jeg ved at sætte dobbelt minustegn foran ISNUMBER. Med celle B5 stadigvæk valgt, marker præcist følgende del af formlen: --ISNUMBER(SEARCH(Crit;A5)) (altså inklusive de to minustegn) og tryk på F9. Du vil nu se: {0;0;1;0;0;0;0;0;0}. Så vi matcher altså 1 (første argument i MATCH funktionen med første udgave af 1 i matricen {0;0;1;0;0;0;0;0;0} og resultatet er tre, hvilket ses ved at marekere præcist MATCH(1;--ISNUMBER(SEARCH(Crit;A5));0) og trykke på F9. Jeg går ud fra du herefter selv kan analysere resten af formlen. Hvis ikke, så kom igen. (Crit er navnet for område F3:F11).
Nej, den finder kun første udgave af et kriterie. Det er muligt den skal kunne det du skriver, men det synes jeg ikke fremgik klart af spørgsmålet. Jeg vil ikke sige det er umuligt formel-mæssigt, men jeg har ikke umiddelbart et forslag.
Man kan ikke bare tilføje ad i kriteriet da det ikke altid er det der står foran. Det står heller ikke altid samme sted.
Vil helst ikke bruge makro da det er meget vital fil. Den bruges hver dag mange timer af 3 brugere ud over mig. Tror slet ikke de pt kan rumme hvis jeg siger nu skal i gemme med makro
Finb fatter ikke havd du skriver. Hvad står alle dine ,,,, for?:0)
Jeg tror vi snakker forbi hinanden. I #12 skrev jeg: Nej, den finder kun første udgave af et kriterie. I #13 henviste du til #12 og skrev: Måske er det så simpelt som at bytte rundt på rækkefølgen af "DE" og "ENT" i listen, således den først søger efter "ENT"....
Men uanset hvad du gør, hvis teksten indeholder først ENT og senere AD (og AD er et af kriterierne) så vil formlen kun returnere ENT og det var hvad jeg mente med "kun første udgave af et kriterie". Så hvad er detrr, der holder fint? Og hvordan ville du i øvrigt løse den af dig foreslåede ombytning?
I #11 skriver jeg: Det lader ikke til, at den fanger koden "ENT" i sidste række....??
Hertil svarer du, at den kun finder første kriterie. Min pointe var, at den skulle finde "ENT" i stedet for "DE" sådan som i dit udmærkede løsningsforslag i Dropbox (#7).
3./ADENT3a4555 versus 3./ADENT3a4555
I din løsning fandt den "DE" i rækken med "ENT", fordi "DE" står oven over/før "ENT" i det navngivne område Crit. Bytter man rundt på de to, således "ENT" står først, finder den enten "DE" eller "ENT" korrekt i de forskellige rækker.
Jeg har ikke på noget tidspunkt talt om "AD" eller at finde flere kriterier i samme række. "AD" er jo slet ikke nævnt som et kriterie i selve indlægget/spørgsmålet fra lail. Det, der holder, er, at Crit-listen skal sorteres alfabetisk faldende for, at hvert kriterie findes korrekt i relevant række. Sagen var blot, at "ADENT3a4555" indeholder "DE", men D'et hører til "AD", og E'et hører til "ENT", som er kriteriet. Derfor skal der søges efter "ENT" før "DE".
En brugerdefineret function kræver kun tilladelse til at køre macro og gemme med macro ikke noget med man skal køre nogen da den fungere som de indbyggede.
denne fungerer med en hjælpekolonne med kriterierne i
Dim sTemp As String, rKriterie As Range, rCell As Range
Function Test(TestCelle As String, Kriterie As Range) sTemp = TestCelle Set rKriterie = Kriterie For Each rCell In rKriterie If InStr(1, sTemp, rCell.Value) Then Test = rCell.Value Next If Test = "" Then Test = "bulp" End Function
Tekststrengen var: 3./ADENT3a4555 Jeg skrev i #12 Nej, den finder kun første udgave af et kriterie
Som jeg ser det - første kriterie den støder på her er DE og det er hvad formlen returnerer. ENT er næste kriterie som den ikke returnerer. Min bemærkning om AD var fordi jeg troede du mente at tekststrengen skulle ombyttes til: 3./ENTAD3a4556. Det var en fejl fra min side.
I #23 skriver du: Sagen var blot, at "ADENT3a4555" indeholder "DE", men D'et hører til "AD", og E'et hører til "ENT"
Hvorfor det? Der har så vidt jeg kan se ikke været nævnt noget om, at et kriterium altid skal være minimum to bogstaver. Det er mulig, du har ret, men det synes jeg ikke man kan udlede det af det oprindelige spørgsmål.
Undskyld til alle andre der ikke interesserer sig for denne diskussion.
Ud fra det begrænsede datasæt og listen af bogstavkombinationer, der skulle søges efter, har jeg antaget, at de to første bogstaver i data (efter skråstregen) var et præfiks, der ikke skulle søges på. "ID", "AD", "HD" - ingen af disse tre kombinationer fremgår af søgelisten.
1./IDBM1a1234 - her skal den finde "BM", som står på listen 3./ADPM5b1458 - her skal der stå "bulp", fordi "PM" ikke står på listen 2./ADBM2c4589 - her skal den finde "BM", som står på listen 4./HDCM1a4781 - her skal den finde "CM", som står på listen 2./IDPM3a1222 - her skal der stå "bulp", fordi "PM" ikke står på listen 7./ADDE1a1444 - her skal den finde "DE", som står på listen 3./ADENT3a4555 - her skal den finde "ENT", som står på listen
Søgelisten: BM, UM, CM,DE,ENT, SD,KD;VF,RD
Her passer det så med, at søgekriterierne står umiddelbart efter præfikset på to bogstaver men før det første tal i tekststrengen. Det er det mønster, jeg ser, men jeg kan jo tage fejl. Og det er derfor, jeg konkluderer, at D'et hører til "AD" i rækken med "ENT". Søgekriteriernes længde fremgår af listen, dvs. 2 eller 3 karakterer.
Men du kan da have ret i, at jeg har gjort mig antagelser på baggrund af hvilke, jeg udleder et mønster i dataene, som muligvis er forkert. Det ændrer dog ikke på, at dit første løsningsforslag i Dropbox ikke fandt kriteriet "ENT" i den sidste række, hvilket - som hele min pointe var i #13 - kunne løses ved at bytte rundt på "DE" og "ENT" i Crit-listen (og her fik jeg måske ikke gjort det klart nok, at det var Crit-listen, jeg mente).
Jeg vil gerne lige understrege, at det er din løsning med matrix-formlen, som jeg anser for den rigtige løsning til dette indlæg, da den netop ikke anvender VBA, sådan som lail ønsker det. Jeg har på intet tidspunkt haft til hensigt at anfægte din løsning men blot at henlede opmærksomheden på, at ét af søgekriterierne ikke blev opfanget.
Du skriver: Ud fra det begrænsede datasæt og listen af bogstavkombinationer, der skulle søges efter, har jeg antaget, at de to første bogstaver i data (efter skråstregen) var et præfiks, der ikke skulle søges på.
Jeg har så antaget, at spørgeren faktisk mente det han skrev, at: Hvis A indeholder BM , så skal den skrive BM osv Der skal søges på BM, UM, CM,DE,ENT, SD,KD;VF,RD
#28 Så du mener ikke, at det er "ENT", som skal findes i den sidste række? Vil du så ikke forklare, hvorfor "DE" skulle være det korrekte i den række? Bare fordi jeg har antaget et præfiks, betyder det da ikke, at jeg ikke har tænkt søgningen, som spørgeren har skrevet. Jeg har blot forsøgt at finde et mønster i dataene for se, hvor søgningen burde finde koderne. Ser du slet ikke dette mønster i dataene?
Her er filen så med den sidste krølle (vt, sam). Se C16:C24.
I filen har jeg været nødt til at indsætte ekstra rækker for at kunne udvide Crit-listen. Det har bevirket, at jeg har været nødt til at rette en masse cellereferencer i forklaringen. Håber jeg har fået det rettet korrekt.
Jeg ved ikke om du har fulgt med i den diskussion jeg havde med Max_P_Larsen. Hvis opgaven skal forstås sådan som han gør det, så skal der ændres noget.
Der var stadigvæk enkelte forkerte (og forvirrende) cellereferencer, så her kommer lige en ny fil. Se venligst bort fra de tidligere og brug denne her.
xl-Enthusiast tak for din forklaring. Hvor er det fantastisk at du gider - og du får det skrevet, så selv jeg forstår. Du gør det nok, fordi du er et godt menneske der gerne vil lære fra dig. Du kommer helt sikkert med i min aftenbøn. TAK
jeg måtte også bytte om på rækkefølgen i crit - men så fungerer det også 100% Hvornår ved man at man skal bruge{} for bedre at kunne følge alle paranteser byggede jge formlen langsom op i brudstykker Jeg kunne se at det var efter sammenlign at jeg fik brug for ctrl + shift+enter
Hvornår ved man at man skal bruge{} for bedre at kunne følge alle paranteser byggede jge formlen langsom op i brudstykker Jeg kunne se at det var efter sammenlign at jeg fik brug for ctrl + shift+enter
Det kan jeg ikke svare på med få ord. Matrixformler er et kæmpeområde. Jeg kan kun anbefale at du selv studerer og praktiserer emnet, hvis du er interesseret i det.
Inden du eventuelt begynder at studere alt for meget om matrixformler skal jeg for god ordens skyld nævne, at hvis du har Office 365, så vil du inden for relativt kort tid modtage en version af Excel der indeholder det helt nye begreb (i Excel) der hedder Dynamic Arrays. Herefter er det ikke længere nødvendigt at bruge indtastningsmåden: hold Ctrl og Shift nede før tryk på Enter, man kan nøjes med bare at trykke Enter. Jeg har selv modtaget sådan udgave af Excel ved at have tilmeldt mig noget der hedder "Office Insider program". Der er meget spændende nyt på vej i Excel!
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
Ny bruger
Nybegynder
Opret
Preview
