Zuordnung unterschiedliche Werte zu einem Wert

[FlorianKra]

Guten Abend Zusammen,

schwierig zu artikulieren wo mein Problem liegt.
In Spalte A stehen Buchungsnr. und Spalte B zugehörige Standorte. Nun möchte ich die Buchungsnr. extrahiert haben bei denen unterschiedlich Standorte stehen. Ich habe es schon mit Pivot probiert. So werden zwar die unterschiedlichen Standorte der Buchungsnr. zusammengefasst aber eben auch die Buchungsnr. mit ein und dem selben Standort.
Vielleicht kann mir jemand helfen.
Anbei eine Testdatei.

Vielen Dank im Voraus,
Florian

[Frogger1986]

Du möchtest quasi keine Duplikate? Oder wie darf ich das Verstehen?

[FlorianKra]

Es geht weniger um Duplikate.
Ich möchte keine Buchungsnr. die mit ein und den selben Standort verbunden sind.

Nummer A    Standort A
Nummer B    Standort A
Nummer B    Standort B
Nummer C    Standort B
Nummer A    Standort A
Nummer C    Standort B
Nummer C    Standort C

Nummer A ist für mich uninteressant, da nur mit Standort A verbunden.

[neopa]

Hallo Florian,

war/ist nicht ganz einfach, aber mit nur zwei Matrixfunktion(alität)sformeln, die keines spez. Formelabschluss wie klassische Matrixformel benötigen, lösbar, wenn keine Massendatenauswertung (tausende Datensätze) vorgesehen sein sollte.

Formel D2 nach unten und E2 nach unten und zusätzlich nach rechts kopieren.

	A	B	C	D	E	F	G	H
1	Buchungsnummer	Depot		Buchungsnummer	Depot	Depot	Depot	Depot
2	459E37KX0RI	CDNB		459E37KX0RI	CDNB	BTSRB
3	459E53NO7KS	BTSRB		459E53NO7KS	BTSRB	SCTST
4	464E63LL0VB	BTSUL		464E63LL0VB	BTSUL	KLOMU
5	459E35LD8AM	BTSRB		459E37MH6QR	BTSRB	KLOMU	KLOAU
6	459E35LD8AM	BTSRB
7	459E35MG6WE	BTSRB
8	459E53NO7KP	BTSRB
9	459E37KX0RI	BTSRB
10	459E37MH6QR	BTSRB
11	459E37MH6QR	KLOMU
12	459E37MH6QR	KLOAU
13	459E37KX0RI	CDNB
14	464E63LL0VB	BTSUL
15	464E63LL0VB	KLOMU
16	459E53NO7KP	BTSRB
17	459E53NO7KS	SCTST
18

Formeln der Tabelle

Zelle Formel D2 =WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(ZÄHLENWENNS(A$2:A$99;A$2:A$99;B$2:B$99;B$2:B$99)<>ZÄHLENWENN(A$2:A$99;A$2:A$99))/(ZÄHLENWENN(D$1:D1;A$2:A$99)=0);1));"") E2 =WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE(A$2:A$99)/($A$2:$A$99=$D2)/(ZÄHLENWENN($D2:D2;$B$2:$B$99)=0);1))&"";"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[FlorianKra]

Hallo Wener,

vielen Dank für Deine schnelle Hilfe. Das Prinzip ist richtig.
Aber die Datei ist schon etwas größer...25.000 Zeilen.
Ich habe dein $98 manuell auf 25000 abgeändert, aber leider kommt dann überall Null als Ergebnis.
Eventuell ist die Formel nicht für die Anzahl geeignet. Wie du acuh schon vermutet hast.

[Jockel]

Hallo, schade, dass du uns zu wenig Zeit gelassen hast...

[BoskoBiati]

Hi,

vielleicht geht es hier besser.

[FlorianKra]

Auch hier nochmal, wobei ihr es wahrscheinlich eh schon im anderen Forum gelesen habt, da ihr ja so gut vernetzt seid.
Vielen Dank für den Hinweis. Ich werde es zukünftig berücksichtigen.
Der Thread kann dann auch geschlossen werden.

[neopa]

Hallo Florian,

wir sind uns einig, dass für Deine eindeutig notwendige Massendatenauswertung eine reine Formellösung ausscheidet.
Außer einer VBA-Lösung käme vor allem eine PowerQuery-Lösung in Frage. Gemäß Deiner Infos per PN wäre es günstig, wenn die Lösung auch unabhängig davon und auch in  unterschiedlichen Excelversionen läuft.

Dafür hab ich  nachfolgende Lösung aufgestellt. Es ist eine Kombination von Pivotauswertung mit anschließender Formelauswertung. Diese hab ich für Deine Datei, die Du mir übermittelt hast getestet. Die Auswertungsgeschwindigkeit für die auszuwertenden ca 44.000 Datensätze erscheint selbst auf meinen altersschwachen PC unter WIN 7 und Excel 2010 vertretbar. 

Damit dies möglich wurde, hab ich die abschließende Formelauswertung auf der Basis von möglichst einfachen und damit schnellen Formeln aufgestellt.

