Baugleich Artikel - pivotieren

Bild

Betrifft: Baugleich Artikel - pivotieren
von: Alex
Geschrieben am: 02.06.2015 10:25:49

Hallo und guten Morgen liebe Forumskollegen,
ich habe heute einen Fall aus dem Produktmanagement. Wir erhalten von unseren Lieferanten Baugleich Artikeldaten in Excel und wollen diese entsprechend auswerten. Nun kann es sein, dass wir pro Zeile die gleiche Nummer erhalten mit einer entsprechenden Referenz. Ich habe eine entsprechende Datei, ganz kleiner Auszug vorbereitet.
Wäre euch dankbar über jede Hilfe.
Gruß Alex
https://www.herber.de/bbs/user/97980.xlsx

Bild

Betrifft: AW: Baugleich Artikel - pivotieren
von: Alex
Geschrieben am: 02.06.2015 10:30:47
Userbild

Bild

Betrifft: AW: Baugleich Artikel - pivotieren
von: Alex
Geschrieben am: 02.06.2015 10:36:10
sorry, hatte einen kleinen Fehler drin.
Userbild

Bild

Betrifft: nachgefragt ...
von: der neopa C
Geschrieben am: 02.06.2015 10:56:57
Hallo Alex,
... die Dupletten freien Nummern A lassen sich einfach mit einer PIVOTauswertung erzeugen aber auch gänzlich dynamisch mit einer Formel. Was würdest Du nutzen wollen?
Du hast allerdings in Deinem Zielergebnis-Listing nicht alle aufgeführt. Hat das einen Grund?
Die zugehörigen Nummern B kann dann mit einer Formel ermitteln.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ...
von: Alex
Geschrieben am: 02.06.2015 11:09:08
Hallo Werner, ja ich habe das für das Beispiel hier nicht vollends aufgeführt. Sorry.
ich würde gerne einen Weg nutzen auch wir die 1:1 Verweise und die 1:n Verweise und am besten dynamisch über ein VBA. Ohne Pivot.
Da kenne ich mich leider gar nicht aus mit.
Gruß Alex

Bild

Betrifft: eine dynamische Lösung könnte ich Dir anbieten ...
von: der neopa C
Geschrieben am: 02.06.2015 11:31:16
Hallo Alex,
... allerdings würde ich diese als eine reine Formellösung (nur zwei Formeln) aufstellen.
Wenn Du weiterhin eine VBA-Lösung suchst, dann stell den Thread wieder auf offen und schreib im neuen Betreff: VBA-Lösung gesucht.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: eine dynamische Lösung könnte ich Dir anbieten ...
von: Alex
Geschrieben am: 02.06.2015 11:36:41
Hallo Werner, die Formellösung wäre interessant. Danke für deinen Tipp und auch Unterstützung

Bild

Betrifft: da noch XL2007 mit einer Matrxformel ...
von: der neopa C
Geschrieben am: 02.06.2015 11:58:27
Hallo Alex,
... Formel A17 nach unten kopieren und die Standardformel B17 nach rechts und unten:

 BCDEFG
2So erhalten wir die Daten vom Lieferanten   
3Number ANumber B    
4WK 210 450 904 005    
5WK 31/20 450 904 059    
6WK 31/20 450 904 058    
7WK 31/20 450 904 002    
8WK 31/20 450 904 001    
9WK 31/40 450 904 060    
10WK 31/40 450 904 003    
11WK 42/10 450 904 004    
12WK 6120 450 902 161    
13WK 760 450 902 162    
14      
15So würden wir Sie gerne gedreht haben   
16Number ANumber B    
17WK 210 450 904 005    
18WK 31/20 450 904 0590 450 904 0580 450 904 0020 450 904 001 
19WK 31/40 450 904 0600 450 904 003   
20WK 42/10 450 904 004    
21WK 6120 450 902 161    
22WK 760 450 902 162    
23      

Formeln der Tabelle
ZelleFormel
B17{=INDEX(B:B;KKLEINSTE(WENN((VERGLEICH(B$3:B$14&"";B$3:B$14&"";)=ZEILE(B$3:B$14)-2)*ISTNV(VERGLEICH(B$3:B$14;B$16:B16;)); ZEILE(B$3:B$14)); 1))&""}
C17=WENN(($B17="")+(SPALTE(A1)>ZÄHLENWENN($B$3:$B$14;$B17)); "";INDEX($C:$C;VERGLEICH($B17;$B:$B;)+SPALTE(A1)-1))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: AW: da noch XL2007 mit einer Matrxformel ...
von: Alex
Geschrieben am: 02.06.2015 13:11:20
Hallo Werner, ich komme da nicht ganz so klar. Könntest du mir das für einen echten Fall bauen?
Würde dir dafür eine Excel zukommen lassen.
Gruß Alex

