Microsoft Excel

Herbers Excel/VBA-Archiv

Gleiche Werte Nebeneinander Sortieren

Betrifft: Gleiche Werte Nebeneinander Sortieren von: Jeffrey Kunert
Geschrieben am: 27.09.2020 21:11:39

Hallo zusammen,


Zur Erläuterung meines Problems:

Ich möchte in Excel eine Zimmerliste, wo die Zimmernummern untereinander sortiert sind, so formatieren, dass sich die Zimmernummern mit den zugehörigen Namen nebeneinander sortieren.

Ungefähr wie folgt:


Original:

Zimmer....Nachname....Vorname

1234........Hansen.........Hans

1234........Hansen.........Hannelore

4321........Müller............Mark

4321........Müller............Mareike


Ändern in:

Zimmer....Nachname....Vorname....Zimmer1....Nachname1....Vorname1

1234........Hansen.........Hannelore..1234..........Hansen...........Hans

4321........Müller............Mareike......4321..........Müller..............Mark



Wie zu erkennen ist, sollte auch auf das Geschlecht Rücksicht genommen werden (erst die Frau dann der Mann). Dies sollte, denke ich mal, mit einer Spalte, in der das Geschlecht definiert wird klappen.


Ich habe mich dies bezgl. schon etwas auf Google rumgetrieben und einige Lösungsansätze gefunden, nur eben nichts was wirklich auf mein Problem anwendbar ist. Ich hab auch schon gehört, dass dies wahrscheinlich nur mittels VBA möglich ist, hab davon aber wirklich null Ahnung.


Hat hier jemand vielleicht eine Lösung?


Kleiner zusatz: Ich arbeite mit einer Englischen Version. Falls möglich wären Formeln in Englisch hilfreich, aber nicht zwingend notwendig.


Im Folgenden findet Ihr die Tabelle um die es am Ende geht.

https://www.herber.de/bbs/user/140474.xls



Ich danke vielmals im Voraus und bleibt gesund!

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: Johannes
Geschrieben am: 27.09.2020 21:47:46

Hallo Jeffrey,

jetzt mal im Ernst, ohne frech wirken zu wollen, aber woher soll Excel wissen, ob es sich um einen Mann oder eine Frau handeln soll? - dies klappt nur, wenn du eine Hilfsspalte einfügst, worin man unterscheiden kann, ob m oder w. Wenn das bei dir "machbar" wäre, könnte ich dir die Tage ne Lösung dazu bieten, allerdings um die Zeit leider nicht mehr.... - und ich denke fast, das klappt alles ganz ohne VBA.

cheers Johannes

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: Jeffrey Kunert
Geschrieben am: 27.09.2020 22:02:00

Hallo Johannes



wie schon geschrieben wäre dies durchaus möglich und, da stimme ich dir zu, notwendig

("[...]Dies sollte, denke ich mal, mit einer Spalte, in der das Geschlecht definiert wird klappen.").



Ich danke dir wirklich vielmals wenn du dir tasächlich die Zeit dafür nimmst. Das muss aber auch wirklich nicht mehr heute sein. Hab's da nicht eilig :)



LG

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: ralf_b
Geschrieben am: 28.09.2020 01:20:31

https://www.herber.de/bbs/user/140478.xlsm

Betrifft: AW: wäre schon auch mit Formel realisierbar ...
von: neopa C
Geschrieben am: 28.09.2020 09:00:35

Hallo Jeffrey,

... wenn in einer zusätzlichen Spalte das Geschlecht in der Datenliste angegeben wird.
Wobei ich nicht verstehe, warum in der gleichen Datenzeile die gleich Zimmernummer zweimal angeben werden soll?

Gruß Werner
.. , - ...

Betrifft: AW: wäre schon auch mit Formel realisierbar ...
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 09:19:12

Hallo Werner,
Ob sie 1x oder mehrmals angezeigt wird ist mir nicht wichtig. Sie muss halt zumindest einmal vorkommen (für den Serienbrief)

LG

Betrifft: AW: zwei Formeln sind ausreichend ...
von: neopa C
Geschrieben am: 28.09.2020 11:01:02

Hallo Jeffrey ,

... nachfolgende Formeln sind Matrixfunktion(alität)sformeln die aber keines spez. Formelabschluss wie eine klassische Matrixformeln benötigen. Die Anordnung der Namen in der Quellliste kann beliebig sein.

Formel G2 nach unten und H2 nach unten und rechts ziehend kopieren.

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJK
1ZimmerNachnameVornamem/w/d  ZimmerNachnameVornameNachnameVorname
21234HansenHansm  1234HansenHanneloreHansenHans
34321MüllerMarkm  4321MüllerMareikeMüllerMark
41234HansenHannelorew       
54321MüllerMareikew       
6           