Die Formeln D1, D3 und I1 ermitteln Werte die mehr Informationscharakter haben. Wobei der Wert in I1 Auskunft gibt, wie weit die Formeln in Spalte G und H mindestens nach unten kopiert werden.
Analog gibt der ermittelte wert Auskunft darüber wie weit die Formeln in K2 und L2 mindestens nach unten kopiert werden müssen und der wert in J3 wie viele unterschiedliche "Depots" max auszuwerten sind.

Die Hilfsspaltenformeln in G4 und H4 einfach durch Doppelklick nach unten kopieren.  Formel in H2 ist momentan so definiert, dass damit max 9 verschiedenen Depots je Buchungsnummer ermittelt werden können. Kann natürlich erweitert oder reduziert werden.

Formel K2 entsprechend mindestens so weit nach unten und Formel L2 mindestens soweit nach rechts kopieren, wie bereits beschrieben. Anschließend durch Doppelklick nach unten kopieren.

Die Pivotauswertung in Spalte E:F für den Datenbereich weit über den momentane Datenzeilenbereich in A:C (für evtl. spätere größere Datenmengen) und im Tabellenlayout (!) vornehmen. Den Berichtsfilter setzen.
Die leeren Datensätze können bleiben oder auch ausgeblendet werden. Dies gilt so auch für die Hilfsspalte H:G

Bei erweiterten/geänderten Daten bedarf es nun lediglich eines Mausklick auf die Pivotaktualisierung und Du hast das angestrebte neue Ergebnislisting ab Spalte K. Eine komplette Neuberechnung dauerte für Deine ca. 44.000 Datensätze max  10sec.  Das wird jedoch  nur einmalig getan. Die berechnete Datei   einzuladen dauert keine Sekunde.
Auf evtl. Fragen hierzu kann ich nur noch heute Abend und dann erst wieder ab dem 03.10 antworten.

Viel Erfolg.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Buchungsnummer

Depot

Request

18

Request

REJ

15

Buchungsnummer

Depot

Depot

Depot

2

459E37KX0RI

CDNB

REJ

4

459E37KX0RI

BTSRB

CDNB

3

459E53NO7KS

BTSRB

REJ

28

Buchungsnummer

Depot

3

459E37MH6QR

BTSRB

KLOAU

KLOMU

4

459E15NN4ZL

BTSRB

ACC

459A52MS0AL

BTSRB

459E53NO7KS

BTSRB

SCTST

5

459E15NN4ZL

BTSRB

ACC

459A52MV9AE

BTSRB

464E63LL0VB

BTSUL

KLOMU

6

464E63LL0VB

BTSUL

REJ

459E35LD8AM

BTSRB

7

459E35LD8AM

BTSRB

REJ

459E35MG6WE

BTSRB

8

459E15NN4ZL

KLOAU

ACC

459E37KX0RI

BTSRB

8

2

9

459E35LD8AM

BTSRB

REJ

CDNB

10

459E35MG6WE

BTSRB

REJ

459E37MH6QR

BTSRB

10

3

11

459E15NQ4ZA

BTSRB

ACC

KLOAU

12

459E37KX0RI

KLOMU

ACC

KLOMU

13

459E53NO7KP

BTSRB

REJ

459E53NO7KP

BTSRB

14

459E52NO7TV

BTSRB

ACC

459E53NO7KS

BTSRB

14

2

15

459E37KX0RI

BTSRB

REJ

SCTST

16

459E37MH6QR

BTSRB

REJ

464E63LL0VB

BTSUL

16

2

17

459E37MH6QR

KLOMU

REJ

KLOMU

18

459E15NQ4ZF

BTSRB

ACC

19

459E37MH6QR

KLOAU

REJ

20

459E37KX0RI

CDNB

REJ

21

464E63LL0VB

BTSUL

REJ

22

464E63LL0VB

KLOMU

REJ

23

459E53NO7KP

BTSRB

REJ

24

459E53NO7KS

SCTST

REJ

25

459A52MS0AL

BTSRB

REJ

26

459A52MV9AE

BTSRB

REJ

27

459E15NQ4ZF

SCTST

ACC

28

459E52NO7TV

SCTST

ACC

29

Formeln der Tabelle

Zelle Formel D1 =ZÄHLENWENN(C:C;"REJ") I1 =ANZAHL2(F:F)-2+1 J2 =ANZAHL(G:G) K2 =WENN(ZEILE(A1)>J$2;"";INDEX(E:E;KKLEINSTE(G:G;ZEILE(A1)))) L2 =WENN(K2="";"";WENN(SPALTE(A1)>INDEX($H:$H;KKLEINSTE($G:$G;ZEILE(A1)));"";INDEX($F:$F;KKLEINSTE($G:$G;ZEILE(A1))+SPALTE(A1)-1))) D3 =ANZAHL2(A:A) J3 =MAX(H:H)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[BoskoBiati]

Hallo Werner,

so ganz traue ich Deiner Auswertung nicht:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C
1	Buchungsnummer	Depot	Request
2	459E37KX0RI	CDNB	REJ
12	459E37KX0RI	KLOMU	ACC
15	459E37KX0RI	BTSRB	REJ
20	459E37KX0RI	CDNB	REJ

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Arbeitsblatt mit dem Namen 'Tabelle2'
	J	K	L	M
1		Buchungsnummer	Depot	Depot
2	4	459E37KX0RI	BTSRB	CDNB

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg