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

1807

    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. Kylläpä on nautinnollista taas tämä palstan vassari valitus!

      Lähes jokainen avaus on vassareiden kitinää ja valitusta. Eikö se tarkoitakin, että silloin asiat menee maassamme parem
      Maailman menoa
      101
      3258
    2. Palkansaajilta kupattiin 27,5 mrd euroa työeläkkeisiin

      Jo pelkän himmelin toimintakulut olivat 400 miljoonaa euroa, jolla olisi mukavasti tuottanut myös sote-palveluja hyvinvo
      Maailman menoa
      8
      2647
    3. HS: persujen v. 2015 turvapaikanhakijoista alle puolet töissä

      Aikuisina Suomeen tulleista ja myönteisen päätöksen saaneista vain 42 prosenttia oli vuonna 2023 töissä, vaikka he ovat
      Maailman menoa
      75
      2536
    4. Persut muuten hyväksyvät 2 + 8 mrd. euron maatalous- ja yritystuet

      Vaikka molemmat tukimuodot tiedetään haitallisiksi, koska ovat käytännössä pelkkää säilyttävää tukea, eivätkä kannusta k
      Maailman menoa
      73
      1488
    5. Mikä kaivatussasi herätti mielenkiintosi

      Kun tapasitte ensi kerran? Ulkonäössä? Luonteessa tai olemuksessa? Kuinka nopeasti mielenkiinto muuttui ihastukseksi?
      Ikävä
      93
      1486
    6. Valkoinen Golf

      Kukahan on tämä ukko, joka työkseen kyylää pienen ässän asiakkaita viikon jokaisena päivänä.
      Kuhmo
      16
      1065
    7. Kaikki ovat syntisiä!!!

      Näin täällä koko ajan vakuutellaan uskovaisten toimesta. Myös Päivi Räsänen on toistanut tätä samaa matraa jatkuvasti. N
      Luterilaisuus
      335
      975
    8. Martina Aitolehti podcastissa: Ero

      Martina Aitolehti podcastissa: Ero Martina Aitolehti kertoi BFF-podcastin https://www.iltalehti.fi/viihdeuutiset/a/696
      Kotimaiset julkkisjuorut
      184
      975
    9. Moottorisahalla kauppaan

      Missäs päin kaupunkia tämä yöllä moottorisahalla kauppaan yrittänyt asiakas askaroi? https://poliisi.fi/-/mies-yritti-s
      Kajaani
      11
      880
    10. Jos olisit kaivattusi

      Kanssa kahdestaan samassa tilassa niin miten kävisi
      Ikävä
      50
      834
    Aihe