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.
Vastaukset 17
- 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
Riikka Purra on mukana Suomen huutokauppakeisari -sarjassa
Suomen huutokauppakeisari on tavallisten suomalaisten ohjelma. Sarjan suosiosta kertoo se, että nyt alkaa jo 19. kausi t1251284Kuuleppas
ensimmäiset naissuhteet toimii miehelle eräänlaisena oppikouluna, mutta hän ei välttämättä itse ymmärrä oppineensa mitää250960Paljonko on etäisyytesi kiinnostavaan naiseen km?
Kaipailen tärkeää miestä ja pohdin hänen ajatuksiaan... ❤️89959On meillä harvinaisen törkeä valtuuston puheenjohtaja
Ja tyhmä! Kannattaisi googlettaa "julkisuuslaki" ja nojata siihen vasta sitten. Samalla voisi googlettaa muiden kuntien75777- 54754
Olet mieleni päällä edelleen, J
En voi sille mitään. Ja olen yrittänyt voida, monen monta kertaa. Miten jäinkin näin kiinni sinuun... 😞34672- 66667
- 46640
Mikko Leppilampi loi TTK:ssa ankaran tavan - Jatkaako Veronica Verho haastavaa perinnettä?
Suorien lähetysten juontaminen vaatii rautaista ammattitaitoa ja kovaa työtä. Tanssii Tähtien Kanssa -ohjelmassa on näh30577Oletko miettinyt
Mikä minusta tekee sinulle niin erityisen? Onko se samanlaiset lapsuusmaisemat? Työpaikan liian pitkät katseet? Selvittä30575Provider miehistä..
Tietyt miehet puhuvat ilkeästi naisista, jotka haluavat elää miehen tuloilla. Eikö? Mutta miksi tietyt miehet siitä pah183557Mulla ei ole Suomessa enää mitään
4 vuotta joutuu asumaan henkisessä vankilassa. Sitten voi muuttaa takaisin ulkomaille.157557- 35534
Miksi älyllinen ponnistelu ei ole kaikille ihanaa?
Auttakaa minua, en ymmärrä. 🥵 Miten on mahdollista, että joku ei pidä pähkäilystä ja älyllisestä ponnistelusta? Minus97509- 200488
Miten tehdään Todellavaikeeta hulluksi?
Tässä on nainen, jolla on korkea vaatimustaso! Todellavaikeeta voisi lukea tämän ja kiehua kateudessaan. 🤭 "[Etunimi91458Oulaisten kaupunki/ Honkamajan kuntolenkit
Mikä ihme riivaa Oulaisten kaupunkia,kun honkamajan kuntopolut jääneet koko kesältä heinät ja pajut niittämättä? Kenelle17447Olet oikeasti paras
Mies, joka on kohdalleni sattunut. Oikeastaan liian hyvä, jos rehellisiä ollaan. Sinun paikkaasi ei kukaan vie, vaikka e27446- 34438
Paula hakenut rovaniemelle
Ei vissiin seurakunnan hommat ollutkaan mieleen vai olisiko kenties joku muu syy?19432