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
1509
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
Kiitos nainen
Kuitenkin. Olet sitten ajanmerkkinä. Tuskin enää sinua näen ja huomasitko, että olit siinä viimeisen kerran samassa paik123790MTV: Kirkossa saarnan pitänyt Jyrki 69 koki yllätyksen - Paljastaa: "Se mikä oli hyvin erikoista..."
Jyrki Linnankivi alias Jyrki 69 on rokkari ja kirkonmies. Teologiaa opiskeleva Linnankivi piti elämänsä ensimmäisen saar781997Hyväksytkö sinä sen että päättäjämme ei rakenna rauhaa Venäjän kanssa?
Vielä kun sota ehkäpä voitaisiin välttää rauhanponnisteluilla niin millä verukkeella voidaan sanoa että on hyvä asia kun5431610Kirjoita yhdellä sanalla
Joku meihin liittyvä asia, mitä muut ei tiedä. Sen jälkeen laitan sulle wappiviestin831267Olet hyvin erilainen
Herkempi, ajattelevaisempi. Toisaalta taas hyvin varma siitä mitä haluat. Et anna yhtään periksi. Osaat myös ilkeillä ja671077Yksi syy nainen miksi sinusta pidän
on se, että tykkään luomusta. Olet luonnollinen, ihana ja kaunis. Ja luonne, no, en ole tavannut vielä sellaista, joka s331018Hyödyt Suomelle???
Haluaisin asettaa teille palstalla kirjoittelevat Venäjää puolustelevat ja muut "asiantuntijat" yhden kysymyksen pohditt214898Hyvää Joulua mies!
Toivottavasti kaikki on hyvin siellä. Anteeksi että olen hieman lisännyt taakkaasi ymmärtämättä kunnolla tilannettasi, o60873- 171854
Paljastavat kuvat Selviytyjät Suomi kulisseista - 1 päivä vs 36 päivää viidakossa - Katso tästä!
Ohhoh! Yli kuukausi viidakossa voi muuttaa ulkonäköä perusarkeen aika rajusti. Kuka mielestäsi muuttui eniten: Mia Mill3798