Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1612to1616
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

Werte aus einer Tabelle in andere Tabelle

Werte aus einer Tabelle in andere Tabelle
12.03.2018 16:01:48
Matthias
Hallo Zusammen,
ich glaube, ich habe ein einfaches Problem zu lösen. Aber leider komm ich nicht auf die Lösung.
https://www.herber.de/bbs/user/120366.xlsx
Ich habe ein Tabellenblatt A:
- in Zelle A1 steht "Code"
- Zeile 1, ab Spalte B1: Elemente aus dem Periodensystem
- Spalte A, ab Zeile A2: Alle Materialien, für dich ich die einzelnen Werte eintragen muss
Jetzt habe ich ein anderes Tabellenblatt B:
- in Zelle A1 steht "Code"
- Spalte A: Alle Materialen (wie in Tabellenblatt A, nur teilweise auf zwei Teilen verteilt und nicht in gleicher Zeile
- die Materialen sind auf Blöcke aufgeteilt, so dass nur die Elemente als Überschrift dastehen, welche auf in dem Material vorkommen (das ist mein Problem, welches ich nicht gelöst bekomme)
Ich möchte die Werte aus Tabellenblatt B in Tabellenblatt A in die richtige Zelle bringen. Komme aber mit meinen Formelkenntnissen leider nicht weiter.
In der Beispieldatei habe ich die Gelb markierten Materialien aus Tabellenblatt B händisch in Tabellenblatt A eingefügt und immer in die richtige Spalte eingefügt.
Wünschenswert wäre noch eine farbliche Markierung, wenn ein Wert nicht gefunden wurde, so dass ich manuell überprüfen kann, ob der Wert vorhanden ist.
Wäre toll, wenn mir jemand dabei helfen könnte. Mir raucht irgendwie der Kopf...
VG Matze

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
12.03.2018 16:24:09
...
Hallo Matze,
... Deine Datenstruktur der Tabelle ist zumindest nicht optimal.
- Können weitere Materialdopplungen vorkommen?
- Wie viele Datensätze sind denn max auszuwerten?
- Die "Überschriften" beginnen immer mit "CTIF" oder "Analyte"?
- Die Material-Namen in Tabelle A stehen fest oder sollen normalerweise auch ermittelt werden?
Gruß Werner
.. , - ...
AW: nachgefragt ...
12.03.2018 16:39:57
Matthias
Hi Werner,
ja. Tabellenblatt B ist beispielhaft, wie ich sie bekomme
- Ja, Materialdopplungen treten unregelmäßig und öfter auf
- es sind ja ca. 30 Blätter (wenn ich nicht noch mehr bekomme) welche so zusammengefasst werden sollen pro Blatt sind es mit Leerzeilen ca. 500-1000 Zeilen
- die Überschriften können eigentlich vernachlässigt werden, da im Tabellenblatt A alle direkt untereinaderstehen sollen ohne dieser Überschriften. Nur "Code" steht im Tabellenblatt A ganz oben, bzw. die ganzen Elemente.
- wenn es geht, sollten die Materialnamen aus Tabellenblatt ermittelt werden. Wobei ich glaube, dass das zu kompliziert wäre. Weil nur die nicht fettgedruckten (in diesem Beispiel) Materialnamen ohne den Überschriften zu nehmen wäre und bei doppelten Namen der Name nur einmal genommen werden sollte
Grüße,
Matze
Anzeige
AW: ich stell den thread offen, weil ...
12.03.2018 16:51:52
...
Hallo Matze,
... bei derartigen vielen Tabellenblättern wohl nur eine VBA-Lösung sinnvoll sein dürfte. Aus VBA halte ich mich heraus. Wenn es nur ein Tabellenblatt wäre, könnte man es auch für bis 1000 Datensätze mit Formeln lösen.
Gruß Werner
.. , - ...
AW: ich stell den thread offen, weil ...
12.03.2018 16:55:38
Matthias
Könntest du mir sagen, wie es mit Formeln gehen würde?
Vll kann ich sie mir dann selber anpassen.
Grüße,
Matze
AW: sagen ;-) kann ich es nicht ...
12.03.2018 17:26:31
...
Hallo Matze,
... aber ich kann Dir aufzeigen, wie man die Formel für die Spalte A schreiben könnte.
In A2:
=WENNFEHLER(INDEX('Tabellenblatt B'!A:A;AGGREGAT(15;6;ZEILE(A$2:A$999)/
(ZÄHLENWENN(A$1:A1;'Tabellenblatt B'!A$2:A$999)=0)/(RECHTS('Tabellenblatt B'!A$2:A$999;6)"Series")/
(LINKS('Tabellenblatt B'!A$2:A$999;4)"Anal")/('Tabellenblatt B'!A$2:A$999"");1));"")