ZelleFormel
G2=WENNFEHLER(AGGREGAT(15;6;A$2:A99/(A$2:A99<>"")/(ZÄHLENWENN(G$1:G1;A$2:A99)=0);1);"")
H2=WENNFEHLER(INDEX($B:$C;AGGREGAT(15;6;ZEILE(A$2:A99)/($A$2:$A99=$G2)/($D$2:$D99=WENN(SPALTE(A1)<3;"w";"m"));1);WENN(LINKS(H$1;1)="N";1;2));"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...

Betrifft: AW: zwei Formeln sind ausreichend ...
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 15:11:17

Hallo Werner,

vielen Dank für die Formel. Habe es soeben ausprobiert und stoße da leider noch auf das ein oder andere Hindernis:
1. Es werden nur die Vornamen von der Formel erfasst aber nicht die Nachnamen. Somit habe 2x die Vornamen der jeweiligen Person.
2. Bei Gleichgeschlechtlichen Paaren in einem Zimmer wird immer nur einer übernommen. Die anderen Zeilen nebendran bleiben leer.
3. Wenn möglich, wäre es super, dass wenn in einem Zimmer nur ein Herr ist, dieser in der ersten Zeile auftaucht (kein Beinbruch, wenn es nicht geht. Es würde den Prozess nur vereinfachen).

Ich habe nochmal die Tabelle mit meinem Versuch hochgeladen:
https://www.herber.de/bbs/user/140494.xls

Vielen Dank nochmal!
LG

Betrifft: AW: Du hast es nicht komplett übernommen ...
von: neopa C
Geschrieben am: 28.09.2020 15:28:37

Hallo Jeffrey,

... bei mir steht in H1 und J1 "Nachname" (davon nehme ich den 1. Buchstaben ="N") bei Dir nicht. Ich könnte jedoch die Formel so abändern, dass es auch diese Info nicht benötigt.

Gruß Werner
.. , - ...

Betrifft: AW: Formel abhängig von der relativ Spalte ...
von: neopa C
Geschrieben am: 28.09.2020 15:40:52

Hallo Jeffrey,

... würde dann so aussehen in H2:

=WENNFEHLER(INDEX($B:$C;AGGREGAT(15;6;ZEILE(A$2:A99)/($A$2:$A99=$G2)/($D$2:$D99=WENN(SPALTE(A1)<3; "w";"m"));1);2-REST(SPALTE(A1);2));"")


da ist dann egal, ob und was in Zeile1 steht.

Gruß Werner
.. , - ...

Betrifft: AW: Formel abhängig von der relativ Spalte ...
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 16:01:42

Hi Werner,

Jetzt hab auch ich es verstanden. Gut, das Problem wäre behoben. Weißt wie man es lösen kann, dass die Gleichgeschlechtlichen Paare korrekt angezeigt werden? Das funktioniert nämlich leider nach wie vor nicht.

Danke für deine Geduld mit mir... ^.^

LG

Betrifft: AW: ist auch möglich, doch nachgefragt ...
von: neopa C
Geschrieben am: 28.09.2020 16:10:21

Hallo Jeffrey,

... in Deiner Datei gibt es 3 Personen zu Mustermann, wie sollen diese gelistet werden?

Gruß Werner
.. , - ...

Betrifft: AW: ist auch möglich, doch nachgefragt ...
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 16:19:05

Hallo Werner,

die 2. weibliche Person müsste neben der 1. stehen (Reihenfolge wie Alphabet, etc. ist egal). Dann kommt der Mann.

LG

Betrifft: AW: Dein 2. Tabellenblatt in der Datei ...
von: neopa C
Geschrieben am: 28.09.2020 16:49:07

Hallo Jeffrey,

... hab ich mir eben erst angeschaut. Das Ergebnis soll wohl nun doch so aussehen? Dann würden die Formeln nämlich wieder etwas anders definiert werden.

Gruß Werner
.. , - ...

Betrifft: AW: wenn das Ermittlung im 2.Tab. erfolgen soll...
von: neopa C
Geschrieben am: 28.09.2020 19:43:40

Hallo Jeffrey,

... und Du vor der Spalte A eine (Leer)Spalte einfügen kannst und die Spalten "Room1 - Room3" ausblenden kannst (nicht löschen) wäre auch diese Ermittlung mit nur zwei Formeln möglich. Nur das diese länger werden.

Gruß Werner
.. , - ...

Betrifft: AW: wenn das Ermittlung im 2.Tab. erfolgen soll...
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 21:09:55

Hallo Werner,

Das 2. Tabellenblatt dient nur zur Demonstration, um zu zeigen wie das ganze am Ende ungefähr aussehen soll. Das ausblenden der zusätzlichen Room-Spalten war auch mein Plan

LG

Betrifft: AW: wenn das Ermittlung im 2.Tab. erfolgen soll...
von: ralf_b
Geschrieben am: 28.09.2020 21:14:27

hallo Jeffrey,

was gefällt dir den nan meiner vba lösung nicht?

Betrifft: AW: wenn das Ermittlung im 2.Tab. erfolgen soll...
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 21:28:57

Guten Abend Ralf,

Sorry, dein Link ist leider untergegangen. Hab es mir gerade angeschaut. Leider erscheint beim Starten des Makros "Run-time error '438': Object doesn't support this property or method"

LG

Betrifft: AW: wenn das Ermittlung im 2.Tab. erfolgen soll...
von: ralf_b
Geschrieben am: 29.09.2020 16:51:28

aha, ok.
den Fehler habe ich nicht. Es liegt dann wohl an deiner etwas älteren Excelversion.

Betrifft: AW: wenn das Ermittlung im 2.Tab. erfolgen soll...
von: Jeffrey Kunert
Geschrieben am: 30.09.2020 08:38:08

Hallo Ralf,

Das kann wirklich sein.
Aber wir haben es jetzt ohne VBA geschafft. Somit erübirgt sich dies.
Trotzdem danke ich dir, dass du dir Zeit dafür genommen hast.

LG

Betrifft: AW: hierzu nun ...
von: neopa C
Geschrieben am: 29.09.2020 10:08:09

Hallo Jeffrey,

... mit einer Hilfsspalte ist die Auswertung einfach, setzt aber voraus, dass Du bei Datenänderung/-erweiterung zunächst immer die Datenausgangslage zunächst nach m/w abwärts und dann nach Room aufwärts sortieren musst.

Solltest Du trotzdem noch an meiner Hilfsspalten-freien Formellösung interessiert sein, dann lass es mich wissen.

Gruß Werner
.. , - ...

Betrifft: AW: hierzu nun ...
von: Jeffrey Kunert
Geschrieben am: 29.09.2020 10:32:22

Guten Morgen Werner,

Ich denke mal eine Hilfspaltenfreie Formel wäre für uns besser. Ich möchte bei uns die Menge an potentiellen Fehlern so gering wie möglich halten. Auch wenn man die Hilfsspalten am Ende protecten kann, kann ich mir gut vorstellen, dass beim Sortieren der ein oder andere Probleme bekommt (auch wenn es tierisch simpel ist, eigentlich...).

Ich wäre wirklich sehr dankbar, wenn du mir die Formellösung einmal zeigen könntest.

Vielen Dank und einen guten Start in den Tag wünsche ich!

LG

Betrifft: AW: dann ...
von: neopa C
Geschrieben am: 29.09.2020 11:16:59

Hallo Jeffrey,

... als erstes füge (zwingend) in Tab. "Endlösung" vor Spalte A eine Spalte ein (die Du danach auch ausblenden kannst).

Dann in B2 folgende Formel:

=WENNFEHLER(AGGREGAT(15;6;Ausgangslage!A$2:A99/(Ausgangslage!A$2:A99<>"")/(ZÄHLENWENN(B$1:B1;Ausgangslage!A$2:A99)=0);1);"") und nach unten ziehend kopieren.

Danach in C2 (der lange Tab.-Name "Ausgangslage" lässt die Formel noch länger erscheinen, als sie auch bei kürzeren Tab.-Namen schon wäre):

=WENNFEHLER(WENNFEHLER(INDEX(Ausgangslage!$B:$D;AGGREGAT(15;6;ZEILE(C$2:C99)/(Ausgangslage!$A$2:$A99=$B2)/(Ausgangslage!$D$2:$D99="w");1+ZÄHLENWENN($A2:A2;"w"));REST(SPALTE()-2;4));INDEX(Ausgangslage!$B:$D;AGGREGAT(15;6;ZEILE(C$2:C99)/(Ausgangslage!$A$2:$A99=$B2)/(Ausgangslage!$D$2:$D99="m");1+ZÄHLENWENN($A2:A2;"m"));REST(SPALTE()-2;4)));"")

diese nach rechts nur bis Spalte E ziehend kopieren und danach die drei Formelzellen C2:E2 weit genug nach unten.

Dann den gesamten dreispaltigen Formelblock: C2:E## kopieren und in G2 K2 und O2 einfügen.

Solltest Du die Room-Nrn in Spalte F, J und N doch ermitteln und darstellen wollen, dann da
in F2: =WENN(G2="";"";B2) und nach unten kopieren und danach diesen einspaltigen Block nach J und N kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: dann ...
von: Jeffrey Kunert
Geschrieben am: 29.09.2020 15:28:39

Hallo Werner,

Das... ist... PERFEKT!
Damit kann ich super arbeiten.
Somit ist das Thema abgehakt.

VIELEN DANK! Danke für deine Zeit und Geduld.
Bleib gesund und komm' gut durch die Woche

LG

Betrifft: AW: danke, das wünsch ich Dir auch owT
von: neopa C
Geschrieben am: 29.09.2020 16:41:27

Gruß Werner
.. , - ...

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: Daniel
Geschrieben am: 28.09.2020 11:57:17

Hi

Wenn du die Geschlechterkennung drin hast, dann kannst du die Liste danach sortieren und die eine Hälfte ausschneiden und neben die andere Verschieben.

Gruß Daniel

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: Jeffrey Kunert
Geschrieben am: 28.09.2020 21:31:26

Hallo Daniel,

Danke für den Tipp, aber wenn ich es manuel hätte lösen wollen, hätte ich sicherlich kein Thema dafür im Forum geöffnet ;)
Wäre doch auch zu einfach.

