Daten aus anderer Datei abgleichen

[Gast 123]

Hallo Rene

ich habe schon verstanden das ich mit den Maschinen Nr wahrscheinlich den String aufarbeiten muss, kein Thema.

In deiner Beispieldatei konnte ich deine Beispiel Nr. leider nicht finden.  Kannst du mir eine kleine Beispieldatei mit nur 5-10 Datensaetzen in Overview und Delivered erstellen, wo die Serien Nummern übereinstimmen, und einige Daten die nicht gleich sind!  Die ungleichen am besten in Schriftfarbe Rot, dann erknne ich es schneller. Danke .....

Noch eine Frage, kann es sein das bei zwei Zahlen der vordere Satz und der hintere vertauscht sein können?  Die eine Abteilung die Nummer nach vorne schreibt, und die andere Abteilung die Nummer hinten anhaengt??  Für mich ist das egal, es ist nur mehr Programmieraufwand, den ich mir nicht mache, wenn es nicht sein muss!! 

Sobald ich euer System verstanden habe, und weiss wie ich suchen musss, haben wir sicher eine gute Lösung.

mfg  Gast 123

[UltraTM]

Hallo Gast 123,

die Seriennummern werden immer überall gleich geführt.


In Overview sind nur Maschinen drin die auch in unserer Firma sind aktuell.

In Delivered 2017, 2018 usw welche ausgeliefert wurden. Das heißt diese werden von Overview dann dort hin verschoben. Es gibt also nie die gleichen Maschinen in Overview und Delivered.
Wastl hatte meine_datei und datei_zum_abgleich in eine kopiert. Was darin Maschinenliste ist, ist meine Datei. Und in der Logistikdatei gibt es Overview, Delivered 2017, Delivered 2018 usw.
Grund war eben in meine_datei die sachen aus der Logistikdatei abgleichen zu können welche schon nicht mehr bei uns sind.

Jetzt glaub ich kommen wir dem Problem auf die schliche hoffe ich :)

