[Excel 2010] Feldinhalte nach Abhängigkeit ersetzen

[Frezl]

Hallo allerseits,

ich weiß leider nicht so genau, nach welchem Begriff ich eigentlich suche - daher auch die etwas schammige Formulierung im Titel - deshalb beschreibe ich einfach mal mein Problem:

Ich habe eine Tabelle von Datensätzen. Jeder Datensatz repräsentiert ein Bauteil, das durch eine eindeutige Nomenklatur benannt ist. Manche Bauteile sind von anderen Abhängig, dann ist die Nomenklatur des "Eltern"-Bauteils in einer extra Spalte gespeichert:

|nomenclature|dieses|jenes|reference_component|
|Capacitor Al-Elko abc (100mg)|a|b|null|
|Capacitor Al-Elko xyz (300mg)|c|d|Capacitor Al-Elko abc (100mg)|

Ich möchte die Tabelle so umbauen, dass jedes Bauteil eine ID bekommt und die Referenzierung über die ID erfolgt:
|id|nomenclature|dieses|jenes|reference_component|
|1|Capacitor Al-Elko abc (100mg)|a|b|null|
|2|Capacitor Al-Elko xyz (300mg)|c|d|1|

Die Spalte mit den IDs einzubauen ist in Excel ja kein Problem, aber wie kann ich automatisch die Referenzen ersetzen?

Freue mich über hilfreiche Tipps!

Viele Grüße,
Frezl

 

[Yaslaw]

mit einem Lookup

Das folgende willst du:

  || A  | B     | C           | D
========================================
1 || ID | CODE1 | CODE_PARENT | ID_PARENT
2 || 1  | A     |             |
3 || 2  | B     | A           | 1
4 || 3  | C     | D           | 4
5 || 4  | D     |             | 
6 || 5  | E     | A           | 1

Die Formel für D2 ist dann

=IFERROR(LOOKUP(C2;$B$2:$B$6;$A$2:$A$6);"")

Diese dann über die restlichen Zeilen kopieren.

 

[Frezl]

Hey Yaslaw,

vielen Dank für deine Antwort. Ein Kollege hatte "irgendwas mit VERWEIS" im Hinterkopf. Zusammen mit deinem Tipp hab ich's dann hingebogen. Im Anhang ein Beispiel.

Was man noch dazu sagen sollte: bei VERWEIS (bzw. LOOKUP) muss die Tabelle vorher nach der Spalte mit dem Suchvektor (im Bild grün markiert) sortiert sein. K. a. warum, aber wenn es nicht so ist, kommt Müll raus.

Wie man sieht, hatte ich die IDs vergeben, bevor ich die Tabelle sortiert habe. Sieht nicht schön aus, aber es funktioniert. Werd jetzt mal versuchen, das auf meine eigentliche Tabelle anzuwenden.

Vielen Dank und viele Grüße,
Fred

 

[Frezl]

Sodele, hab's jetzt endlich geschafft, die Operation auch auf meine produktive Tabelle anzuwenden. War gar nicht so einfach. Für diejenigen, die das gleiche vor haben, folgende Tipps:

1. Die Zellen, die verglichen werden sollen, dürfen keine Formeln enthalten! Also sicherheitshalber vorher die ganze Spalte kopieren und nur die Werte wieder einfügen.

2. Ich habe alle Strings vorher etwas überarbeitet, bevor ich sie verglichen habe. Dazu habe ich folgende Funktion verwendet:

=KLEIN(SÄUBERN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(E2;"""";"");"-";"");"&";"");".";"");"/";"");",";"");")";"");"(";"");" ";"")))

Sie entfernt Leerzeichen und eine Auswahl an Sonderzeichen. Außerdem macht es den kompletten String lower case.

Das hilft, wenn die Daten von Menschenhand eingegeben wurden und daher evtl. kleine Fehler (, statt . oder ein Leerzeichen zu viel) enthalten. Evtl. arbeitet dann auch die Vergleichsfunktion zuverlässiger, aber das habe ich nicht getestet.

Meine Frage an die Excel-Cracks: Wie kann ich solche Ersetzungen einfacher vornehmen, ohne VB verwenden zu müssen? Habe versucht, als Suchkriterium von WECHSELN eine Tabellenspalte zu verwenden, aber das funktioniert leider nicht :-(

3. Wie oben bereits erwähnt, muss die Spalte mit dem Suchvektor alphabetisch sortiert sein. Natürlich nach den ganzen Überarbeitungen, also direkt, bevor VERWEIS angewandt wird.

Viele Grüße,
Frezl

 

[Yaslaw]

Ohne VBA ist mir nix bekannt, mit VBA ists aber einfach

Die folgende Funktion in ein neues Modul kopieren

Public Function substitutePlus( _
    ByRef text As Range, _
    ByRef oldText As Range, _
    Optional ByVal NewText As String = vbNullString _
) As String
    Dim c       As Range

    substitutePlus = text.Value2
    For Each c In oldText.Cells
        substitutePlus = Replace(substitutePlus, c.Value2, NewText)
    Next c
End Function

Und schon kannst du die Formel so schreiben

=substitutePlus(A1;$G$1:$G$10)

Wobei A1 der originaltext ist,
$G$1:$G$10 ein Range mit den zu ersetzenden Zeichen. Extra mit $, damit beim Kopieren der Formel immer auf densleben Range gezeigt wird.
Der Dritte Parameter kann man setzen, wenn man etwas anderes als ein "" haben will