und diese nach unten kopieren (könnte schon etwas den PC ins schwitzen bringen).
Für die Ermittlung der dazugehörigen Elemente-Werte, müsste Du zumindest noch mitteilen:
- ob diese in den Datentabellen in der gleichen Reihenfolge wie in Zeile 1 angeordnet sind,
- mit der Ausnahme, dass nur für das Material welches doppelt benannt ist, die geleich wie in Zeile 12 hat.
Gruß Werner
.. , - ...
Anzeige
AW: sagen ;-) kann ich es nicht ...
12.03.2018 18:05:25
Matthias

Ähm, das aufzeigen reicht mir vollkommen :D
Die funktioniert schon einmal perfekt :) Danke dir ;)
Ich wäre da nie drauf gekommen, aber ich glaube, ich kann die mir so hinbasteln, dass die auch  _
bei anderen Blättern geht.
- Ja, die Reihenfolge ist die gleiche
- die "Doppelung" ist nur bei Materialien, welche so "viele" Elemente haben, dass sie es auf  _
zwei Zeilen aufgeteilt haben => also ist es jeweils eigentlich nur ein Material, welches auf zwei Zeilen aufgeteilt ist.
- über jedem Block steht immer "Code" und dann die Elemente
Grüße und vielen Dank schon mal,
Matze

Anzeige
AW: dazu noch 1ne Matrixfunktion(alität)sformel...
12.03.2018 18:34:12
...
Hallo Matze,
... in B2:
=WENNFEHLER(INDEX('Tabellenblatt B'!$A:$Z;VERGLEICH($A2;'Tabellenblatt B'!$A:$A;0);
VERGLEICH(B$1;'Tabellenblatt B'!$1:$1;0));WENNFEHLER(INDEX('Tabellenblatt B'!$A:$Z;
VERWEIS(9;1/('Tabellenblatt B'!$A$1:$A$999=$A2);ZEILE(A$1:A$999));
VERGLEICH(B$1;'Tabellenblatt B'!$12:$12;0));""))