LG

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: Daniel
Geschrieben am: 28.09.2020 21:47:34

was ist denn das für eine bescheuerte Aussage?
hier fragen viele, die auch nach manuellen Lösungen suchen.
ist ja egal, Hauptsache gelöst.

du hattest nicht dazugeschrieben, dass es per Formel sein MUSS.
bei VBA-nein gehe ich eigentlich davon aus, dass was ohne VBA brauchst.
und selbst wenn, auch für eine VBA-Lösung ist der von mir beschriebene Lösungsweg der einfachste Ansatz:
Zeilen sortieren und nebeneinander kopieren.
geht halt nur, wenn jede Zimmernummer auch 2x vorkommt und entsprechend gekennzeichnet ist.

wenns per formel sein muss, würde ich in der Ausgangsliste eine weiter Hilfsspalte hinzufügen, in welcher du die Zimmernummer und den Zusatz "m"/"w" oder 1/2 oder wie auch immer zu einem Begriff verkettest.

dann kopierst du die Zimmernummern (ohne den Zusatz) in eine zweite Tabelle und Entfernst die Duplikate (entweder per Formel, die sollte sich per Suchmaschine finden lassen, oder mit der Menüfunktion DATEN - DATENTOOLS - DUPLKATE ENTFERNEN, oder ganz klassisch mit dem Spezialfilter)

