funktio joka löytää saman tekstin sarakkeesta

Stuba

Hei. Pystyykö joku auttamaan seuraavanlaisessa ongelmassa? Eräässä excel-taulukossa on varastokirjanpitoa missä yhdessä sarakkeessa on tuotekoodi ja toisessa sen hyllypaikka (kirjaimia ja numeroita). Millä funktiolla voin löytää jos samassa hyllypaikassa saattuu olemaan useampaa tuotekoodia? Eli käytännössä funktio tutkisi J-sarakkeesta löytyykö sieltä kahta samanlaista tekstiä ja ilmoittaisi sen jotenkin, vaikkapa k-sarakkeessa. Kiitokset avusta.

17

1800

    Vastaukset

    Anonyymi (Kirjaudu / Rekisteröidy)
    5000
    • Kundepuu

      Kymmeniä mun postauksia aiheesta.
      Hae haejasiirrä ja etsijasiirrrä jne hakusanoilla.
      Pitäisi löytyä suoraan sulle sopiva koodikin niistä

      Keep EXCELing
      @Kunde

      • Stuba

        Olen yrittänyt haeskella sopivaa mutta vielä ei ole löytynyt. En ole kyllä kaikkia postauksia kahlannut läpi (tai ehkä en vain ole tajunnut). Ongelmana on se että yleensä näissä on hakuehto valmiina mutta mun tapauksessa hakuehtoa ei ole tiedossa. Funktion pitäisi vain katsoa löytyykö kahta samaa arvoa sarakkeesta mutta tiedossa ei ole mitä arvoa.


    • ajattelumalli

      Entä jos kääntäisi homman toisin päin? Siis että ensimmäisessä sarakkeessa olisi kaikki hyllypaikat, ja sen perässä sarakkeissa kaikki paikalla olevat tuotteet?

      • Stuba

        se ei valitettavasti onnistu muista syistä


    • Tämmöinen

      Hyllypaikat sarakkeessa I, tuotteet sarakkeessa J. Lajittele kaikki ensin tuotekoodien, sitten hyllypaikkojen mukaan. Seuraava kaava soluun K2, ja se kopioituna viimeiselle riville asti näyttää ne tuotteet, joita samassa hyllyssä on useampi kuin yksi.
      =IF(OR(AND(I2=I3;J2=J3); AND(I2=I1;J2=J1));"monta";"")

    • ORCL

      kokeile k sarakkeeseen kaavaa:

      =IF(COUNTIF(J:J;INDIRECT("J"&ROW()))>1;"Useammassa hyllyssä";"")

    • Kundepuu

      Option Explicit
      Dim Solu As Range
      Dim Solu2 As Range
      Dim cainoa As Collection
      Dim Hyllypaikat As Collection
      Dim i As Long
      Dim uList() As Variant
      Dim TäytäLista As Variant
      Dim Löydetyt As Range
      Dim Vika As Long

      Sub OnkoHyllyssäSamojaTuotekoodejaUseaita()
      On Error Resume Next
      Set cainoa = New Collection
      Vika = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
      'etsitään uniikit tuotekoodit A-sarakkeesta,muuta sopivaksi
      For Each Solu In Range("A1:A" & Vika)
      If Solu.Value <> "" Then
      cainoa.Add Solu.Value, CStr(Solu.Value)
      End If
      Next Solu
      TäytäLista = ""
      If cainoa.Count > 0 Then
      ReDim uList(1 To cainoa.Count)
      For i = 1 To cainoa.Count
      uList(i) = cainoa(i)
      Next i
      TäytäLista = Application.WorksheetFunction.Transpose(uList)
      End If
      'muuta sarakkeet sopiviksi
      'nyt C-sarake ilmoittaa tupla tuotekoodin ja D- sarake missä hyllypaikassa
      Range("C:D") = ""
      Range("C1").Resize(cainoa.Count) = TäytäLista
      Vika = Range("C" & Range("C:C").Rows.Count).End(xlUp).Row
      On Error GoTo 0
      On Error GoTo Tupla
      'haetaan hyllypaikat uniikeille tuotteille C-sarakkessa, muuta sopivaksi
      'haetaan uniikkien tuotteiden osoitteet A-sarakkeesta ja vastaava osoite muutetaan hyllyhin ( B -sarake), muuta sopivaksi
      For Each Solu In Range("C1:C" & Vika)

      Set Löydetyt = EtsiJaSiirrä(Solu, Range("A:A")).Offset(0, 1)
      Set Hyllypaikat = Nothing
      Set Hyllypaikat = New Collection
      ' tsekataan uniikit hyllypaikat ja jos tuplalisäys niin tulee virhe ja hyödynnetään sitä, eli sen on oltava tuplahylly...
      For Each Solu2 In Löydetyt
      If Solu2.Value <> "" Then
      Hyllypaikat.Add Solu2.Value, CStr(Solu2.Value)
      End If
      Next Solu2
      ' poistetaan viimeinen pilkku solusta
      Solu.Offset(0, 1) = Left(Solu.Offset(0, 1), Len(Solu.Offset(0, 1)) - 1)
      Next
      'sovitetaan solulevydet sarakkeessa
      Range("C:D").EntireColumn.AutoFit
      Exit Sub
      'tänne tullaan jos tuotteella oli sama hyllypaikka
      'lisätään tieto naapurisoluun hyllypaikasta
      'ja palautetaan koodin suoritus takaisin virhettä seuraavalle riville
      Tupla:
      Solu.Offset(0, 1) = Solu.Offset(0, 1) & Solu2 & ","
      Resume Next
      End Sub
      Function EtsiJaSiirrä(Hakuehto As Variant, HyllyAlue As Range) As Range
      Dim Solu As Range
      Dim EkaOsoite As String

      With HyllyAlue
      Set Solu = .Find( _
      What:=Hakuehto, _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False, _
      SearchFormat:=False)
      'eka hyllypaikka
      If Not Solu Is Nothing Then
      Set EtsiJaSiirrä = Solu
      EkaOsoite = Solu.Address
      'luupataan läpi loput hyllypaikat
      Do
      Set EtsiJaSiirrä = Union(EtsiJaSiirrä, Solu)
      Set Solu = .FindNext(Solu)
      Loop While Not Solu Is Nothing And Solu.Address <> EkaOsoite
      End If
      End With

      End Function

      Keep EXCELing
      @Kunde

      • Stuba

        Kiitoksia vain vastaajille. On ollut sen verran kiireitä etten ole kerennyt aiemmin kokeilemaan mutta nyt sain aikaiseksi.

        funktio =IF(COUNTIF(J:J;INDIRECT("J"&ROW()))>1;"Useammassa hyllyssä";"") ei toiminut.

        Kundepuun ansiokas makro pysähtyi riville "Solu.Offset(0, 10) = Solu.Offset(0, 10) & Solu2 & ","". En tiedä missä vika. K-sarakkeeseen alkaa ilmestymään hyllypaikkoja mutta sitten homma tyssää tuohon riviin.
        Ehkä pieni lisäselvitys on paikallaan kyseisestä taulukosta: sarakkeita on A-M. Taulukossa on kaksi osaa. Ensimmäinen alkaa riviltä 1 ja rivit 1-5 sisältää otsikkotietoja. Riviltä 6 alkaa tavarakoodit sekä paikkatiedot. Ensimmäinen osa päättyy riville 23 jonka jälkeen on tyhjä rivi, sitten kaksi otsikkoriviä ja toinen osa alkaa riviltä 26 jossa tavarakoodit ja paikkanumerot jatkuvat. Molemmissa osissa on B-sarakkeessa tavarakoodi ja J-sarakkeessa paikkakoodi (mallia kirjain.numero[joskus myös -]). Toiset sarakkeet sisältävät muita makroja ja niissä on hintatietoja ym. K-sarake on tyhjä.
        Käytän suomenkielistä excel-versiota.
        Aika monimutkaiseksi menee ja pystyn kyllä elämään vaikka tätä ei saada toimimaan. Mutta jos jollakulla riittää mielenkiintoa painiskella tämän kanssa niin siitä vaan.


    • Kundepuu

      Option Explicit
      Dim Solu As Range
      Dim Solu2 As Range
      Dim cainoa As Collection
      Dim Hyllypaikat As Collection
      Dim i As Long
      Dim uList() As Variant
      Dim TäytäLista As Variant
      Dim Löydetyt As Range
      Dim Vika As Long

      Sub OnkoHyllyssäSamojaTuotekoodejaUseita()
      On Error Resume Next
      Set cainoa = New Collection
      Vika = Range("B" & Range("B:B").Rows.Count).End(xlUp).Row
      'etsitään uniikit tuotekoodit B-sarakkeesta,muuta sopivaksi

      'Ensimmäinen osa päättyy riville 23 jonka jälkeen on tyhjä rivi, sitten kaksi otsikkoriviä
      'ja toinen osa alkaa riviltä 26 jossa tavarakoodit ja paikkanumerot jatkuvat.
      'ton mukaan kylläkin rivi 27 ...
      For Each Solu In Union(Range("B6:B23"), Range("B27:B" & Vika))
      If Solu.Value <> "" Then
      cainoa.Add Solu.Value, CStr(Solu.Value)
      End If
      Next Solu
      TäytäLista = ""
      If cainoa.Count > 0 Then
      ReDim uList(1 To cainoa.Count)
      For i = 1 To cainoa.Count
      uList(i) = cainoa(i)
      Next i
      TäytäLista = Application.WorksheetFunction.Transpose(uList)
      End If
      'muuta sarakkeet sopiviksi
      'nyt K-sarake ilmoittaa tupla tuotekoodin ja L- sarake missä hyllypaikassa
      Range("K:L") = ""
      Range("K1").Resize(cainoa.Count) = TäytäLista
      Vika = Range("K" & Range("K:K").Rows.Count).End(xlUp).Row
      On Error GoTo 0
      On Error GoTo Tupla
      'haetaan hyllypaikat tuplille tuotteille K-sarakkessa, muuta sopivaksi
      For Each Solu In Range("K1:K" & Vika)
      Set Löydetyt = EtsiJaSiirrä(Solu, Range("B:B")).Offset(0, 8)
      Set Hyllypaikat = Nothing
      Set Hyllypaikat = New Collection
      ' tsekataan uniikit hyllypaikat ja jos tuplalisäys niin tulee virhe ja hyödynnetään sitä, eli sen on oltava tuplahylly...
      For Each Solu2 In Löydetyt
      If Solu2.Value <> "" Then
      Hyllypaikat.Add Solu2.Value, CStr(Solu2.Value)
      End If
      Next Solu2
      ' poistetaan viimeinen pilkku solusta
      If Not Solu.Offset(0, 1) = "" Then
      Solu.Offset(0, 1) = Left(Solu.Offset(0, 1), Len(Solu.Offset(0, 1)) - 1)
      Else
      Solu = ""
      End If
      Next
      Range("K1").Resize(Cells(Rows.Count, "K").End(xlUp).Row, 2).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
      'sovitetaan solulevydet sarakkeessa
      Range("C:D").EntireColumn.AutoFit
      Exit Sub
      'tänne tullaan jos tuotteella oli sama hyllypaikka
      'lisätään tieto naapurisoluun hyllypaikasta
      'ja palautetaan koodin suoritus takaisin virhettä seuraavalle riville
      Tupla:
      Solu.Offset(0, 1) = Solu.Offset(0, 1) & Solu2 & ","
      Resume Next
      End Sub
      Function EtsiJaSiirrä(Hakuehto As Variant, HyllyAlue As Range) As Range
      Dim Solu As Range
      Dim EkaOsoite As String

      With HyllyAlue
      Set Solu = .Find( _
      What:=Hakuehto, _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False, _
      SearchFormat:=False)
      'eka hyllypaikka
      If Not Solu Is Nothing Then
      Set EtsiJaSiirrä = Solu
      EkaOsoite = Solu.Address
      'luupataan läpi loput hyllypaikat
      Do
      Set EtsiJaSiirrä = Union(EtsiJaSiirrä, Solu)
      Set Solu = .FindNext(Solu)
      Loop While Not Solu Is Nothing And Solu.Address <> EkaOsoite
      End If
      End With

      End Function


      Keep EXCELing
      @Kunde

      • Stuba

        Tämäkin tyssää samalle riville "Solu.Offset(0, 1) = Solu.Offset(0, 1) & Solu2 & "," ". En tiedä vaikuttaako asiaan se että ensimmäisessä osassa on tyhjiä rivejä. rivit 11-24 ovat tyhjiä.


    • toimintaperiaate

      Onko tässä kyseessä vähän kuten VLOOKUP, mutta että se hakisi yhden sijaan kaikki osumat?

      • Stuba

        En oikein ole kartalla tästä VLOOKUPista mutta ideana oisi se että yhdestä sarakkeesta (J) tutkitaan onko siinä kahta tai useampaa samanlaista hyllytietoa (ovat mallia P.48 tai P.24-1) ja että jos löytyy niin sen saisi jotenkin näkymään seuraavassa sarakkeessa. Hyllytiedot pitäisi tutkia koko taulukosta; tässä on kaksi osaa joiden välissä tyhjiä rivejä ja otsikkotietoja. En ole itse tehnyt tätä taulukkoa ja siihen on haettu tietoja yhdestä sun toisesta lähteestä ja sisältää muitakin makroja. Itse asiassa nyt vasta huomasin että taulukossa on ensimmäisellä rivillä täppä josta saa suodattaa tietoja. Toisen osan alussa ei ole mitään täppää mistä voisi lajitella tietoja.Eli jonkinmoinen taulukko on varmaan kyseessä mutta on outoa että tässä on tyhjiä rivejä ja on tosiaan kaksiosainen.


      • toimintaperiaate
        Stuba kirjoitti:

        En oikein ole kartalla tästä VLOOKUPista mutta ideana oisi se että yhdestä sarakkeesta (J) tutkitaan onko siinä kahta tai useampaa samanlaista hyllytietoa (ovat mallia P.48 tai P.24-1) ja että jos löytyy niin sen saisi jotenkin näkymään seuraavassa sarakkeessa. Hyllytiedot pitäisi tutkia koko taulukosta; tässä on kaksi osaa joiden välissä tyhjiä rivejä ja otsikkotietoja. En ole itse tehnyt tätä taulukkoa ja siihen on haettu tietoja yhdestä sun toisesta lähteestä ja sisältää muitakin makroja. Itse asiassa nyt vasta huomasin että taulukossa on ensimmäisellä rivillä täppä josta saa suodattaa tietoja. Toisen osan alussa ei ole mitään täppää mistä voisi lajitella tietoja.Eli jonkinmoinen taulukko on varmaan kyseessä mutta on outoa että tässä on tyhjiä rivejä ja on tosiaan kaksiosainen.

        Minä olen joskus tehnyt sellaisen funktion, joka käy VLOOKUP-funktion tavoin taulukkoa läpi ja palauttaa kaikki ehdot täyttäneet arvot pilkulla erotettuna funktion sisältävään soluun.

        Jos taulukon A- ja B- sarakkeissa olisi ao. arvot, niin omafunktio("a") palauttaisi 1, 5, 7. Mahdatkohan tarkoittaa jotain tuon tyylistä?

        A B
        ----
        a 1
        b 2
        c 3
        d 4
        a 5
        b 6
        a 7


      • Stuba

        vähän tuon suuntaista. Tässä on se ongelma että en tiedä mitä tietoa haetaan. Pitäisi löytää tieto että onko sama hyllypaikka kaksi tai useammin sarakkeessa eikä ole tietoa mikä hyllypaikka se on.


      • toimintaperiaate
        Stuba kirjoitti:

        vähän tuon suuntaista. Tässä on se ongelma että en tiedä mitä tietoa haetaan. Pitäisi löytää tieto että onko sama hyllypaikka kaksi tai useammin sarakkeessa eikä ole tietoa mikä hyllypaikka se on.

        Ok, nyt ehkä ymmärsin. Oletko kokeillut hakea "find duplicates excel"? Auttaisikohan sieltä löytyvät linkit yhtään. Esimerkiksi ehdollisella muotoilulla voi korostaa useammin kuin kerran näkyvät solut.

        https://www.excel-easy.com/examples/find-duplicates.html

        Ohje jolla ne saisi toiseen sarakkeeseen:

        https://www.extendoffice.com/documents/excel/2655-excel-extract-duplicates-from-a-column.html


      • Stuba

        Tuo kaksoiskappaleiden haku samassa sarakkeessa on kaikessa yksinkertaisuudessa toimiva ratkaisu. Toimii kuin junan vessa. Kiitos vinkistä!


    • Kundepuu

      Unohda noi mun kooddit. Koodini tarkoitus oli etsäi tuotteita, jotka esiintyvät useammassa hyllyssä...
      Eihän tossa ttartte kikkailla mitään sitten. Uniikit vaan ja paikka mihin siirtää

    Ketjusta on poistettu 0 sääntöjenvastaista viestiä.

    Luetuimmat keskustelut

    1. Niin surullinen

      onnettomuus taas. Voimia ihan kaikille läheisille,kavereille ja kouluun 🙏
      Lapua
      196
      4832
    2. MIksi suurin osa persuista on alkoholisteja?

      Tätä jäin pohdiskelemaan....
      Maailman menoa
      37
      3352
    3. 33
      2867
    4. Kehutaan vaihteeksi Perussuomalaisia

      Perussuomalaiset ovat olleet melkoisen lokakampanjoinnin kohteena, vaikka ovat saaneet paljon hyvää aikaiseksi. Nyt on
      Maailman menoa
      141
      2758
    5. Paloautoko se oli kolarissa Juntusrannan risteyksessä?

      Oli kuva paloautosta nettijutussa.
      Suomussalmi
      25
      2408
    6. SDP ylivoimainen ykkönen

      En ole koskaan viitsinyt käydä äänestämässä, mutta nyt SDP:n etumatka on niin kutkuttava, että pakkohan se on vaivautua.
      Maailman menoa
      68
      2257
    7. Tiesitkö? Johannes Brotheruksen ex-isäpuoli on kuin onkin Mikko Kuustonen - Tästä on kyse!

      Tiesitkö? Ja hehän on kuin kaksi marjaa... Johannes Brotherus on KUUMAA-yhtyeen jäsen ja tänä syksynä mukana Vain elämää
      Suomalaiset julkkikset
      19
      2088
    8. Mitä tapahtuu?

      Mitä säpäkän risteyksessä on tapahtunut kun poliiseja, ambulansseja ja kopteri paikalla?
      Outokumpu
      31
      1790
    9. Roiskeläpät takaisin niin alkaa lasit kestämään

      "Tuulilaseja hajottava talvi-ilmiö on ehkä ratkennut" Tämän päivän autoissa kun on esimerkiksi vanhempaa autokalustoa s
      Yleistä autoilusta
      41
      1742
    10. Polttomoottoriauto on köyhän merkki

      Kun ei ole varaa ostaa sähköautoa, niin joutuu köyhän autoa käyttämään.
      Maailman menoa
      80
      1404
    Aihe