Bild

Betrifft: ist schon möglich, doch ...
von: der neopa C
Geschrieben am: 02.06.2015 13:19:01
Hallo Alex,
... prüfe mal, ob Du die Formel wirklich als Matrixformel abgeschlossen hast.
Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!
Wenn es immer noch nicht klappen sollte, dann kannst Du mir die ausnahmsweise auch mal zusenden, wenn Du sie nicht hier hochladen kannst.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ist schon möglich, doch ...
von: Alex
Geschrieben am: 02.06.2015 13:21:43
Ich teste das mal mit meinem Excel und 4.000 Datensätzen. Ich habe alles entsprechend angepasst und das Problem ist nur, dass sich mein Excel einfriert :)

Bild

Betrifft: nachgefragt ...
von: der neopa C
Geschrieben am: 02.06.2015 13:30:06
Hallo Alex,
... wie lautet Deine angepasste Formel?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ...
von: Alex
Geschrieben am: 02.06.2015 14:41:23
Hallo Werner, ich habe alles angepasst und im kleinen getestet. FUNKTIONIERT!!!!!!!
Vielen DANK!!

Bild

Betrifft: Interessehalber nachgefragt ...
von: der neopa C
Geschrieben am: 02.06.2015 17:09:33
Hallo Alex,
... wie viele verschiedene "Number A" haben sich ergeben und wie hat sich die Auswertung der 4000 Datensätze zeitlich in etwa wirklich ausgewirkt?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Baugleich Artikel - pivotieren
von: Daniel
Geschrieben am: 02.06.2015 13:35:22
Hi
bei grossen Datenmengen musst du die Liste manuell erstellen.
1. sortiere die Liste nach "Number A"
2. füge in Zelle D4 folgende Formel ein: =C4&Wenn(B4=B5;";"&D5;"")
3. ziehe die Formel bis ans Tabellenende
4. kopiere die Formel und füge sie an gleicher Stelle als Wert ein
jetzt stehen immer in der obersten Zeile einer Number A-Folge alle danzugehörigen Number B-Werte in einer zelle mit Semikolon getrennt.
5. wende auf die ganze Tabelle die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an , mit der Spalte B(Number A) als Kriterium.
Das löscht alle Duplikate der NumberA-Werte ausser dem ersten Vorkommen (das mit allen Werten in einer Zelle)
6. Wende auf die neue Spalte die Funktion DATEN - DATENTOOLS - TEXT IN SPALTEN mit dem Semikolon als Trennzeichen an, um die Werte wieder in die Spalten aufzuteilen
7. lösche die alte NumberB-Spalte.
ist zwar ein bisschen Handarbeit, braucht aber wesetlich weniger Rechenzeit als eine reine Formellösung.
Gruß Daniel

Bild

Betrifft: das von Dir vorgeschlagen geht aber einfacher ...
von: der neopa C
Geschrieben am: 02.06.2015 14:07:45
Hallo Daniel,
... und auch schneller über eine PIVOTauswertung. Aber genau das wollte Alex bereits nicht, wie Du weiter oben im Thread nachlesen kannst. Du kannst ihn aber eine entsprechende VBA-Lösung bereitstellen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: das von Dir vorgeschlagen geht aber einfacher ...
von: Daniel
Geschrieben am: 02.06.2015 14:26:15
Hi
VBA-Lösungen bei VBA nein mache ich nur ungern.
Ist immer ein bisschen wie jemanden der keinen Führerschein hat nen Autoschlüssel in die Hand zu drücken.
Ansonsten sollte es auch bei geringen VBA-Kenntnissen nicht allzu schwierig sein, diese Schritte mit Hilfe des Recorders in ein Makro um zu setzten.
das Makro muss ja nur diese Schritte ausführen und enthält keine anspruchsvolle Programmierlogik und ist daher grösstenteils mit dem Recorder aufzeichenbar.
du hast zwar geschrieben, dass es mit Pivot geht, allerdings nicht wie und da mir selber grad keine Möglichkeit einfällt, wie man diese Strukur mit einer Pivottabelle erzeugen kann, wäre es gut, wenn du mir erklären könntest, wie das gehen soll.
Gruß Daniel

Bild

