Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > Tabellenvergleich: was wurde geändert?
Normale Version: Tabellenvergleich: was wurde geändert?
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2

Rabe

13.10.2015, 13:14
Hallo,

mir stellen sich gerade ein paar Fragen:
Wie kann ich feststellen, was zwischen Tabelle1 und Tabelle2 geändert wurde? (Spalte M wurde jetzt nur für die Anfrage ausgefüllt, normalerweise ist der Vergleich über beide Tabellen)
Wie kann ich in die Formel in Spalte N und in die bedingte Formatierung den Blattnamen allgemein als Link (=$P$3) aufnehmen? Dann könnte ich die Blätter beliebig umbenennen.

Tabelle1
LMNOP
3Register:Tabelle1
4_verändert
5Hilfsspalte, kann ausgeblendet werden!Hilfsspalte, kann ausgeblendet werden!
6Tabelle1Tabelle2Filterspalte
7B600 - BR-1225 -  -  - 1 -  - Panasonic - BR-1225_verändert
8C100, C101, C102, C200, C202, C203, C204, C300, C301, C303, C306, C307, C311, C319, C320, C322, C401, C412, C417, C420, C500, C501, C502, C503, C504, C505, C506, C507, C508, C513, C514, C515, C516, C517, C518, C519, C523, C525, C528, C529, C530, C531, C533, C536, C537, C540, C541, C542, C543, C544, C545, C546, C547, C549, C550, C551, C600, C601, C602, C603, C604, C608, C701, C702 - 100n - 50V - 10% - 64 - C_sm_0603 -  - C100, C101, C102, C200, C202, C203, C204, C205, C300, C301, C303, C306, C307, C311, C319, C320, C322, C407, C412, C417, C420, C500, C501, C502, C503, C504, C505, C506, C507, C508, C513, C514, C515, C516, C517, C518, C519, C523, C524, C525, C526, C527, C528, C529, C530, C531, C532, C533, C536, C537 - 100n - 50V - 10% - 50 - C_sm_0603 -  - _verändert
9C302, C304 - 10n - 50V - 10% - 2 - C_sm_0603 -  - C302, C304, C406 - 10n - 50V - 10% - 3 - C_sm_0603 -  - _verändert
10C305, C308 - 1n - 50V - 10% - 2 - C_sm_0603 -  - C305, C308, C401 - 1n - 50V - 10% - 3 - C_sm_0603 -  - _verändert
11C309 - 10p - 25V - 10% - 1 - C_sm_0603 -  - C309 - 10p - 25V - 10% - 1 - C_sm_0603 -  - = Tabelle2 / Item # 4
12C310, C315, C323 - 100p - 25V - 10% - 3 - C_sm_0603 -  - C310, C315, C323 - 100p - 25V - 10% - 3 - C_sm_0603 -  - = Tabelle2 / Item # 5

verwendete Formeln
Zelle Formel Bereich N/A
P3=TEIL(ZELLE("filename";$A$1);FINDEN("]";ZELLE("filename";$A$1))+1;FINDEN("]";ZELLE("filename";$A$1))-FINDEN("[";ZELLE("filename";$A$1))-1)
L7:L12=C7 & " - " & D7 & " - " & E7 & " - " & F7 & " - " & G7 & " - " & H7 & " - " & I7 & " - " & J7
M8:M12=Tabelle2!L7
N7:N12=WENNFEHLER("= Tabelle2 / Item # "&VERGLEICH($L7;Tabelle2!$L$7:$L$1000;0);Tabelle1!$O$4)

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füllfarbe   Unterstrichen   Schrift- farbe Muster   Musterfarbe Typ Bereich
$N$7:$N$11801.Bed.: Formel ist=$N7=Tabelle1!$O$4255       2N7:N118
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich  
Wird angewendet auf   Nr. Bed. Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ   Bereich
$N$7:$N$11801.Bed.   Wahr          2N7:N118
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 19.07 einschl. 64 Bit

schauan

14.10.2015, 04:11
Hallo Ralf,

zu 1)
Da muss wohl vba ran, ansonsten bekommst Du einzelne Einträge auch nicht markiert. Ist wahrscheinlich auch etwas kompliziert. Daher erst mal eine Frage: Was soll alles als Änderung gewertet werden? Wenn z.B. eine "Datenspalte" entfällt oder dazukommt, was ist dann alles eine Änderung? Wenn z.B. in M8 C100 entfallen wäre, dann würde alles "nach links" rutschen und entsprechend wäre alles eine Änderung? Falls alle anderen Positionen in dem Fall nicht als Änderung gewertet werden sollen, könnte man ja nichts markieren, da C100 ja fehlt ...

zu 2) mit INDIREKT?