Daher hätte ich die Delivered gar nicht mehr berücksichtigt, da wie es mir jetzt passiert in der aktuellen Datei der Logistik ist der Tab Delivered 2017 verschwunden, da die den archiviert haben und ich darauf keinen Zugriff habe :(

Das wäre der Grund mit das man in Overview suchen sollte da diese Da (ja) sind und alle die nicht da sind müssen ja weg (nein) sein.

[Gast 123]

Hallo Rene

anbei eine neue Beispieldatei mit mehreren Makros.  Dabei gab es einen interessanten Effekt der mir völlig neu war.

Zuerst habe ich das Makro Standardmaessig mit der Find Methode entwickelt.  Die Zeit war mir zu lang, ca. 45 Sekunden.  Zu meiner Verblüffung laueft die For Next Schleife mit 2 Sekunden wesentlich schneller, und findet mehr Serien Nummern.  Bei Find waren es nur 69 gefundene Nummern, bei For Next sind es 89 Serien Nummern.  Ich raetsele selbst wo dieser Unterschied herkommt???  Nehme es vorerst nur zur Kenntnis!!

Im Makro kannst du die Datei Namen und die Zeile ab der ich zu suchen beginne in den Const Anweisungen festlegen.  Dadurch kann man das Makro leichter anpassen.  Es gibt auch ein Makro für zwei Delivered Sheet zu druchsuchen, 2017 + 2018.  Du must dem Button dafür nur das andere Makro zuweisen.  Jetzt sucht er nur in Delivered 2017.

Neu eingefügt ist eine Such Tabelle, wo du in der Abgleich Mappe nach belibigen Daten suchen kannst, die dann als kompletter Datensatz aufgelistet werden.  Ob ein oıder mehrer Begriffe, durch ", " getrennt, mit Gross/Kleinschreibung, ganzes Wort suchen oder Teibegriffe ist als Option einstellbar!

Mir faellt gerade ein das wir in "Meine_datei" den Ordner Namen und die Tabelle für die Abgleich Datei in zwei Zellen schreiben können, die ich auslesen kann.  Sonst must du immer wieder die Const Anweisung aendern.  z.B. für "Delivered 2019".  Wenn das günstiger ist kann ich das im Makro einfügen, ich muss es nur Wisssen.  Die Zellen für diese Eingaben must du aber selbst festlegen.

So, jetzt warte ich mal ab ob meine arbeit gut ist, bin gespannt auf deine Rückmeldung.

mfg  gast 123

[UltraTM]

Hallo Gast 123,

Vielen Dank für die ganze Arbeit. Ich habe nur das Problem das ich die Datei_zum_Abgleich offen habe zusätzlich zu meiner er aber das nicht erkennt.

Oder wie soll das funktionieren?

Hast dir da ja ganz schön Arbeit gemacht. Bin ja gespannt wie es funktioniert. Ich hoffe nur das die Logisitik nicht dauernd spalten ändert, die Arbeiten gerne mal an ihrer Datei rum. Aber die Seriennummer bleibt zumindest imemr gleich, das schon mal gut.

Hier der Fehler der bei mir kommt:
Set Wb = Workbooks(Mappe)     'Datei

Index ausserhalb des gültigen Bereichs.


Grüße René

[Gast 123]

Hallo Rene

weil du schreibst das die Logistik manchmal die Spalten aendert habe ich das Makro ein wenig erweitert, damit es Universel ist!
Den Code kannst du im Modul:  "Module1_ForDel" komplett austauschen, dann kannst du die Spalten oben in der Const Anweisung als Buchstabe "F" oder als als Zahl 6 angeben.  Wenn sich etwas aendert schreibst du oben in Const MaschSpa = "F" einfach die neue Spalte rein, fertig!! 
Im Makro musste ich eine Zeile aendern, mehr nicht!  Im Makro für 2 Jahre, Deliver 2017 + 2018 kannst du es wie hier gezeigt selbst aendern. 

Const MaschSpa = "F"    'Spalte der Maschinen Nummer (F) oder als Nummer
   'If DEL.Cells(i, 6) = MaschNr Then    'alter Code!
    If DEL.Cells(i, MaschSpa) = MaschNr Then

zur 2. Frage:  ich hatte zum testen den Befehl On Error abgeschaltet, durch das vorgestellt ' Zeichen.  Dann sieht Excel die Zeile als Kommentar an!!
Aktiviere den Befehl bitte wieder indem du das ' Zeichen entfernst.  Die Datei muss zum suchen immer geöffnet sein, wenn du es vergisst kommt die MsgBox mit Fehlermeldung und fragt ob die Datei geöffnet ist??
     On Error GoTo Fehler
     Set Wb = Workbooks(Mappe)     'Datei

Würde mich freuen wenn ansonsten alles gut klappt ...

mfg  Gast 123

Code:

Option Explicit      '20.1.2017  Gast 123  Clever Form
'Modul1 ForDel
Const AbgDatei = "Datei_zum_Abgleich"  'Original Name der Abgleich Datei
Const Deliver1 = "Delivered 2017"      'kann jedes Jahr geandert werden
Const MaschSpa = "F"    'Spalte der Maschinen Nummer (F) oder als Nummer
Const MZ1 = 19          '1.Zeile in Maschinenliste wahlweise 19 oder 22


'Module für Serien Nr in Delivered

Sub SerienNr_vergleichen_ForNext()
Dim lzDV As Long, lzML As Long
Dim MaschNr As String, d1 As Long
Dim i As Long, n As Long, j As Long
Dim Wb As Workbook, DEL As Worksheet

On Error GoTo Fehler
Set Wb = Workbooks(AbgDatei)
Set DEL = Wb.Worksheets(Deliver1)
lzDV = DEL.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Maschinenliste")
    lzML = .Cells(Rows.Count, 1).End(xlUp).Row
    'ganze Spalte G auf "Nein" setzen
    .Range("G" & MZ1 & ":G" & lzML) = "Nein"
   
    'Schleife zum suchen der Serien-Nr in Delivered
    For j = MZ1 To lzML
       Application.StatusBar = lzML & "  /  " & j
       MaschNr = .Cells(j, 5).Value
       If Left(MaschNr, 1) = "!" Then MaschNr = Mid(MaschNr, 2, 50)
       For i = 2 To lzDV
         'If DEL.Cells(i, 6) = MaschNr Then
         If DEL.Cells(i, MaschSpa) = MaschNr Then
         If .Cells(j, 7) = "Nein" Then
           .Cells(j, 7) = "Ja"
            n = n + 1:  Exit For
         Else
           .Cells(j, 7) = "Ja / dopp"
            d1 = d1 + 1:  Exit For
         End If
         End If
       Next i
    Next j
End With

Application.ScreenUpdating = True
If d1 > 0 Then MsgBox d1 & "  doppelte in Deliver 2017 gefunden"

MsgBox n & "  Serien Nr. gefunden"
Exit Sub

Fehler:  MsgBox "unerwarteter Fehler - existiert Sheet Delivered 2017 ??"
End Sub

[UltraTM]

Hallo Gast 123,

Habe beide Dateien offen nur leider erkennt er die nicht.
Liegt es dran das er bei mir beide dateien in einem extra fenster öffnet?
Habe nämlich Excel damals umgestellt zwecks des Hintergrundes das wir 2 Bildschirme nutzen, um so 2 Dateien gleichzeitig verwenden zu können.
Frage ist ob man da auf einen Datei angeben (explorer Dialog) ausweichen könnte oder den Pfad zur Datei angeben kann diese liegt bei uns auf dem Server immer im gleichen Ornder/Laufwerk

Bestimmt denke ich nur wieder zu kompliziert :16:


Dann könnte ich es ausgiebig testen :)


