Opslag skal returnerer ALLE linier der opfylder opslagsværdien
Hej
Jeg har lidt en udfordring i excel (2007, engelsk version), som er lidt svær at forklare, men jeg har forsøgt nedenfor.
Jeg har en liste med 50 rækker og 3 kolonner.
Jeg vil gerne lave et opslag (ala VOPSLAG) i denne liste, på kolonne A, og returnere de værdier fra kolonne B og kolonne C der opfylder min opslagsværdi, til 2 kolonner i en anden fane.
MEN jeg vil gerne have returneret ALLE de linier der opfylder opslagsværdien, stående på en linie pr. resultat
Eksempel:
Opslagsværdien = "HAM.MOD"
Så skal der i den nye fane blive returneret række 1, 4, 6 og 8
A B C
1 HAM.MOD Pallemag 802
2 HAM.PLU Pallereoler 4.966
3 HAM.PLU Truck 9.490
4 HAM.MOD Ombygning kantinen 814
5 HAM.PLU Ombygning kantinen 1.018
6 HAM.MOD Ombygning 919
7 HAM.PLU Ombygning 1.149
8 HAM.MOD Etablering kantine 219
9 HAM.PLU Etablering kantine 273
Kan man på en eller anden måde løse det???
Til orientering, er der IKKE altid samme antalt linier i listen pr. opslagsværdi.
Mvh
Line
Skrevet tor. d. 29. juli 2010 kl. 13:04:23| #1
LOPSLAG() (som det må være på den måde data er opbygget) kan ikke benyttes til opgaven. LOPSLAG() finder kun første forekomst af en given værdi.
Du kan opnå det ønskede med et avanceret filter.
Data->Filter->Avanceret filter.
Skrevet tor. d. 29. juli 2010 kl. 13:10:47| #2
Jeg er godt klar over at hverken LOPSLAG eller VOPSLAG kan bruges, det var bare for at give en idé om hvad det var for en måde at lave et opslag på, som jeg mente.
Men hvordan bruger man datafiltre, når listen med alle data ligger i én fane, og jeg kun vil have de data over i den anden fane, som opfylder betingelserne (fx HAM.MOD) ??
Skrevet tor. d. 29. juli 2010 kl. 14:23:48| #3
Du kan bruge denne løsning, hvor A5 er nummeret på forekomsten (dvs. skriv 1 i A5, hvis du vil have første forekomst). Du kan så bare lave flere rækker med informationen:
For at få Kolonne B resultaterne:
INDEX(ARK1!$B$1:$B$1000;SMALL(IF(ARK1!$A$1:$A$1000=$C$3;ROW(Ark1!$B$1:$B$1000)-MIN(ROW(Ark1!$B$1:$B$1000))+1);$A5))
For at få Kolonne C resultaterne:
INDEX(ARK1!$C$1:$C$1000;SMALL(IF(ARK1!$A$1:$A$1000=$C$3;ROW(Ark1!$C$1:$C$1000)-MIN(ROW(Ark1!$C$1:$C$1000))+1);$A5))
osv..
C3= det du gerne vil søge efter i kolonne A
Du laver så en kolonne I arket hvor du vil have resultaterne, som starter i A5, hvor du skriver 1. A6 = 2, A7= 3 osv.
Håber det giver mening - virker i hvert fald for mig :)
HUSK at trykke ALT + SHIFT + ENTER - i stedet for bare ENTER! Det er en Array formel!
Skrevet tor. d. 29. juli 2010 kl. 14:26:34| #4
btw. skal det løses med VBA, hvis excel skal tænke på hvor mange rækker der er.
På denne måde skal du sikre dig at du har nok rækker med formelen :)
Skrevet tor. d. 29. juli 2010 kl. 14:30:09| #5
btw?? Hvad betyder det?
Har du mulighed for at sende mig et excel ark hvor den formel virker i, da den er rimelig svær at overskue ;-)
Skrevet tor. d. 29. juli 2010 kl. 15:49:11| #6
Engelsk version:
Kolonne B værdier:
=INDEX(Sheet1!$B$1:$B$9;SMALL(IF(Sheet1!$A$1:$A$9=Sheet2!$G$1;ROW(Sheet1!$A$1:$A$9));ROW(1:1)))
Kolonne C værdier:
=INDEX(Sheet1!$C$1:$C$9;SMALL(IF(Sheet1!$A$1:$A$9=Sheet2!$G$1;ROW(Sheet1!$A$1:$A$9));ROW(1:1)))
Opslagsværdien har jeg forudsat indtastet i Sheet2!$G$1.
Cellereferencerne skal selvsagt tilpasses det område der er relevant for dig.
Formlerne vil returnere #NUM! når der ikke findes flere udgaver af den søgte værdi. Hvis du gerne vil undgå #NUM!, kan du for eksempel bruge IFERROR funktionen.
Du kan kopiere formlerne herfra til din egen fil. Når du har formlen i en celle så tryk på F2, herefter holder du Ctrl og Shift nede før du trykker på Enter. Herved bliver formlerne omsluttet af tuborgparenteser som udtryk for, at Excel forstår dem som matriksformler. Kopier formlerne nedad så langt som nødvendigt.
Som allerede nævnt af erikjuul er det også muligt at bruge Avanceret Filter. Sig til hvis du ønsker hjælp til det.
Hans
Skrevet tor. d. 29. juli 2010 kl. 16:12:49| #7
Hej Hans
Er det ikke det samme som jeg skrev? :)
Skrevet tor. d. 29. juli 2010 kl. 17:07:55| #8
Det må du undskylde.
Jeg synes dog ikke det er helt det samme. Det du skriver om i følgende sætning:
Du laver så en kolonne I arket hvor du vil have resultaterne, som starter i A5, hvor du skriver 1. A6 = 2, A7= 3 osv
Det er ikke nødvendigt med formlerne i #6.
Men ved eftertanke medgiver jeg, at dit svar var en brugbar løsning og jeg burde måske ikke være kommet med noget indlæg.
NB
I og med spørgeren bruger engelsk version gætter jeg på, "Sheet" er mere relevant end "Ark".
Hans
Skrevet tor. d. 29. juli 2010 kl. 17:20:44| #9
Hej Hans
Du har ret... Du har forfinet den lidt - det vil jeg straks overføre i mine excel ark!
Det med sheet og ark var bare en tanketorsk fra min side - men nu er eksempel på løsning sendt og så må vi se hvad brugeren siger :)
Venligst
Nis
Skrevet fre. d. 30. juli 2010 kl. 08:28:29| #10
Godmorgen de herrer
Tak for det tilsendte zjat - det virker perfekt. Men det gør dine formler også Hans.
Jeg kan godt se at dine formler er lidt mere forfinede Hans, men zjat's løser dog fint mit problem.
DOG DOG DOG, vil mere jo altid have mere :-)
Zjat i #4, skriver du at man kan få excel til selv at finde ud af hvormange rækker der skal være, ved hjælp af en VBA kode.
Jeg kunne nemlig rigtig godt tænkte mig, at der kun var plads til præcis det antal linier der var nødvendige, da jeg efter disse linier skal have en Total linie med summering. Kan det lade sig gøre?
Hvis jeg nu gerne vil have hjælp til den VBA kode, skal jeg så oprette et nyt spørgsmål??
Skrevet fre. d. 30. juli 2010 kl. 11:10:58| #11
Du kan jo altid lave en total linie nederst og vælge at summere de data som er tilstede.
Hvis du gerne vil have VBA, så kan du bruge en simpel hide funktion (der er smartere funktioner):
Dim Criteria As Boolean
Dim i As Integer
Rows("23:182").EntireRow.Hidden = False
On Error GoTo slut
Sheets("Sheet1").Activate
i = 1
For i = 23 To 71
If (Cells(i, 9).Value = "0") And (Cells(i, 11).Value = "0") Then
Criteria = True
Rows(i).EntireRow.Hidden = True
End If
Den kigger på række 9 og 11 og tjekker om den er = 0
Hvis den er det, så skjuler den rækken.
Smid det ind under en knap og kør løs :)
Jeg går her udfra du kender lidt til VBA :)
Skrevet fre. d. 30. juli 2010 kl. 12:15:36| #12
bob bob, kendskab er nu nok så meget sagt ;-)
Jeg kan finde ud af at indspille macroer og efterfølgende rette lidt i dem. Og jeg regner med at en VBA kode er i samme stil, bare en "skrevet" process istedet for en optaget..... har jeg fat i den rigtige opfattelse?
Skal jeg bare kopiere din tekst ind i en macro direkte, og rette tallene til?
Hvad henviser 23:182 til? Er det alle rækkerne i mit skema?
Min række 77 til 89 indeholder din formel zjat, så det er de rækker der skal skjules hvis de er tomme. Men betyder 0 at de er tomme? For der er jo formler i dem, hen over flere kolonner...?
Din række 9 og 11 som du kigger på, hvorfor de to rækkenumre? Hvad med række 10? Jeg tror ikke helt jeg forstår hvad det er for nogle rækkenumre jeg skal indsætte i VBA-koden......
Skrevet fre. d. 30. juli 2010 kl. 16:33:44| #13
Du har fat i det rigtige, eksemplet er bare hvis du vil tjekke 2 kolonner for om der er noget. Du kan også nøjes med at tjekke en. Hvis felter i den kolonne er 0 så vil den skjule hele rækken :)