Rabe

14.10.2015, 11:19
Hi André,

(14.10.2015, 04:11)schauan schrieb: [ -> ]zu 1)
Da muss wohl vba ran, ansonsten bekommst Du einzelne Einträge auch nicht markiert. Ist wahrscheinlich auch etwas kompliziert. Daher erst mal eine Frage: Was soll alles als Änderung gewertet werden? Wenn z.B. eine "Datenspalte" entfällt oder dazukommt, was ist dann alles eine Änderung? Wenn z.B. in M8 C100 entfallen wäre, dann würde alles "nach links" rutschen und entsprechend wäre alles eine Änderung? Falls alle anderen Positionen in dem Fall nicht als Änderung gewertet werden sollen, könnte man ja nichts markieren, da C100 ja fehlt ...  

zu 2) mit INDIREKT?
zu 2) ja, da muß ich mich wohl mal tief in INDIREKT reindenken.

zu 1) stimmt.
Nun ist uns eine andere Lösung/veränderung der Aufgabenstellung eingefallen: wenn das eh per VBA gemacht werden muß, dann kann man es auch gleich anders machen:
  • Aufspaltung der Zellen mit den Bauteil-Bezeichnungen (Spalte C) in einzelne Zeilen mit Kopie der restlichen Spalten-Inhalte in die betreffenden Zellen (A-J ohne C)
  • wieder Vergleich der einzelnen Zeilen miteinander

Somit fehlt mir die Vorbereitung des Vergleichs:
Aufsplittung der Datenspalte C in einzelne Zeilen und Kopie der korrespondierenden Spalten.
Es müßten also zwischen Zeile 20 und 21 noch zusätzliche 42 Zeilen eingefügt werden, dort in Spalte C die einzelnen C104 und ff eingefügt werden und dann die Werte aus Spalte B und D-J reinkopiert werden. Gewissermaßen Kopie der Zeile 20, diese 42 mal unterhalb von 20 einfügen und in Spalte C von vorne beginnend die Werte immer ab dem nächsten Komma löschen.
Zwischen jetzt 21 und 22 werden es dann 7 zusätzliche Zeilen, ...

Trennzeichen ist immer ein Komma.
Wenn nur eine Bezeichnung in C steht, muß ja nichts gemacht werden.
Die Gesamtzahl der Zeilen pro Block stehen immer in Spalte G = Quantity

Also gewissermaßen das Erstellen einer Einzelstückliste aus der zusammengefassten.
Das benötigte Makro übersteigt meinen VBA-Horizont.

Hier die Ursprungszeilen, das Vergleichen bekomme ich ja jetzt schon hin.

Tabelle1
ABCDEFGHIJ
6Item #CommentDesignatorValueVoltageToleranceQuantityFootprintManufacturerManufacturer Part Number
2014Capacitor SMDC104, C105, C312, C313, C316, C318, C319, C327, C328, C331, C332, C340_A, C340_B, C344, C348, C600, C601, C604, C606, C608, C609, C610, C611, C612, C613, C614, C615, C616, C617, C618, C619, C621, C624, C625, C626, C627, C629, C702, C703, C704, C706, C711, C718100nF50V10%43C_sm_0603
2115Capacitor SMDC300, C301, C302, C305, C403, C404, C417, C418220nF1kV10%8C_sm_2220MustermannC2220X224K102TX
2216Capacitor SMDC303, C304n.b.2C_sm_2220MustermannC2220X224K102TX
2317ConnectorX100PCV 6-16/ 4-G-10,161PCV 6-16/ 4-G-10,16Phoenix Contact'1804700

verwendete Formeln
Zelle Formel Bereich N/A
A20:A23=ZEILE(A20)-ZEILE($A$6)
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 19.08 einschl. 64 Bit

schauan

14.10.2015, 17:48
Hallo Ralf,

hier mal ein Ansatz für die Datentrennung. Du musst aber beachten, dass hier nur in Spalte C 42 leere Zellen eingefügt werden. Wenn Du es auf der ganzen Breite willst, dann müsstest Du den Bereich entsprechend "verbreitern".

Code:
Sub WerteTrennen()
'Variablendeklaration - Variant
Dim arrWerete
'42 Zellen nach C20 einfuegen
Range("C21:C43").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Werte aus C20 aufnehmen
arrwerte = Split(Range("C20").Value, ",")
'Werte am Komma trennen und untereinander einfuegen
Range("C21").Resize(UBound(arrwerte) + 1) = WorksheetFunction.Transpose(arrwerte)
End Sub

Rabe

16.10.2015, 14:25
Hi André,

