Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1468to1472
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

CSV Import (Done) - Abgleich Titel Menge und Besta

CSV Import (Done) - Abgleich Titel Menge und Besta
12.01.2016 15:17:12
Reckling
Hi zusammen,
ich arbeite zurzeit an einem Lagerbestandsabgleich und bräuchte Hilfe für ein spezifisches Problem. Es geht dabei um den Import einer CSV Datei mit Verkäufen, welche auf einem Hilfblatt gesplittet wird. Dies funktioniert soweit tadellos.
Der CSV Import sieht wie folgt aus (Sheetname "Afterbuy Einlesen"):
Rechnungsnummer Rechnungsdatum Menge Artikelbeschreibung
49592 05.01.2016 1,00 5x MAXELL CR2032 LITHIUM BATTERIEN BATTERY KNOPFZELLE 3V --- OVP --- NEU
Diese täglichen Verkäufe werden ins Hilfsheet eingelesen und sollen dann mit den Beständen abgeglichen werden. Diese befinden sich mit etwas anderen Titeln auf dem Sheet "Lager", welches wie folgt aussieht:
Bezeichnung Bestand
5x MAXELL CR2032 LITHIUM BATTERIEN BATTERY KNOPFZELLE 3V 10
In diesem Fall soll der Code zunächst den Artikel 5x MAXCELL CR2032 im Lager Sheet finden, sich die Reihe merken und folgich den Bestand um die Menge aus dem Einlesen Sheet reduzieren.
Mein jetziger Ansatz arbeitet mit VLOOKUP und Spaltenidentifizierung, jedoch glaube ich, dass es einfacher wäre den String direkt zu suchen und den Wert der Reihe zu speichen.
Über jede Idee bzw. Ansatz bin ich dankbar.
Beste Grüß

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: CSV Import (Done) - Abgleich Titel Menge und Besta
12.01.2016 18:28:24
Reckling
Die Lösung.
Was noch fehlt ist ein ungefährer Match des Strings (Title) und eben kein 100% Match wie es jetzt ist.
Jemand ne Idee?
Sub Einlesen()
Dim strFileName As String, arrDaten, arrTmp, lngR As Long, lngLast As Long
Const cstrDelim As String = "," 'Trennzeichen
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Datei wählen"
.InitialFileName = "d:\HBZ\Energieausweise\Berechnungsgrundlagen\*.csv"  'Pfad anpassen
.Filters.Add "CSV-Dateien", "*.csv", 1
If .Show = -1 Then
strFileName = .SelectedItems(1)
End If
End With
Sheets("Afterbuy Einlesen").Select
'Select Afterbuy File
If strFileName  "" Then
Application.ScreenUpdating = False
Open strFileName For Input As #1
arrDaten = Split(Input(LOF(1), 1), vbCrLf)
Close #1
For lngR = 0 To UBound(arrDaten)
arrTmp = Split(arrDaten(lngR), cstrDelim)
If UBound(arrTmp) > -1 Then
With ActiveSheet
lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
lngLast = Application.Max(lngLast, 0)
.Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
= Application.Transpose(Application.Transpose(arrTmp))
.Cells(lngLast, 11) = Mid(strFileName, InStrRev(strFileName, "\") + 1)
End With
End If
Next lngR
End If
' Search and replace ("")
Range("A1:AL2500").Select
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
' Search for sold items and adjust value in warehouse
Dim Title As String
Dim Row As Integer
Dim Colomn As Integer
Dim Menge As Double
For i = 1 To lngLast
Application.ScreenUpdating = True
Sheets("Afterbuy Einlesen").Select
Title = ActiveSheet.Cells(i + 2, 4).Value
Menge = ActiveSheet.Cells(i + 2, 3).Value
Sheets("Lager").Select
Row = Sheets("Lager").Columns("F:F").Find(What:=Title, SearchOrder:=xlByRows,  _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
ActiveSheet.Cells(Row, 8).Value = ActiveSheet.Cells(Row, 8).Value - Menge
Next i
End Sub

Anzeige
Levenshtein3
13.01.2016 19:29:59
KlausF
Moin,
such' mal ln der Recherche dieser Seite nach Levenshtein3.
Evtl. hilft Dir das oder diese Datei ja weiter:
https://www.herber.de/bbs/user/102759.xls
Gruß
Klaus

AW: Levenshtein3
14.01.2016 09:38:38
Reckling
Hilft sehr, danke!
Eine Frage noch: Wie man bei dem Artikel sieht handelt es sich um 5x Maxell CR2032. Die Menge ist allerginds 1, jedoch impliziert der Titel 5 Stücke.
Gibt es eine Mäglich den String "Titel" auf Integer in den ersten 5 Zeichen zu checken, den Intteger auszulesen und mit der Menge zu multiplizieren?
Vielen Dank für die großartige Hilfe!

Anzeige
Function TextWeg
14.01.2016 10:33:43
KlausF
Moin,
hier mal eine Funktion aus meiner Sammlung:
Function TextWeg(rng As Range)
Dim intZ As Integer
For intZ = 1 To Len(rng)
Select Case Asc(Mid(rng, intZ, 1))
Case 48 To 57
TextWeg = Val(TextWeg & Mid(rng, intZ, 1))
End Select
Next intZ
End Function
Vielleicht kannst Du das entsprechend anpassen.
Gruß
Klaus

AW: Function TextWeg
14.01.2016 19:08:34
Reckling
Danke nochmals.
Ich versuche gerade mit der MP Function die Reihe eines Matches der beiden Sheets zu identifizieren falls bei Match 80%, dann Anpassung Menge. Stehe aber auf dem Schlauch, haste du eine Idee?

Anzeige
AW: Function TextWeg
15.01.2016 16:30:20
KlausF
Hallo,
keine Ahnung, was Du meinst. Ohne Ansicht einer Datei ist das auch schwierig.
Bin außerdem mit einem Mac unterwegs, da lässt sich das schlecht testen.
Die Syntax für einen Vergleich lautet
If mp(Suchwort, Vergleichstring) >= 0.8 Then
Wenn Du hier eine Datei reinstellst bekommst Du evtl. Hilfe von kompetenterer Seite.
Und ... man hat es hier immer sehr gern, wenn man sein Anliegen mit einem Hallo
beginnt und mit Gruß + Namen beendet ...
Gruß
Klaus

AW: Function TextWeg
15.01.2016 16:41:05
Reckling
Hallo Klaus,
vielen Dank für die prompte Antwort. Die Syntax der Funktion haben wir verstanden, jedoch fällt es uns schwer einen konkreten Nutzen für unser Vorhaben zu finden. Gerne laden wir die Files hoch und würden uns über Deine Hilfe freuen. Vielleicht hast du kurz Zeit, um dir das Problem einmal anzuschauen.
Hier die Hauptfile:
https://www.herber.de/bbs/user/102819.xlsm
Hier die File zum Einlesen der Verkäufe is eine CSV Datei, welche auf dem "Afterbuy Einlesen" Sheet sauber dargestellt wird (siehe VBA Code)
Das Problem ist nun zum einen, dass die Titel der Einlesen File nicht genau mit den Titelen der auf dem Lager Sheet übereinstimmen, sodass ein Finden des Titels und Anpassung des Bestands schwierig ist. Zum anderen wird oft 5x oder 16er Pack verkauft, wober der Bestand in einzel Stücken ist. Diese Zahl steht immer am Anfang des Strings, weshalb die eigentlich damit bei diesen Fällen multipliziert werden müsste.
Besten Dank und viele Grüße,
Herbert

Anzeige
vieles unklar
15.01.2016 18:49:03
KlausF
Hallo Herbert,
ehrlich gesagt, verstehe ich sehr vieles nicht an der Datei und Deinen Fragen.
1. Ganz egal, welche Ähnlichkeitsprüfung genommen wird, musst Du erst einmal
dafür Sorge tragen, dass die Artikelbeschreibung auf dem Einlesen-Sheet nicht
gesplittet wird. Du bekommst sonst immer ein falsches Ergebnis (siehe Spalte
Artikelnummer).
2. Ich würde bei unterschiedlichen Artikelbezeichnungen allerdings nie ein Makro
entscheiden lassen, was gleich und ungleich ist und Berechnungen ausführen lassen.
Wenn keine 100% Übereinstimmung gefunden wird müsste die Zeile mit jeder Zeile
des Lagers verglichen und die größte Übereinstimmung angezeigt werden. Da eignet
sich die Levenshtein3-Berechnung besser, weil sie selber den größten Wert ermittelt.
Ein entsprechendes Makro findest Du in der Recherche unter Levenshtein3 mit
Beispieldatei.
3. Warum Du die Bestellmenge mit der Gebindegröße multiplizieren willst erschließt sich
mir nicht. Was macht das für einen Sinn? Die Lageranzahl weist doch immer die Anzahl der
Verkaufseinheiten aus. Oder werden auch Einzelposten aus der Verkaufseinheit verkauft?
4. Man sollte doch davon ausgehen, dass die Artikelnummern immer gleich bleiben (?)
Warum vergleichst Du also nicht einfach die Spalten mit den Artikelnummern?
Falls die Lagerbestände eigene Artikelnummern aufweisen sollten, kann man doch
zum Vergleich trotzdem immer die Artikelnummer des Einlesen-Sheets mit auflisten.
Gruß
Klaus

Anzeige
AW: vieles unklar
15.01.2016 19:09:44
Reckling
Hallo Klaus,
vielen Dank für deine schnelle Antwort.
1. Die Idee dahinter ist, dass der Großteil der zur Indentifikation mit dem Lager Sheet notwendigen informationen bereits in dem ersten Teil des Strings steht. Es sollte also vollkommen ausreichen einen Abgeleich darauf basiernd zu erstellen.
2.Ich habe bereits nach Deinem letzten Tipp die Levenshtein3 Berechnung gesucht, wurde jedoch nicht fündig bzw. konnte das Ergebnis nicht sinnvoll verwenden.
3.Es werden auch einzelne Einheiten verkauft. Der Bestand wird pro Einzelstück geführt, d.h. bei 5x Maxcell sollte der Bestand um 5 Einheiten reduziert werden (Es handelt sich hierber schon um sogn. Blister mit mehreren Einheiten).
4.Leider bleiben die Artikelnummern nicht gleich, das würde vieles vereinfachen. Der Hintergrund ist simpel: Es gibt verschiedene Verkaufsplattformen, welche über Afterbuy zusammengefasst werden. Die Artikelnummern werden von dort importiert und wir haben keinen Einfluss darauf. D.h. also, dass ein und derselbe Artikel verschiedene Artikelnummern haben kann. Ein gutes Beispiel ist Zeile 37/38.
Da wir gerade Inventur gemacht suchen wir nach einer zeitnahen Lösung. Solltest Du einen guten Ansatz oder gar eine Lösung inpetto haben, würden wir Dir gerne etwas aus unserem Produktportfolio als Ausgleich anbieten. In jedem Falle aber vielen Dank für Deine Hilfe bis hier hin.
Viele Grüße,
Herbert

Anzeige
zu komplex
15.01.2016 22:07:46
KlausF
Hallo Herbert,
das Thema ist mir ehrlich gesagt zu komplex. So viel Zeit habe ich bei weitem nicht.
Bei näherem Hinsehen ergeben sich noch Fragen, die ich hier noch mal stelle für den Fall,
dass einer der VBA-Cracks noch Lust und Zeit dafür findet.
Fragen:
sollen nur die am Anfang stehenden Ziffern mit x-Zeichen berücksichtigt werden?
Was ist z.B. mit Zeile 39 (Sanyo 8-er Pack) oder Zeile 60 (Powerone .... 60 Stück)
Erschwerend kommt hinzu, dass mal mit und mal ohne Blancs garbeitet wird (Zeile 473)
Im Lager sollten die Texte doch bereits "bereinigt" vorliegen. Wieso steht in Zeile 2 aber noch
5x MAXELL ... ? Die Zeile müsste nach meinem Verständnis mit MAXELL anfangen und bei
Bestand sollte dann 45 stehen, oder?
Ich stelle mal auf "offen".
Gruß
Klaus

Anzeige
Ziffern finden
16.01.2016 00:17:40
KlausF
Hallo Herbert,
habe mich jetzt doch einmal an den ersten Teil ran gesetzt.
Nachfolgendes Makro findet auf Sheet "Afterbuy Einlesen" die Ziffern,
die am Anfang mit einem "x" stehen, multipliziert die Mengenangabe
und korrigiert die Artikelbeschreibung
Sub ZiffernFinden()
Dim i As Long
Dim xPos As Integer
Dim Anzahl As Integer
Dim Cut As Integer
Dim Artikel As String
Dim Menge As String
On Error Resume Next
For i = 2 To ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
'die ersten 6 Zeichen auf "x" untersuchen
If InStr(Mid(Cells(i, 4), 1, 6), "x") Then
Artikel = Cells(i, 4).Text
'Position von x auslesen
xPos = InStr(Cells(i, 4), "x")
'Cut-Position auslesen / steht Blanc hinter x?
If Left(Artikel, xPos + 1) = " " Then
Cut = xPos + 3
Else
Cut = xPos + 2
End If
'Ziffern auslesen / steht Blanc vor x?
If Left(Artikel, xPos - 1) = " " Then
Anzahl = Left$(Artikel, xPos - 2)
Else
Anzahl = Left$(Artikel, xPos - 1)
'Fehler ausschalten
If Anzahl = 0 Then Anzahl = 1
End If
'mit Menge multiplizieren
Cells(i, 3).Value = Cells(i, 3).Value * Anzahl
'Artikel cutten
Artikel = Mid$(Artikel, Cut)
Cells(i, 4).Value = Artikel
End If
Next i
On Error GoTo 0
End Sub
Gruß
Klaus

Anzeige
AW: Ziffern finden
16.01.2016 00:54:32
Reckling
Hallo Klaus,
zunächst vielen Dank. Zu so später Stunde noch aktiv :)
Zu Deinen Fragen:
1
Fragen:
sollen nur die am Anfang stehenden Ziffern mit x-Zeichen berücksichtigt werden?
Was ist z.B. mit Zeile 39 (Sanyo 8-er Pack) oder Zeile 60 (Powerone .... 60 Stück)
Erschwerend kommt hinzu, dass mal mit und mal ohne Blancs garbeitet wird (Zeile 473)

Es ist schwierig, da alle Zahlen berücksichtigt werden müssen, d.h. 16er,16 er Pack, 5x & 5 x
Im Lager sollten die Texte doch bereits "bereinigt" vorliegen. Wieso steht in Zeile 2 aber noch
5x MAXELL ... ? Die Zeile müsste nach meinem Verständnis mit MAXELL anfangen und bei
Bestand sollte dann 45 stehen, oder?

Das ist korrekt. Es handelt sich hierbei lediglich um einen Test. Die im Begriffe im Lager sind bereinigt.
Ich habe ein wenig mit der SoundEx Funktion bzgl. des Abgleichs der Titel gespielt, jedoch bisher keine guten Ergebnisse erzielt.
Für mich geht es morgen weiter. Schönes Wochenende!
Grüße,
Herbert

Anzeige
AW: Ziffern finden
16.01.2016 14:19:14
Reckling
Hallo Klaus,
vielen Dank für die sehr elegante Lösung bzgl. des Problems mit den Ziffern. Ich habe dein Vorschlag nun so angepasst, dass es auf die anderen Ziffernvormate ebenfalls passt.
Das Problem der Titelsuche besteht allerdings weiterhin. Hättest Du eine Idee oder kennst du jemanden hier im Forum, der mir weiter helfen könnte?
Viele Grüße,
Herbert

AW: Ziffern finden
17.01.2016 14:13:33
KlausF
Moin Herbert,
wie gesagt, habe ich dafür keine Zeit.
Hier im Forum gibt es bestimmt Einige, die Dir weiter helfen könnten.
Und sei gewiss, dass die Meisten hier auch diesen Threat mitlesen.
Ob Lust an der Lösung dieses Themas besteht weis ich natürlich nicht.
Nach meinem Dafürhalten ist Dein Ansatz allerdings auch falsch.
Ich vermute, dass Du mit "Ähnlichkeit" kein korrektes Ergebnis bekommst.
Der meiner Meinung nach einzige Weg besteht darin, Quelle und Ziel
vorher gleich zu schalten um am Ende gleiche Strings vergleichen zu können.
z.B.
Varta Powerone Typ 312 Hörgerätebatterie Zinc Air P312 PR41 ZL3
Varta Powerone Typ 13 Hörgerätebatterie Zinc Air P13 PR48 ZL2
wird eine Übereinstimmung von fast 100% erreichen, obwohl unterschiedliche Artikel.
dem gegenüber
ORIGINAL SAMSUNG EF-ZG920 CLEAR VIEW COVER SCHUTZHÜLLE CASE - Galaxy S6 -SCHWARZ
ORIGINAL SAMSUNG EF ZG920 CLEAR VIEW COVER SCHUTZHÜLE CASE - Galaxy S6 - SCHWARZ
wird keine 100% Übereinstimmung haben, obwohl nur anders geschrieben (Schreibfehler)
Viel Glück dabei
Klaus

AW: Function TextWeg
15.01.2016 18:43:09
Reckling
Hallo,
mein Ansatz:
    'String Vergleichswerte
For I = 1 To lngLast
Sheets("Afterbuy Einlesen").Select
For a = 1 To lngLast
'String Vergleich > 80%
If mp(Sheets("Afterbuy Einlesen").Cells(a + 2, 4).Value, Sheets("Lager").Cells( _
I + 1, 6).Value) >= 0.8 Then
'Mengen Vergleich bzw. Angleich
Sheets("Lager").Cells(I + 1, 8).Value = Sheets("Lager").Cells(I + 1, 8).Value -  _
Sheets("Afterbuy Einlesen").Cells(a + 2, 3).Value
Else
End If
Application.DisplayStatusBar = True
Application.StatusBar = "Einlesen Artikel " & a & " von " & lngLast
Next a
Application.DisplayStatusBar = True
Application.StatusBar = "Lager Artikel " & I & " von 350"
Next I
End Sub
Hängt sich jedoch leider bei der mp Funktion auf, ein infinite Loop.
Grüße,
Herbert

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige