Registriert seit: 10.04.2014
Version(en): 2016 + 365
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 | L | M | N | O | P | 3 | | | | Register: | Tabelle1 | 4 | | | | _verändert | | 5 | Hilfsspalte, kann ausgeblendet werden! | Hilfsspalte, kann ausgeblendet werden! | | | | 6 | Tabelle1 | Tabelle2 | Filterspalte | | | 7 | B600 - BR-1225 - - - 1 - - Panasonic - BR-1225 | | _verändert | | | 8 | C100, 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 | | | 9 | C302, C304 - 10n - 50V - 10% - 2 - C_sm_0603 - - | C302, C304, C406 - 10n - 50V - 10% - 3 - C_sm_0603 - - | _verändert | | | 10 | C305, C308 - 1n - 50V - 10% - 2 - C_sm_0603 - - | C305, C308, C401 - 1n - 50V - 10% - 3 - C_sm_0603 - - | _verändert | | | 11 | C309 - 10p - 25V - 10% - 1 - C_sm_0603 - - | C309 - 10p - 25V - 10% - 1 - C_sm_0603 - - | = Tabelle2 / Item # 4 | | | 12 | C310, 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$118 | 01.Bed.: Formel ist | | =$N7=Tabelle1!$O$4 | | 255 | | | | | 2 | N7: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$118 | 01.Bed. | | Wahr | | | | | 2 | N7: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 |
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
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?
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
Registriert seit: 10.04.2014
Version(en): 2016 + 365
14.10.2015, 11:19
(Dieser Beitrag wurde zuletzt bearbeitet: 14.10.2015, 11:23 von Rabe.)
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 | A | B | C | D | E | F | G | H | I | J | 6 | Item # | Comment | Designator | Value | Voltage | Tolerance | Quantity | Footprint | Manufacturer | Manufacturer Part Number | 20 | 14 | Capacitor SMD | C104, 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, C718 | 100nF | 50V | 10% | 43 | C_sm_0603 | | | 21 | 15 | Capacitor SMD | C300, C301, C302, C305, C403, C404, C417, C418 | 220nF | 1kV | 10% | 8 | C_sm_2220 | Mustermann | C2220X224K102TX | 22 | 16 | Capacitor SMD | C303, C304 | n.b. | | | 2 | C_sm_2220 | Mustermann | C2220X224K102TX | 23 | 17 | Connector | X100 | PCV 6-16/ 4-G-10,16 | | | 1 | PCV 6-16/ 4-G-10,16 | Phoenix 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 |
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
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
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
Registriert seit: 10.04.2014
Version(en): 2016 + 365
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 | A | B | C | D | E | F | G | H | I | J | 6 | Item # | Comment | Designator | Value | Voltage | Tolerance | 1 | Footprint | Manufacturer | Manufacturer Part Number | 7 | 1 | Capacitor SMD | C300, C301, C302, C305, C403, C404, C417, C418 | 220nF | 1kV | 10% | 8 | C_sm_2220 | Holystone | C2220X224K102TX | 8 | 2 | Capacitor SMD | C303, C304 | n.b. | | | 2 | C_sm_2220 | Holystone | C2220X224K102TX | 9 | 3 | Capacitor SMD | C306, C709 | 220nF | 25V | 10% | 2 | C_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 |
Registriert seit: 17.04.2014
Version(en): MS Office 365(32)
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
Registriert seit: 10.04.2014
Version(en): 2016 + 365
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?
Registriert seit: 11.04.2014
Version(en): Office 2007
Hallo Ralf,
vielleicht so?
Code: arrWerte = Split(Range("C" & intZeile).Value, ", ")
Gruß Stefan
Win 10 / Office 2016
Registriert seit: 10.04.2014
Version(en): 2016 + 365
Hi Stefan,
(18.10.2015, 18:14)Steffl schrieb: vielleicht so?
leider sind in der Originalliste nicht überall Leerzeichen zwischen "," und Bezeichnung.
Registriert seit: 17.04.2014
Version(en): MS Office 365(32)
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
|