Betrifft: meine diesbzgl. Aussage bezog sich, ....
von: der neopa C
Geschrieben am: 02.06.2015 17:06:05
Hallo,
... wie auch angegeben, auf meinene Aussage im Beitrag "nachgefragt ..." im Thread weiter oben. Die Ermittlung der "Number B" kann ja in dessen Abhängigkeit dann auch ohne Matrixformeln relativ schnell ermittelt werden. Diese Art der Ermittlung ist eine Kombination aus PIVOT und Formeln und damit sowohl noch schnell in der Auswertung und bis auf einen notwendigen Zusatzklick für die Aktualisierung der Pivotauswertung bei neuen/geänderten Daten trotzdem noch insgesamt dynamisch.
Wie die Ermittlung der Dupletten freie Ermittlung der Number A mit ein paar Mausklicks geschieht, weißt Du sicherlich auch ohne meine Erklärung.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: meine diesbzgl. Aussage bezog sich, ....
von: Daniel
Geschrieben am: 02.06.2015 17:11:21
Sorry, blick ich nicht.
Wenn ich ne PIVOT-Auswertung mache, sollte das Duplikate-entfernen doch eingenlich automatsich beim erstellen der Pivottabelle erfolgen, oder?
in den Datenfeldern der Pivottabelle stehen doch immer numerisiche Werte, hier handelt es sich aber doch um Texte.
Wie willst du damit eine Pivottabelle erstellen?
Die Werte aus "Number B" als Spaltenüberschrift zu verwenden kann ja nicht zielführend sein.
Gruß Daniel

Bild

Betrifft: Du nimmst mich jetzt aber nicht auf den Arm?...
von: der neopa C
Geschrieben am: 02.06.2015 17:34:55
Hallo,
... markiere in der Beispieldatei B3:B14 aktiviere Pivotauswertung und ziehe danach "Number A" in die Zeilenbeschriftung" und Du hast die Liste der Duplettenfreien "Number A"
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Du nimmst mich jetzt aber nicht auf den Arm?...
von: Daniel
Geschrieben am: 02.06.2015 17:40:53
Hi
naja, dublettenfreie Liste der Number A ist ja erst nur der Anfang.
du brauchst ja auch noch die Number B-Werte dahinter.
So wie du dich anhörtest klang das als würdest du gleich die vollständige Lösung mit der Pivot-Tabelle erstellen.
Um nur eine Duplikatfreie Liste zu erstellen, kann man auch das DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN nehmen.
Oder wenns dynamsich sein soll, die entsprechende Formel von WWW.Excelformeln.de.
Gruß Daniel

Bild

Betrifft: da hast Du mich wohl nicht richtig verstanden ...
von: der neopa C
Geschrieben am: 02.06.2015 17:59:24
Hallo,
... dazu lies noch einmal meinen Beitrag hier: https://www.herber.de/forum/messages/1428911.html. Darin hatte ich Bezug genommen auf meinen Beitrag hier: https://www.herber.de/forum/messages/1428812.html wo ich nichts anderes geschrieben habe.
Die einfachen Standardformeln zur Ermittlung der dazugehörigen Number B (als eine Ergänzung zur PIVOTauswertung) hatte ich innerhalb meines komplexen Formellösungsangebotes hier: https://www.herber.de/forum/messages/1428824.html aufgezeigt.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Du nimmst mich jetzt aber nicht auf den Arm?...
von: Daniel
Geschrieben am: 02.06.2015 18:20:53
Hi
ok, dann hab ich dich wirklich falsch verstanden.
dachte du wolltst da gleich komplett mit Pivot-dran gehen.
Ich nehme für das erstellen der Duplikatfreien Liste immer die entsprechende Excelfunktion.
Gruß Daniel

Bild

Betrifft: Deine Firma sollte ggf über die Anschaffung ...
von: Luc:-?
Geschrieben am: 02.06.2015 15:31:31
…von QlikView von QlikTec nachdenken, Alex,
kostenlose TestVersionen sollte es evtl noch geben. Das erleichtert die Auswertung großer Listen ungemein. Pgmierung ist möglich, etliche zusätzliche TextFktt und andere Features, die den Aufbau einer Kreuztabelle erleichtern und das Beste — mit einem Klick kann zwischen Kreuztabellen- und Diagramm-Darstellung umgeschaltet wdn. Im Laufe der letzten 13 Jahre ist noch mehr dazugekommen, wovon man sich auf ihrer WebSite überzeugen kann (voriges Jahr gab's die jedenfalls noch!).
Gruß, Luc :-?

Bild

Betrifft: PS: Export der Kreuztabelle nach Xl möglich! owT
von: Luc:-?
Geschrieben am: 02.06.2015 15:33:06
:-?

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Baugleich Artikel - pivotieren"