(14.10.2015, 17:48)schauan schrieb: [ -> ]hier mal ein Ansatz für die Datentrennung. Du musst aber beachten, dass hier nur in Spalte C 42 leere Zellen eingefügt werden. Wenn Du es auf der ganzen Breite willst, dann müsstest Du den Bereich entsprechend "verbreitern".
Danke.
Ich habe das Makro nun mal verallgemeinert.

Wie bekomme ich die Leerzeichen in Spalte C weg (TRIM hat noch nicht geklappt) und wie bekomme ich es hin, daß das Makro durchläuft, denn loLetzte am Anfang ist ja kleiner, als die letzte Zeilenzahl nach dem (ersten) Einfügen. Momentan (siehe Beispiel unten) muß ich drei mal starten:
Code:
Sub WerteTrennen()
   'Variablendeklaration - Variant
   Dim arrWerte
   Dim intZeile As Integer
   Dim intEinfüg As Integer
   Dim loLetzte As Long
   
   loLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)   'letzte belegte Zeile in Spalte A (1)
   For intZeile = 7 To loLetzte            'zu prüfende Zeile
       intBauteile = Range("G" & intZeile) 'Anzahl Bauteile
       intEinfüg = intBauteile - 1         'Anzahl einzufügende Zeilen
       
       If intEinfüg > 0 Then
           'Zeilen einfuegen
           Range("A" & intZeile + 1 & ":O" & intZeile + intEinfüg).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
           
           'Inhalt runterkopieren
           Range("A" & intZeile & ":o" & intZeile).Copy Range("A" & intZeile + 1 & ":o" & intZeile + intEinfüg)
           'Zahl der Bauteile auf 1 setzen
           Range("G" & intZeile & ":G" & intZeile + intEinfüg).Value = 1
           
           'Werte aus C aufnehmen
           arrWerte = Split(Range("C" & intZeile).Value, ",")
           'Werte am Komma trennen und untereinander einfuegen
           Range("C" & intZeile).Resize(UBound(arrWerte) + 1) = WorksheetFunction.Transpose(arrWerte)
           intZeile = intZeile + intEinfüg
       End If
   loLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)   'letzte belegte Zeile in Spalte A (1)
   Next intZeile
   
   'Zeilenhöhe anpassen
   loLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)   'letzte belegte Zeile in Spalte A (1)
   Rows("7:" & loLetzte).AutoFit
End Sub

zum Testen hier nochmal ein Ausschnitt:

Tabelle1
ABCDEFGHIJ
6Item #CommentDesignatorValueVoltageTolerance1FootprintManufacturerManufacturer Part Number
71Capacitor SMDC300, C301, C302, C305, C403, C404, C417, C418220nF1kV10%8C_sm_2220HolystoneC2220X224K102TX
82Capacitor SMDC303, C304n.b.2C_sm_2220HolystoneC2220X224K102TX
93Capacitor SMDC306, C709220nF25V10%2C_sm_0805

verwendete Formeln
Zelle Formel Bereich N/A
A7:A9=WENN(B7<>"";ZEILE(A7)-ZEILE($A$6);"")
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 19.08 einschl. 64 Bit

Kuwer

16.10.2015, 15:23
Hallo Ralf,

die Schleife sollte rückwärts laufen.
Also statt
  For intZeile = 7 To loLetzte            'zu prüfende Zeile
dann
  For intZeile = loLetzte To 7 Step -1            'zu prüfende Zeile

Gruß Uwe

Rabe

18.10.2015, 18:10
Hi Uwe,

(16.10.2015, 15:23)Kuwer schrieb: [ -> ]die Schleife sollte rückwärts laufen.

danke, hat super funktioniert.

Kannst Du mir jetzt noch sagen, wie ich das TRIM für die aufgesplitteten Zellen in Spalte C anwende?

Steffl

18.10.2015, 18:14
Hallo Ralf,

vielleicht so?

Code:
arrWerte = Split(Range("C" & intZeile).Value, ", ")

Rabe

18.10.2015, 19:30
Hi Stefan,

(18.10.2015, 18:14)Steffl schrieb: [ -> ]vielleicht so?

leider sind in der Originalliste nicht überall Leerzeichen zwischen "," und Bezeichnung.

Kuwer

18.10.2015, 20:18
Hi Ralf,

ändere den Schluss so ab:

Code:
  'Zeilenhöhe anpassen
  loLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)   'letzte belegte Zeile in Spalte A (1)
  Range("C7:C" & loLetzte).TextToColumns Destination:=Range("C7"), DataType:=xlFixedWidth
  Rows("7:" & loLetzte).AutoFit
End Sub

Gruß Uwe
Seiten: 1 2
Clever-Excel-Forum > Office-Anwendungen > Excel > Tabellenvergleich: was wurde geändert?