Hallo Zusammen,
benötige dringend eure Hilfe.
In Spalte A werden mir PLZ angezeigt. In Spalte B werden mir die gleichen PLZ aus Spalte A, jedoch mit der Zusatzinfo "Stadt & Straße" angezeigt.
Nun möchte ich in Spalte C folgendes angezeigt bekommen:
Suche in Spalte B den Wert (PLZ) aus Spalte A und füge den Inhalte aus Spalte B in Spalte C ein.
Ich benötige eine Formel da ich auch gerne dies lernen möchte.
Im Anhang habe ich eine Excel Liste beigefügt.
Würde mich auf Hilfe sehr freuen.
Grüße aus dem sonnigen Kölle :43:
Hallo
Seufz...
Der Aufbau ist falsch. Und warum das eine Hilfsspalte sein soll erschließt sich mir nicht.
In Spalte A ist ein Mischmasch aus Text und Zahl. Schlecht für den Datenabgleich.
In Spalte E habe ich die Blitzvorschau verwendet mit der Beispielvorgabe 10179 in E2.
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E |
| 1 | PLZ | Adresse | | Adresse | PLZ |
| 2 | 52064 | Franzstraße 6 52064 Aachen | | Grunerstraße 20 10179 Berlin | 10179 |
| 3 | 73431 | Carl-Zeiss-Straße 96 73431 Aalen | | Tempelhofer Damm 227 12099 Berlin | 12099 |
| 4 | 72458 | Sonnenstraße 30 72458 Albstadt | | Pankstraße 32-39 13357 Berlin-Wedding | 13357 |
| Zelle | Formel |
| B2 | =INDEX($D$2:$D$268;VERGLEICH(A2;$E$2:$E$268;0)) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo,
mit der Blitzvorschau komme ich im Moment nicht zu Recht, aber folgende Formel in C2 könnte auch zum Ziel führen:
=SVERWEIS(0+TEIL("ζ"&B2&"ζ";1+VERGLEICH(26;MMULT(N(ISTFEHL(0+TEIL(TEIL("ζ"&B2&"ζ";ZEILE(INDEX(B:B;1):INDEX(B:B;LÄNGE(B2)-4));7);{1.2.3.4.5.6.7};1)));{13;1;1;1;1;1;13});0);5);A:B;2;0)
Quelle:
http://excelxor.com/2015/04/23/extractin...c-strings/
Nachtrag: Wenn in der Spalte B zweimal eine PLZ auftaucht, z.B. in B12, dann nimmt die Blitzvorschau die letzte PLZ und meine Formel die erste PLZ.
Und ja, ich bin der gleichen Meinung wie shift-del: Der Datenaufbau ist verbesserungswürdig.
(05.05.2018, 08:39)shift-del schrieb: [ -> ]Hallo
Seufz...
Der Aufbau ist falsch. Und warum das eine Hilfsspalte sein soll erschließt sich mir nicht.
In Spalte A ist ein Mischmasch aus Text und Zahl. Schlecht für den Datenabgleich.
In Spalte E habe ich die Blitzvorschau verwendet mit der Beispielvorgabe 10179 in E2.
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E |
| 1 | PLZ | Adresse | | Adresse | PLZ |
| 2 | 52064 | Franzstraße 6 52064 Aachen | | Grunerstraße 20 10179 Berlin | 10179 |
| 3 | 73431 | Carl-Zeiss-Straße 96 73431 Aalen | | Tempelhofer Damm 227 12099 Berlin | 12099 |
| 4 | 72458 | Sonnenstraße 30 72458 Albstadt | | Pankstraße 32-39 13357 Berlin-Wedding | 13357 |
| Zelle | Formel |
| B2 | =INDEX($D$2:$D$268;VERGLEICH(A2;$E$2:$E$268;0)) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo shift-del,
vielen Dank für deine Anwort. :)
Ich möchte folgendes:
Beispiel Zeile 1
In Spalte A2 wird die PLZ 52064 angezeigt. Jetzt möchte ich das diese PLZ in der kompleten Spalte B gesucht wird. Falls gefunden dann soll der gefundene Inhalt (in diesem Beispiel B25) in Spalte C eingefügt werden.
Grüße :)
Hallo,
dieser Lösungsvorschlag steht seit 7:09 im MS-Office-Forum, was gefällt dir daran nicht?
Tabelle1 | A | B | C |
| 1 | PLZ | Hilfsspalte | Ergebnis |
| 2 | 52064 | Grunerstraße 20 10179 Berlin | Franzstraße 6 52064 Aachen |
| 3 | 73431 | Tempelhofer Damm 227 12099 Berlin | Carl-Zeiss-Straße 96 73431 Aalen |
| 4 | 72458 | Pankstraße 32-39 13357 Berlin-Wedding | Sonnenstraße 30 72458 Albstadt |
| 5 | 55232 | Prerower Platz 1 13051 Berlin-Hohenschönhausen | Karl-Heinz-Kipp-Straße 23 55232 Alzey |
| 6 | 92224 | An den Freiheitswiesen 5 13597 Berlin-Spandau | Am Bergsteig 1 92224 Amberg |
| 7 | 91522 | Karl-Marx-Straße 66 12043 Berlin-Neukölln | Rothenburger Straße 15 91522 Ansbach |
| 8 | 63741 | Märkische Spitze 11 12681 Berlin-Biesdorf | Mainaschaffer Straße 115 63741 Aschaffenburg |
| 9 | 63739 | Am Borsigturm 2 13507 Berlin Tegel | Goldbacherstraße 2 63739 Aschaffenburg |
| 10 | 86154 | Johannisthaler Chaussee 309 12351 Berlin-Gropiusstadt | Schönbachstraße 192 86154 Augsburg |
| 11 | 86159 | Schnellerstraße 21 12439 Berlin-Schöneweide | Eichleitnerstraße 34 86159 Augsburg |
| Formeln der Tabelle |
| Zelle | Formel | | C2 | =SVERWEIS("*"&A2&"*";B:B;1;FALSCH) | | C3 | =SVERWEIS("*"&A3&"*";B:B;1;FALSCH) | | C4 | =SVERWEIS("*"&A4&"*";B:B;1;FALSCH) | | C5 | =SVERWEIS("*"&A5&"*";B:B;1;FALSCH) | | C6 | =SVERWEIS("*"&A6&"*";B:B;1;FALSCH) | | C7 | =SVERWEIS("*"&A7&"*";B:B;1;FALSCH) | | C8 | =SVERWEIS("*"&A8&"*";B:B;1;FALSCH) | | C9 | =SVERWEIS("*"&A9&"*";B:B;1;FALSCH) | | C10 | =SVERWEIS("*"&A10&"*";B:B;1;FALSCH) | | C11 | =SVERWEIS("*"&A11&"*";B:B;1;FALSCH) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
(05.05.2018, 09:02)Peter schrieb: [ -> ]Hallo,
mit der Blitzvorschau komme ich im Moment nicht zu Recht, aber folgende Formel in C2 könnte auch zum Ziel führen:
=SVERWEIS(0+TEIL("ζ"&B2&"ζ";1+VERGLEICH(26;MMULT(N(ISTFEHL(0+TEIL(TEIL("ζ"&B2&"ζ";ZEILE(INDEX(B:B;1):INDEX(B:B;LÄNGE(B2)-4));7);{1.2.3.4.5.6.7};1)));{13;1;1;1;1;1;13});0);5);A:B;2;0)
Quelle: http://excelxor.com/2015/04/23/extractin...c-strings/
Nachtrag: Wenn in der Spalte B zweimal eine PLZ auftaucht, z.B. in B12, dann nimmt die Blitzvorschau die letzte PLZ und meine Formel die erste PLZ.
Und ja, ich bin der gleichen Meinung wie shift-del: Der Datenaufbau ist verbesserungswürdig.
Hallo Peter,
vielen Dank für deine Antwort. Die Formel ist super.
Mir werden jetzt fast alle Werte in Spalte C richtig angezeigt. Die PLZ aus A15 (55543) ist in Spalte B38 vertreten. Jedoch wird mir bei Zelle C15 "#NV" angezeigt.
Ich bin wie folgt vorgegangen:
Deine Formel habe ich in Zelle C2 eingefügt. Dann Doppelklick auf C2 unten recht damit in anderen Zellen in Spalte C die Formel übernommen wird.
Ich finde jedoch nicht den Fehler warum in C15 nicht der richtige Wert angezeigt wird. Hast du eine Idee?
Ich habe hier nochmal die geänderte Excel Datei "Test1" beigefügt.
Grüße
(05.05.2018, 09:10)Klaus-Dieter schrieb: [ -> ]Hallo,
dieser Lösungsvorschlag steht seit 7:09 im MS-Office-Forum, was gefällt dir daran nicht?
Tabelle1
| A | B | C |
| 1 | PLZ | Hilfsspalte | Ergebnis |
| 2 | 52064 | Grunerstraße 20 10179 Berlin | Franzstraße 6 52064 Aachen |
| 3 | 73431 | Tempelhofer Damm 227 12099 Berlin | Carl-Zeiss-Straße 96 73431 Aalen |
| 4 | 72458 | Pankstraße 32-39 13357 Berlin-Wedding | Sonnenstraße 30 72458 Albstadt |
| 5 | 55232 | Prerower Platz 1 13051 Berlin-Hohenschönhausen | Karl-Heinz-Kipp-Straße 23 55232 Alzey |
| 6 | 92224 | An den Freiheitswiesen 5 13597 Berlin-Spandau | Am Bergsteig 1 92224 Amberg |
| 7 | 91522 | Karl-Marx-Straße 66 12043 Berlin-Neukölln | Rothenburger Straße 15 91522 Ansbach |
| 8 | 63741 | Märkische Spitze 11 12681 Berlin-Biesdorf | Mainaschaffer Straße 115 63741 Aschaffenburg |
| 9 | 63739 | Am Borsigturm 2 13507 Berlin Tegel | Goldbacherstraße 2 63739 Aschaffenburg |
| 10 | 86154 | Johannisthaler Chaussee 309 12351 Berlin-Gropiusstadt | Schönbachstraße 192 86154 Augsburg |
| 11 | 86159 | Schnellerstraße 21 12439 Berlin-Schöneweide | Eichleitnerstraße 34 86159 Augsburg |
| Formeln der Tabelle |
| Zelle | Formel | | C2 | =SVERWEIS("*"&A2&"*";B:B;1;FALSCH) | | C3 | =SVERWEIS("*"&A3&"*";B:B;1;FALSCH) | | C4 | =SVERWEIS("*"&A4&"*";B:B;1;FALSCH) | | C5 | =SVERWEIS("*"&A5&"*";B:B;1;FALSCH) | | C6 | =SVERWEIS("*"&A6&"*";B:B;1;FALSCH) | | C7 | =SVERWEIS("*"&A7&"*";B:B;1;FALSCH) | | C8 | =SVERWEIS("*"&A8&"*";B:B;1;FALSCH) | | C9 | =SVERWEIS("*"&A9&"*";B:B;1;FALSCH) | | C10 | =SVERWEIS("*"&A10&"*";B:B;1;FALSCH) | | C11 | =SVERWEIS("*"&A11&"*";B:B;1;FALSCH) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Hallo Klaus-Dieter,
sorry übersehen...vielen Dank für deine Hilfe :)
Grüße
Hallo,
bitte verzichte auf das dämliche Zitieren vorangegangener Beiträge.
Hallo,
die Sverweis-Formel aus dem MOF-Forum macht das was du willst wesentlich einfacher und fehlerfreier als die von mir gespostete Formel.
Die von mir gepostete Formel hat ein Problem wenn die PLZ mit einer 0 beginnt. Ich habe nur die ersten Zeilen getestet, da ist mir das nicht aufgefallen.
Und damit ist für mich das Thema durch - Crossposting mag ich nicht sonderlich.
Hallo,
D1 = "*"
=INDEX($B:$B;VERGLEICH($D$1&A2&$D$1;$B:$B;0))