Tabelle sortieren/auslesen

[MrMagicle]

Hallo liebe Forenmitglieder,
 
ich bin neu hier, und möchte eig. nicht stören, da ich weiß, wie nervig neue Mitglieder sind die vermutlich zum 20. Mal dieselbe Frage stellen.
 
Leider haben unzählige Erklärvideos nicht geholfen und ich weiß auch nicht mit welchen Schlagworten ich im Forum suchen sollte, vielleicht. Kann mir jemand ein paar Referenzen auflisten, oder direkt hier helfen.
 
Ich brauche etwas Hilfe beim Auslesen einer Tabelle,
 
Ich habe folgende Tabelle:
 
 
[Name]/[Referenzzahl]/[Eisen]/[Alu]/[Kupfer]
 
Tobi/3/leer/leer/leer
Tobi/5/54/leer/leer
Tobi/7/leer/leer/42
Jürgen/3/leer/leer/74
Jürgen/7/12/leer/leer
...
 
 
Folgendes möchte ich erhalten:

Name/Eisen bei Refernezzahl 3/Eisen bei Referenzzahl 5/Eisen bei Referenzzahl 7/Alu bei Referenzzahl 3/Alu bei Referenzzahl 5…
 
 
Also pro Namen alle Werte bei allen Referenzzahlen. Keine Dopplung der Namen
 
[Name]/[Eisen3]/[Eisen5]/[Eisen7]/[Alu3]/[Alu5]/[Alu7]/[Kupfer3]/[Kupfer5]/[Kupfer7]
 
Tobi/leer/54/leer/leer/leer/leer/leer/leer/42
Jügen/leer/leer/12/leer/leer/leer/74/leer/leer
...
 
 
Die Datenquelle Excel ist aber sehr groß ca. 1440Zeilen x (SpaltenA-KZ)
 
Ich hoffe das meine Darstellung verständlich ist,
 
 
Vielen Dank für sämtliche Lösungsvorschläge
 
MrMagicle

[LCohen]

Was ist in F:KZ? (Deine 5 plus diese 307 ergeben 312 Spalten)

[MrMagicle]

Danke für die schnelle Antwort.

Es geht mit mehr Daten weiter, Eisen, Alu, Kupfer, Jod, Zinn, Zink, Silber, ....
 und es gibt ca. 1440 Namens Datensätzen wo allerdings nur 140 unterschiedliche Namen vorkommen. Pro Name ca 10 Datensätze.

Danke

[LCohen]

Und immer genau 3 5 7?

[MrMagicle]

Es sind mehrer verschiedene Werte aber diese kann ich auf ein Raster runden, beispielsweise genau 3,5,7,9,11,13,15,...
zur Not auch händisch, damit eine genaue Zuweisung möglich ist.

[LCohen]

Schönes Wetter heute. Wenn es dunkel ist, geht es weiter.

Deine Datendarstellung ist aber extrem schlecht. Vor allem, da Du die Zahl der wirklich benötigten Spalten nicht bestimmen kannst. Die Verteilung von Daten auf Felder anstatt Sätze ist sowieso IMMER gegen jede EDV. 

Daher würde ich einen Zwischenschritt vorschlagen, in welchem 

Name Material RefNr

als Pivot-Quelle dient.

[MrMagicle]

Vielen Dank für deine schnellen Antworten, ich hoffe du hast den sonnigen Tag genossen.

Ich glaube ich verstehen, die Strunktur ist flasch.

Wie meinst du dass, mit dem Zwischenschritt? Für jedes Material wieder ein Datensatz?

Ich könnte die Zahl der wirklich benötigten Spalten schon händisch definieren und dementsprechend festlegen, wenn das etwas bringt.

Vielen Dank.

MrMagicle

[LCohen]

Hier ist die EDV-korrekte Darstellung Deiner Daten, aus welcher Du unter anderem auch jeden Unfug herstellen kannst (XL365 oder XLWeb verwenden):

=LET(
q;A1:KZ1440;
w;WEGLASSEN(q;;2);
f;VSTAPELN({"Name"."Ref"."Mat"."Wert"};WEGLASSEN(HSTAPELN(
SPALTENWAHL(q;{1.2});
NACHZEILE(w;LAMBDA(c;VERWEIS(2;1/(c<>"");ZEILENWAHL(w;1))));
NACHZEILE(w;LAMBDA(c;VERWEIS(2;1/(c<>"");c))));
1));
FILTER(f;1-ISTFEHLER(INDEX(f;;3))))


  Mappe2.xlsx (Größe: 17,03 KB / Downloads: 4)

[MrMagicle]

Vielen Dank LCohen,

die angefügte Datei(Mappe2.xlsx) wäre exakt mein Wunschoutput.

Jetzt mach ich mich mal daran irgendwie, deine Arbeitsschritte nachzuvollziehen.

Die   =LET(       ist mir vollkommen neu. ;)


Ist es auch möglich mehrere Datensätze aus einer Zeile zu entnehmen? (Siehe Beispielbild)




Melde mich wieder.

Besten Dank
MrMagicle

[neopa]

Hallo MrMagicle,


da Du als XL-Version XL2016 angeben hast, müsstest Du für den Lösungsvorschlag von LCohen mit XLWeb arbeiten. Du kannst aber auch in XL2016 mit einer reinen Formellösung oder mit Power-Query-Pivot-Lösung meiner arbeiten, die ich in beigefügter Datei aufgestellt habe. Die Formellösung hat den Nachteil, dass die Formeln (3 verschiedene) allerdings weit genug nach unten und rechts ziehend kopiert werden müssen, was in der aktuellen XL-Version (die ich nicht besitze) nicht notwendig sein soll. Die PQ-PV-Lösung entspricht nicht ganz 1:1 Deiner Layoutvorlage, dafür ist sie wesentlich einfacher zu erstellen und schneller in der Ausführung.


Bei meinem Formel-Lösungsvorschlag müsstest Du auch beachten, dass die Quelldatenliste als sogenannte "intelligente" Tabelle formatiert ist und die eingesetzten Formeln, die im Namensmanager definiert sind, sich auf dessen Spaltenbezeichnungen beziehen. Erweiterung an "Metallen" sollte deshalb durch Einfügung vor der letzten Datenspalte (hier "Zinn") erfolgen, dann werden diese automatisch in der Formellösungstabelle berücksichtigt. Weitere Namens-Datensätze können problemlos einfach unten angefügt werden. 


Der Bereich der momentan mit Formeln "bestückt" ist, hab ich gekennzeichnet. Diese könne einfach weiter ziehend nach unten und nach rechts kopiert werden (die 3 entwickelten "Basisformeln" hab ich grün gekennzeichnet)


Ob evtl. meine Formellösung für Deine max 140x312 Ergebniszellen akzeptabel ist, musst Du testen. Wäre interessant zu wissen und würde mich freuen.


Meine Aufbereitung der Daten mit Power Query für meine Pivotauswertungslösung  erfolgt auf der gleichen Datenbasis und kannst Du nachvollziehen, wenn Du im Menü Daten [Abfragen anzeigen] und danach die Abfrage "Datenaufbereitung" aktivierst.

---

[Edit:]  den aus Versehen doppelt eingestellten Beitrag jetzt wieder gelöscht


  PQ__CEF_221030aus.xlsx (Größe: 128,22 KB / Downloads: 2)