und diese nach rechts und unten kopieren.
Gruß Werner
.. , - ...
AW: dazu noch 1ne Matrixfunktion(alität)sformel...
12.03.2018 18:56:32
Matthias
Super danke dir :)
Kannst du mir noch erklären, was dafür sorgt, dass bei "doppelten" Materialien bei beiden die Werte genommen werden?
Grüße,
Matze
AW: in Kombination mit WENNFEHLER() ...
12.03.2018 19:21:13
...
Hallo Matze,
... findet VERGLEICH() immer den ersten/obersten Wert und VERWEIS() immer den letzten/hier zweiten/untersten Wert und ermittelt dafür die dazugehörige Zeilennummer, welche dem INDEX() als entsprechendes Argument übergeben wird.
Vergessen hatte ich noch mitzuteilen, dass die Formelzelle das benutzerdefinierten Zahlenformat: Standard;; zugewiesen bekommen sollte, damit vorhandene Leerzellen nicht als 0-Wert angezeigt wird.
Gruß Werner
.. , - ...
Anzeige
AW: in Kombination mit WENNFEHLER() ...
12.03.2018 20:27:47
Matthias
Hi Werner,
https://www.herber.de/bbs/user/120373.xlsx
ich habe gerade mal nen "manuellen" Vergleich gemacht.
- die Zellen welche gleich sind habe ich mit bedingter Formatierung rot gefärbt
Irgendwie werden bei einigen Materialien Werte für Elemente gefunden, bei denen gar keine vorhanden sind?
Grüße,
Matze
AW: dazu noch 1ne Matrixfunktion(alität)sformel...
13.03.2018 14:13:01
Matthias
Hi Werner,
mir ist noch was aufgefallen, die Reihenfolge der Elemente ist im Tabellenblatt B nicht immer die gleiche.
Jedoch hat jeder "Block" von Materialien immer "Code" als Überschrift in deren Zeile auch die Elemente stehen. Kann man das irgendwie "einbauen"? Soweit ich die Formeln verstehe, beziehen die sich immer auf Zeile 1 und 12, oder?
Anzeige
AW: gleiche Reihnfolge/Anordn. war Bedingung ...
13.03.2018 17:14:00
...
Hallo Matze,
... darauf hatte ich in meinen gestrigen Beitrag von 16:55:38 hingewiesen.
Wenn diese nicht gewährleistet ist bzw. werden kann, wäre zwar eine Formelauswertung auch möglich aber der Aufwand dafür wird erheblich größer und für bis zu 1000 Datensätze auch für die Auswertungsgeschwindigkeit schon mehr als grenzwertig.
Gruß Werner
.. , - ...
AW: gleiche Reihnfolge/Anordn. war Bedingung ...
13.03.2018 17:23:18
Matthias
Hi Werner,
sorry, dann habe ich dich falsch verstanden.
Dann wäre das Thema weiterhin offen.
Vielleicht kann mir jemand mit einer VBA Lösung weiterhelfen? :)
Grüße Matze
AW: nun doch VBA-Lösung gesucht, thread offen owT
13.03.2018 18:50:19
...
Gruß Werner
.. , - ...
VBA-Lösung
19.03.2018 00:06:58
KlausF
Hallo Matze,
kannst ja mal probieren:
Sub DatenAufteilen()
'Sheet duplizieren
ThisWorkbook.Worksheets("Tabellenblatt B").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
ActiveSheet.Name = "TEMP"
Dim rng As Range
Dim searchCode As String
Dim searchCol As Integer
Dim lastRow As Long, i As Long, a As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
'Verbundzellen aufheben
ActiveSheet.UsedRange.MergeCells = False
'leere Zeilen loeschen
On Error Resume Next
Range("A1", Range("A" & lastRow).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
'Zeilen mit "Analyte" und "Series" loeschen
For i = lastRow To 1 Step -1
If InStr(Range("A" & i), "Analyte") > 0 Or InStr(Range("A" & i), "Series") > 0 Then
Rows(i).EntireRow.Delete
End If
Next i
'Leere Zeile einsetzen + 'Kopf kopieren
Range("A1").EntireRow.Insert
Worksheets("Tabellenblatt A").Range("B1:AC1").Copy Range("AA1")
'Code-Bloecke bestimmen
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For i = lastRow To 2 Step -1
If InStr(Range("A" & i), "Code") > 0 Then
'Spalten aufteilen
For a = 2 To Range("AA" & i).End(xlToLeft).Column
searchCode = Cells(i, a)
searchCol = WorksheetFunction.Match(searchCode, Rows(1), 0)
If searchCol > 0 Then
Range(Cells(i, a), Cells(lastRow + 1, a)).Copy Cells(i, searchCol)
End If
Next a
lastRow = i - 1
End If
Next i
On Error GoTo 0
'Zeilen mit "Code" loeschen
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastRow To 1 Step -1
If InStr(Range("A" & i), "Code") > 0 Then
Rows(i).EntireRow.Delete
End If
Next i
'Spalten loeschen
Columns("B:Z").Delete
'Sortieren
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:AC" & lastRow).Sort _
Key1:=ActiveSheet.Range("A2"), Order1:=xlAscending, Header:=xlNo
'Daten verdichten
For i = lastRow To 2 Step -1
If Range("A" & i) = Range("A" & i - 1) Then
Range("A" & i & ":AC" & i).Copy
Range("A" & i - 1).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True,  _
Transpose:=False
Rows(i).EntireRow.Delete
End If
Next i
'Kopieren nach Tabellenblatt A und leere Zellen = Gelb
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:AC" & lastRow).Copy
With Worksheets("Tabellenblatt A")
.Select
.Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Columns("A:AC").AutoFit
.Range("A1").Select
For Each rng In .Range("A2:AC" & lastRow)
If IsEmpty(rng) Then rng.Interior.ColorIndex = 6
Next rng
End With
Application.CutCopyMode = False
Application.DisplayAlerts = False
Worksheets("TEMP").Delete
Application.DisplayAlerts = True
Set rng = Nothing
End Sub
https://www.herber.de/bbs/user/120511.xls
Passt es?
Gruß
Klaus
PS. Achte auf die Tabellennamen. Du hast "Tabelleblatt A" statt "Tabellenblatt A" geschrieben
Ist im Code korrigiert
Anzeige
AW: VBA-Lösung
19.03.2018 05:33:27
Matthias
Hi Klaus,
vielen vielen Dank :)
Auch für den Hinweis mit Tabellenblatt A.
Für die ersten paar Blätter hat es schon mal super funktioniert. Jetzt muss ich nur die anderen Daten versuchen soweit aufzubereiten, dass sie dieselbe Struktur haben.
Vielleicht noch eine Frage:
Wenn ich mit F8 die einzelnen Schritte durchlaufe, stoppt das Makro bei dem Block:
'Kopieren nach Tabellenblatt A und leere Zellen = Gelb
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:AC" & lastRow).Copy
With Worksheets("Tabellenblatt A")
.Select
.Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Columns("A:AC").AutoFit
.Range("A1").Select
For Each rng In .Range("A2:AC" & lastRow)
If IsEmpty(rng) Then rng.Interior.ColorIndex = 6
Next rng
End With
und markiert den fetten Teil mit dem Fehler:
"die pastespecial-methode des range objektes konnte nicht ausgeführt werden"
Also, ich verstehe jetzt das meiste was das Makro macht, aber wieso der Fehler auftaucht, wenn ich die einzelnen Schritte durchlaufe, jedoch nicht auftaucht, wenn ich das Makro so durchlaufen lasse, verstehe ich nicht.
Vielen vielen Dank nochmal Klaus! Das Makro ist super.
Dass hier einem so gut geholfen wird, ist echt toll und nicht selbstverständlich :)
Auch an Werner, der mir neue Formeln gezeigt hat, die ich bestimmt mal woanders einbauen kann :)
Grüße,
Matze
Anzeige
AW: VBA-Lösung
19.03.2018 08:04:34
KlausF
Hallo Matthias,
danke für das Feedback. Freut mich, dass es läuft.
Keine Ahnung, warum er das anmeckert. Ich habe den Code unter einem uralten Excel
(Excel 2000) erstellt. Möglicherweise hat sich die Syntax von Copy/Paste zu Excel 2013 verändert.
Dann dürfte das Makro allerdings überhaupt nicht durchlaufen. Eine der vielen Ungereimtheiten
von Billiboys Excel ...
Was mich allerdings viel mehr beschäftigt: Ich hatte meine Antwort an Dich abgeschickt, aber dann
wurde der Thread im Forum nicht mehr angezeigt. Auch im Archiv war meine Antwort an Dich nicht
sichtbar. Ich habe deshalb einen neuen Thread aufgemacht, in der Hoffnung, dass Du den liest:
https://www.herber.de/forum/messages/1614950.html
Jetzt bin ich allerdings echt erstaunt, dass Du doch auf die erste Antwort antworten konntest. Wie gesagt,
bei mir wird der gesamte alte Beitrag im Forum nicht mehr angezeigt und ist im Archiv auch nur bis zu
Werners letzter Antwort an Dich zu sehen. Vermutlich werde ich auch diese Antwort an Dich nicht mehr
wiederfinden können weil sie im Nirvana gespeichert wird. Vodoo!
Falls Du noch Fragen hast, dann antworte vorsichtshalber in dem neu von mir aufgemachten Thread.
Gruß
Klaus
Anzeige

309 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige