Zwei Spalten in Zeile sortieren

Forumthread: Zwei Spalten in Zeile sortieren

11.04.2013 17:44:06

rory

Hallo Liebe Pro´s,
habe ein Problem bei dem ich Hilfe benötigen würde, ist vielleicht ganz einfach zu lösen, jedoch bin ich mit meinen basalen Excel-Kentnnissen an einer Grenze. Recherchen in Foren brachten leider keine verwertbaren Lösungen.
Folgendes Problem:
Ich habe eine zweispaltige Liste mit mehreren Zehntausend Zahlen dabei hat Spalte A Variable Werte. Spalte B sind Indexnummern leider mit unterschiedlichen Größen.
Hier ein kurzes Beispiel:
A B
3 1
4 1
2 1
4 2
3 2
5 3
6 4
1 4
2 4
9 5
3 5
4 5
7 5
8 5
Nun sollen die Spalten nach den zugehörigen Indexnummern in Zeilen sortiert werden:
A B C D E F
1 3 4 2
2 4 3
3 5
4 6 1 2
5 9 3 4 7 8
Mit WENN komm ich nicht wirklich weiter, vorallem die automatische umformatierung macht mir sorgen, und das per Hand zu machen dauert viel zu lange.
Hat jemand eine Idee? Vielen Dank schonmal im voraus.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

so vielleicht? Gruß

11.04.2013 18:12:03

robert

Tabelle2

	A	B	C	D	E	F	G	H	I
2	g	5			5	g	f	d	h
3	f	5			1	z	w
4	d	5			3	l
5	h	5			2	j	a
6	z	1
7	j	2
8	k	4
9	l	3
10	w	1
11	a	2

verwendete Formeln
Zelle	Formel	Bereich
F2	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E2;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$2
G2	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E2;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$2
H2	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E2;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$2
I2	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E2;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$2
F3	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E3;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$3
G3	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E3;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$3
H3	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E3;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$3
I3	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E3;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$3
F4	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E4;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$4
G4	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E4;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$4
H4	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E4;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$4
I4	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E4;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$4
F5	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E5;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$5
G5	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E5;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$5
H5	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E5;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$5
I5	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E5;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$5
F6	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E6;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$6
G6	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E6;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$6
H6	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E6;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$6
I6	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E6;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$6
F7	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E7;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$7
G7	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E7;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$7
H7	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E7;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$7
I7	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E7;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$7
F8	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E8;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$8
G8	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E8;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$8
H8	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E8;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$8
I8	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E8;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$8
F9	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E9;ZEILE($1:$10);"");SPALTE(A$1)));"")}	$F$9
G9	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E9;ZEILE($1:$10);"");SPALTE(B$1)));"")}	$G$9
H9	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E9;ZEILE($1:$10);"");SPALTE(C$1)));"")}	$H$9
I9	{=WENNFEHLER(INDEX($A$2:$A$11;KKLEINSTE(WENN($B$2:$B$11=$E9;ZEILE($1:$10);"");SPALTE(D$1)));"")}	$I$9

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Tabellendarstellung in Foren Version 5.46

AW: so vielleicht? Gruß

11.04.2013 19:32:10

rory

Hi Robert,
super das scheint zu funktionieren, die Zelle hat den richtigen Wert gefunden gibt ihn aber,bis auf den ersten Wert, nicht aus.

Das sind MATRIX-Fmln für EINE Zelle!!! orT

12.04.2013 00:48:23

Luc:-?

Steht doch bei den Fmln in Bereich! Mann-o-mann! :-/
Gruß Luc :-?

AW: Das sind MATRIX-Fmln für EINE Zelle!!! orT

12.04.2013 15:06:34

rory