wenn du das hast, kannst du die werte einfach über den normalen SVerweis/VLookUP (setzte ich bei Excel gut als bekannt voraus) zuspielen, in dem du dann für die Werte der ersten Spalte dein Zusatz "w" zum Suchbegriff hinzufügst und für die zweite Spalte den Zusatz "m".


ohne diese Hilfsspalte geht's auch, dann muss die Ausgangsliste aber nach Zimmernummer sortiert sein (zumindest müssen gleiche Zimmernummern direkt untereinander stehen) und jede Zimmernummer muss 2x vorkommen, auch wenn sie nur 1x belegt ist.
dann verwendest du nicht den SVerweis, sondern dessen Ersatzkonstrukt mit Index(ErgebnisSpalte;Vergleich(Suchwert;SuchSpalte;0)) und addierst für die Zweitbelegung dann dann immer 1 zum Vergleichsergebnis hinzu: Index(ErgebnisSpalte;Vergleich(Suchwert;SuchSpalte;0)+1)

das zweite hat den Vorteil, dass du die Hilfsspalte nicht brauchst, die Hilfsspaltenlösung hat den Vorteil, dass die Zeilen auch unsortiert sein dürfen und bei einfach belegtem Zimmer die Zimmernummer nur einmal vorkommen kann.

Gruß Daniel

Betrifft: AW: Gleiche Werte Nebeneinander Sortieren
von: Daniel
Geschrieben am: 28.09.2020 22:15:35

und auch noch mit Beispieldatei für eine Formellösung.
für die richtige Reihenfolge von Männlein und Weiblein bist du verantwortlich.
die Hilfszeilen im Ergebnis habe ich eingefügt, damit die Formel über alle Spalten der Tabelle die gleiche ist.
Ohne die Hilfszeilen musst du den Wert fest in die Formel schreiben und dann hast du in jeder Spalte eine andere Formel.

https://www.herber.de/bbs/user/140503.xls

Gruß Daniel