Grüße René

[UltraTM]

Hallo Gast 123,

zuhause mit Excel funktioniert es in 2 Fenstern.


Der Sache muss ich jetzt mal auf den Grund gehen.


Das einzige ist im Suchen Tab klicke ich suchen und er findet zwei Maschinen.

Auch in der liste auf Seriennummern vergleichen findet er 89 Maschinen.

Ändert er auch wo das Ja und Nein im Moment?

Das ich da mal Maschinen umschreibe und teste usw.?


z.b. in Zeile 185 ist die Maschine mit der Nummer: 201707016033030  diese ist in Delivered 2017 drin. Beim vergleichen ändert er aber kein Ja in nein nicht in meiner Zeile G noch in der Testzeile AH. 

Das suchen Arbeitsblatt ist echt super :)

Da muss man nicht was aus der großen liste suchen usw. sondern zack hat man alle Daten da. Echt klasse gemacht


Grüße René

[Gast 123]

Hallo Rene

es freut mich sehr das dir das neue Suchprogramm gefaellt.  Das erleichtert dir sicher manche Arbeit.

Zum Thema Datei vergleichen, die Dateien müssen beide im selben Excel Programm gestartet werden, NICHT in einer neuen Excel Anwendung.  Dann kannst du auch ein zweites Fenster öffnen. Wenn der Suchlauf nicht klappt schaue bitte zuerst mal ob die Suchspalten übereinstimmen. In dem neuen Code den ich geschickt habe kann man die Spalte ja in der Const Anweisung von Hand vorgeben und beliebig aendern. Die Hilfsspalte AH benutze ich nicht mehr, es sei denn sie ist für dich von Vorteil.  Prüfe bitte zuerst mal ob alle Such Parameter stimmen??

Ich gehe Anfang Feburar in Urlaub, werde ca. einen Monat Pause im Forum machen, möchte diesen Thread vorher noch gut abschliessen!

mfg Gast 123

[UltraTM]

Hallo Gast 123,

die Suche funktioniert auf der Such Seite.


Problem habe ich nur mit der Seriennummer vergleichen.

Das er eben in der Spalte G welches richtig ist nichts auf Nein/Ja ändert.

Zum Test habe ich eben die Maschine in Zeile 285 genommen. Diese ist in Delivered 2018 verfügbar. Er meldet auch das er 89 Maschinen gefunden hat, nur leider ändert er diese Maschine z.b. nicht um.

Urlaub das bräcuhte ich auch mal wieder. Wir haben auch Weihnachten durchgearbeitet bis auf die Feiertage. Seit letzem August keinen Urlaub mehr gehabt. Du glücklicher :)


Grüße René

[Gast 123]

Hallo Rene

ich nehme an das du die Suche nach Maschinen Nr mit dem Programm Modul:  "Modul1_ForDel" mit  Makro "Sub SerienNr_vergleichen_ForNext" durchführst. Dieses Makro vergleicht aber nur das Jahr 2017!!

Ich habe in weiser Voraussicht ein zweites Makro geschrieben für das Jahr 2017 + 2018.  Dem Button must du mit der rechten Maustaste dieses Makro zuweisen:  "SerienNr_vergleichen_ForNext_2".  Es befindet sich im Modul:  "Modul1_ForDel2"  Dort findest du in der Const Anweisung beide Dateien, DEL1 für "Delivered 2017" und DEL2 für "Delivered 2018".  Diese Dateinamen must du in 2019 natürlich wieder anderen, nur das Jahr!!  2018 + 2019!  Eine Tabelle "Delivered 2018" mit Daten aus 2018 muss natürlich in der Datei "Daten_vergleichen" vorhanden sein!!