Ja wer lesen kann ist klar im Vorteil :-) Sorry...
Vielen Dank an Robert!!!
Funktioniert jetzt alles soweit. Nur gibts jetzt ein Problem, da ich enorm lange Zahlenkolonnen habe wird die Matrix dementsprechend groß das sieht dann ungefähr so aus:
=WENNFEHLER(INDEX($B$1:$B$5000;KKLEINSTE(WENN($A$1:$A$5000=$D1;ZEILE($1:$5000);"");SPALTE(A$1)));"")
(Würde gerne ca.20000 Zeilen abfragen)
Da stößt Excel(bzw mein Rechner) an seine Grenzen, die Blattberechnung funktioniert nicht(automatisches berechnen ist aus), denke mal ist nicht genügend Speicher vorhanden. Mit kleineren Werten unter 1000 gehts, dauert aber bis zu 5min.
Hab das mal ohne Matrix nur mit WENN gelöst geht natürlich viel schneller, aber die Werte stehen dann Diagonal untereinander. Könnte dann Werte kopieren und leere Zellen löschen um alles nach oben zu setzen, aber da gibts doch bestimmt eine elegantere Variante.
Hat jemand vielleicht einen anderen Ansatz, oder kann man das obere Arbeitsspeicherschonend umschreiben?
Gruß Rory

Zum verzweifeln

12.04.2013 18:34:31

rory

Hat eventuell noch jemand eine Idee?

Probier mal das....

12.04.2013 19:48:55

robert

https://www.herber.de/bbs/user/84858.xlsm
Hi,
hab ein Makro gebastelt, bau das mal in Deine Datei ein(in eine Testdatei!)
und schau, ob dir das hilft.
Wenn unklar, Rückfrage....
Gruß
robert

Das läuft Super!!! aber

13.04.2013 11:05:04

rory

@Robert,
du bist definitiv der Star meiner nächsten Wochen, das Makro spart mir Sau viel Arbeit.
Vielen vielen Dank!!!!
Funktioniert einwandfrei habs an einer Tabelle mit 200000!!! Zeilen probiert und es rennt.
Nun ergibt sich daraus ein neues Problem(sollte ich einen neuen Beitrag aufmachen?),
die Zeilen die das Makro ausgibt sollen summiert werden, allerdings so:
=WENN(D1<=3;1;AUFRUNDEN(D1/3;0))
Funktionierte soweit, da ich mir die Ergebnisse untereinander ausgeben lasse und dann addiere,
dass funktioniert allerdings bei der Menge an Zellen nicht mehr.(Excel stürzt ab)
Nun möchte ich das Gesamtergebnis(ohne zwischenschritt) über der Spalte angeben lassen.
Hab versucht das mit SUMMEWENN zu lösen allerdings soll nicht die Zelle selbst addiert werden,
sondern "+1" für Werte "3 "WERT/3"aufgerundet.
Gibts da ne Variante oder bin ich komplett auf dem falschen Dampfer?
Viele Grüße
Rory

AW: Das läuft Super!!! aber

13.04.2013 15:25:31

robert

https://www.herber.de/bbs/user/84863.xlsm
Hi Rory,
schön, wenn es dir geholfen hat ;-)
Anbei nochmals die Datei mit der Änderung, dass vor dem Neueintrag zuerts der alte Bereich
gelöscht wird.
Bezüglich deiner Anfrage wäre eine kleine Beispieldatei gut, in der Du zeigst,
wie das Ergebnis sein soll.
Gruß
robert

Hier ein Beispiel

13.04.2013 16:53:14

rory

Danke für die Änderung :-)
Hab hier mal ein Beispiel hochgeladen:
https://www.herber.de/bbs/user/84864.xlsx
im gelben Feld steht das was rauskommen soll.
Wobei die Berechnug nun ohne Zwischenschritte stattfinden soll,
also Spalte C komplett summiert in einem Feld.(Hoffe das ist verständlich)
Gruß
rory

Hier die Formel........

13.04.2013 17:13:34

robert

=SUMMENPRODUKT(AUFRUNDEN(B5:B110/3;0))
Sollte so klappen-getestet!
Gru0
robert

AW: Hier die Formel........

13.04.2013 18:35:14

rory

Yeah so funktionierts, jetzt kanns Richtig losgehen.
Vielen Dank nochmal, weiß gar nicht wie ich das gutmachen kann.
Wünsche dir noch ein schönes Rest Wochenende.
Viele Grüße
Rory
P.S.:Beitrag kann geschlossen werden.

Na, dann leg mal los :-) owT

13.04.2013 18:37:51

robert

Beliebteste Forumthreads (12 Monate)

Forumthread: Zwei Spalten in Zeile sortieren

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Zwei Spalten in Zeile sortieren

Forumthread: Zwei Spalten in Zeile sortieren

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread