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.
funktio joka löytää saman tekstin sarakkeesta
Stuba
17
1591
Vastaukset
- 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
Järkyttävä tieto Purrasta
Purra tapasi nykyisen miehensä täällä. Suomi24:ssä! Tulipa likainen olo. Nyt loppuu tämä roikkuminen tällä palstalla.2284917Näin asia on
Tiedän ettei hän koskaan aio lähestyä minua eikä niin ole koskaan aikonutkaan, eikä lähesty ja enkä minä enää tee sitä k233600Mikseivät toimittajat vaadi Orpoa vastuuseen lupauksistaan
Missä ne 100.000 uutta työpaikkaa muka ovat? Eivät yhtään missään. Näin sitä Suomessa voi puhua ja luvata mitä sattuu. E2712075Taas varoitusta lumesta ja jäästä
Ai kauhea! Vakava säävaroitus Lumi-/jäävaroitus Varsinais-Suomi, Satakunta, Uusimaa, Kanta-Häme, Päijät-Häme, Pirkanmaa,122013Aavistan tai oikeastaan
tiedän, että olet hulluna minuun. Mutta ilman kommunikointia, tällaisenaan tilanne ja kaikki draama ovat mun näkökulmast411299Mistä erotat onko joku kiinnostunut vai muuten mukava?
Voi sekaantua yleiseen ystävällisyyteen vai voiko?1611229Poliisi tahtoo pääsyn 4 miljoonan suomalaisen sormenjälkiin.
https://www.is.fi/digitoday/art-2000011009633.html Tämä sormenjälkiin poliisin pääsy on erittäin tärkeä rikollisten kiin1111025- 32833
- 61777
Örebro kuolleet lisääntyy.
Nyt n, 10. Mitähän vielä. Haavoittuneet?. Kuka on ampuja, salaisuus.98776