Anbei der geaenderte Code zum auswechseln, ich habe auch hier die Suchspalte oben in Cosnt festgelegt, falls die Logistik mal wieder diie Spalten aendert. Diese Spalte gillt aber für beide Jahre. Soltte die Logistik die Spalten in 2018 geaendert haben ist das auch kein Problem, dann schreibst du eine zweite Const Anweisung "MaschNr2 = xxx" und aenderst in der zweiten For Next Schleife "MaschNr" auf "MaschNr2" ab. Das schöne ist, Makros sind universel einsetzbar!!  Die kann man nach belieben anpassen!!

Ich denke das ist so einfach das du es selbst kannst.  Ansonsten frohes schaffen und freue dich auf deinen naechsten Urlaub.

herzliche Grüsse 

von Gast 123
  

Code:

Option Explicit      '20.1.2017  Gast 123  Clever Form

Const AbgDatei = "Datei_zum_Abgleich"  'Original Name der Abgleich Datei
Const Deliver1 = "Delivered 2017"      'kann jedes Jahr geandert werden
Const Deliver2 = "Delivered 2018"      'kann jedes Jahr geandert werden
Const MaschSpa = "F"    'Spalte der Maschinen Nummer (F) oder als Nummer
Const MZ1 = 19          '1.Zeile in Maschinenliste wahlweise 19 oder 22


'Module für Serien Nr in Delivered
'** sucht in Delivered 2017 + 2018

Sub SerienNr_vergleichen_ForNext_2()
Dim Wb As Workbook, d1, d2 As Long
Dim MaschNr As String, lzML As Long
Dim DEL1 As Worksheet, lzDV As Long
Dim DEL2 As Worksheet, lzDV2 As Long
Dim i As Long, n As Long, j As Long

On Error GoTo Fehler
Set Wb = Workbooks(AbgDatei)
Set DEL1 = Wb.Worksheets(Deliver1)
Set DEL2 = Wb.Worksheets(Deliver2)
lzDV = DEL1.Cells(Rows.Count, 1).End(xlUp).Row
lzDV2 = DEL2.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Maschinenliste")
    lzML = .Cells(Rows.Count, 1).End(xlUp).Row
    'ganze Spalte G auf "Nein" setzen
    .Range("G" & MZ1 & ":G" & lzML) = "Nein"
   
    '1. Schleife zum suchen der Serien-Nr in Delivered 2017
    For j = MZ1 To lzML
       MaschNr = .Cells(j, 5).Value
       If Left(MaschNr, 1) = "!" Then MaschNr = Mid(MaschNr, 2, 50)
       For i = 2 To lzDV
         'If DEL1.Cells(i, 6) = MaschNr Then
         If DEL1.Cells(i, MaschSpa) = MaschNr Then
         If .Cells(j, 7) = "Nein" Then
           .Cells(j, 7) = "Ja"
            n = n + 1:  Exit For
         Else
           .Cells(j, 7) = "Ja / dopp"
            d1 = d1 + 1:  Exit For
         End If
         End If
       Next i
    Next j

    '2. Schleife zum suchen der Serien-Nr in Delivered 2018
    For j = MZ1 To lzML
       MaschNr = .Cells(j, 5).Value
       If Left(MaschNr, 1) = "!" Then MaschNr = Mid(MaschNr, 2, 50)
       For i = 2 To lzDV
         'If DEL2.Cells(i, 6) = MaschNr Then
         If DEL2.Cells(i, MaschSpa) = MaschNr Then
         If .Cells(j, 7) = "Nein" Then
           .Cells(j, 7) = "Ja"
            n = n + 1:  Exit For
         Else
           .Cells(j, 7) = "Ja / dopp"
            d2 = d2 + 1:  Exit For
         End If
         End If
       Next i
    Next j
End With

Application.ScreenUpdating = True
If d1 > 0 Then MsgBox d1 & "  doppelte in Deliver 2017 gefunden"
If d2 > 0 Then MsgBox d2 & "  doppelte in Deliver 2018 gefunden"

MsgBox n & "  Serien Nr. gefunden"
Exit Sub

Fehler:  MsgBox "unerwarteter Fehler - existiert Sheet Delivered 2018 ??"
End Sub