Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > Daten auswerten aus zwei Tabellen in einem Blatt
Normale Version: Daten auswerten aus zwei Tabellen in einem Blatt
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.

n8lauscher

19.09.2017, 21:04
Hallo Liebe Excel-Profis

ich habe zwei Tabellen in einem Blatt zusammengefügt und muss diese auswerten.
Es geht hier um Versandkosten und darum diese entsprechend zu kontrollieren.
Da es mehrere Tausend - Datensätze sind - kann ich diese nicht manuell kontrollieren und möchte dies mit einer Excel-Formel erledigen

Tabelle 1 = Auftrag_einzeilig
Tabelle 2 = Auftrag detail

Auftrag_einzeilig
in dieser Tabelle stehen pro Auftrag (Auftragsnummer) die berechneten Versandkosten (Versandkosten)
Leider werden vom Spediteur aber nicht die entsprechenden Artikel gelistet, die entsprechend versendet worden sind. Daher fällt zunächst einmal die Kontrolle der berechneten Versandkosten schwierig aus.
Ich müsste jetzt jeden Auftrag einzeln öffnen, gucken wie oft (Menge) welcher Artikel (Produkt) versendet wurde - und diesen dann manuell eintragen. 
Ich würde das ganze gerne mit Excel lösen. 
Hierfür habe ich aus unserem Auftragsprogramm mir die entsprechenden Datensätze exportiert und diese in die Tabelle Auftrag_detail kopiert.

Auftrag_detail 
In dieser Tabelle steht jeweils die Auftragsnummer (Auftragsnummer), die Menge (RowQuantity), die Artikelnummer (ItemNo) und die wenn es sich um eine Variante handelt entsprechende Varianten-Artikelnummer (ItemVariationNo).

Was soll dabei rauskommen:
die Menge und die Artikelnummer soll aus Tabelle Auftrag_detail herausgefiltert und entsprechend in die Tabelle Auftrag_einzeilig kopiert werden. 
folgende Bedingung muss beachtet werden:
wenn in Auftrag_detail RowBundleItemID=-1 ist - dann soll als Artikelnnummer in Auftrag_einzeilig - die ItemNo + RowQuantity übertragen werden.
wenn nicht dann soll als Artikelnnummer in Auftrag_einzeilig - die ItemVariationNo + RowQuantity übertragen werden.
Das ganze soll wenn möglich komplett automatisch funktionieren und so sein, dass die Tabelle Auftrag_einzeilig immer wieder mit neuen zu prüfenden Datensätzen befüllt werden kann. 
Entsprechend wir sich auch die Tabelle Auftrag_detail erweitern.

ich hoffe Ihr versteht - was mein Ziel ist.
Würde mich freuen, wenn mir einer von den Profis hier helfen könnte!


LG
Martin

Fennek

20.09.2017, 11:35
Hallo,

teste mal diesen Code:
Code:
Sub iFen()

Sheets("Auftrag detail").Activate
With CreateObject("Scripting.Dictionary")
Ar = Cells(1).CurrentRegion
   For i = 2 To UBound(Ar)
       If Ar(i, 5) = -1 Then
           Tx = Ar(i, 2) & "|" & Ar(i, 3)
       Else
           Tx = Ar(i, 2) & "|" & Ar(i, 4)
       End If
       If .exists(Ar(i, 1)) Then
           .Item(Ar(i, 1)) = .Item(Ar(i, 1)) & "|" & Tx
       Else
           .Item(Ar(i, 1)) = Tx
       End If
       
   Next i
'### Ausgabe
Sheets("Auftrag_einzeilig").Activate
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
   If .exists(Cells(i, "A").Value) Then
       Tx = Split(.Item(Cells(i, "A").Value), "|")
       Cells(i, "F").Resize(, UBound(Tx) + 1) = Tx
   End If
Next i
Debug.Print .Count
End With
End Sub

mfg

Fennek

20.09.2017, 11:36
Hallo,

teste mal diesen Code:
Code:
Sub iFen()

Sheets("Auftrag detail").Activate
With CreateObject("Scripting.Dictionary")
Ar = Cells(1).CurrentRegion
   For i = 2 To UBound(Ar)
       If Ar(i, 5) = -1 Then
           Tx = Ar(i, 2) & "|" & Ar(i, 3)
       Else
           Tx = Ar(i, 2) & "|" & Ar(i, 4)
       End If
       If .exists(Ar(i, 1)) Then
           .Item(Ar(i, 1)) = .Item(Ar(i, 1)) & "|" & Tx
       Else
           .Item(Ar(i, 1)) = Tx
       End If
       
   Next i
'### Ausgabe
Sheets("Auftrag_einzeilig").Activate
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
   If .exists(Cells(i, "A").Value) Then
       Tx = Split(.Item(Cells(i, "A").Value), "|")
       Cells(i, "F").Resize(, UBound(Tx) + 1) = Tx
   End If
Next i
End With
End Sub

mfg

n8lauscher

20.09.2017, 17:31
@fennek

Hallo,

geht das ganze nicht auch mit einer Formel?
direkt in der Datei?

n8lauscher

22.09.2017, 11:38
@Fennek

Hallo Fennek,

ich habe die Anforderung noch einmal aktualisiert.
Deine erste Lösung war schon recht hilfreich - leider hat sich aber herausgestellt, dass ich doch die Daten ein wenig anders auswerten muss.
Auch sind noch zwei weitere Herausforderungen aufgetreten, Die Auftragsnummern enthalten mitunter auch einen String - welcher nicht mit verarbeitet werden soll.
Auch soll - wenn die Auftragnummer nicht in der Artikelliste gefunden wird - dies dann gemeldet werden.

Habe daher die Exceldatei noch einmal überarbeitet.

Kannst du dies vielleicht noch einmal versuchen? 

Es wäre echt super!

DANKE!

schauan

24.09.2017, 06:21
Hallöchen,

hier mal auf Basis der Autofilter-Formel von excelformeln.de ein Formel-Ansatz. Voraussetzung wäre, dass Deine ID immer numerisch ist. Ich habe zuerst die Originallösung angewendet und dann in eine waagerechte geändert. Beachte in der waagerechten die Angaben A1, A2, A3 usw. am Ende der Formel. Im Original passt sich das automatisch beim Runterziehen an, hier habe ich es manuell eingetragen.

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJKL
1NummerArtikel gefiltertArtikel NummerArtikelNummerArtikelNummerArtikel
21A 1A 1A1C#ZAHL!#ZAHL!
35B 1C       
41C #ZAHL!#ZAHL!       
53D #ZAHL!#ZAHL!       
63E          
73F          

ZelleFormel
D2{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A1)))}
E2{=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B1)))}
G2{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A1)))}
H2{=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B1)))}
I2{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A2)))}
J2{=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B2)))}
K2{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A3)))}
L2=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B3)))
D3{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A2)))}
E3{=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B2)))}
D4{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A3)))}
E4{=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B3)))}
D5{=INDEX(A$1:A$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(A4)))}
E5{=INDEX(B$1:B$100;KKLEINSTE(WENN((A$1:A$100=1);ZEILE($1:$100));ZEILE(B4)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

n8lauscher

25.09.2017, 16:00
Hallo Liebe Excel-Profis,

hier noch einmal alle Anforderungen in meiner Beispieldatei

auf anraten von Jockel jetzt auch mit Versionsnummer.


Besten Dank für die Hilfe bisher!

schauan

26.09.2017, 16:45
Hallöchen,

hier mal was angepasstes. Da fehlt allerdings noch die Bundle-Sache. Da wäre es sicher einfacher, wenn Du die Liste auch nach den Bundles sortierst und diese ans Ende des Artikels stellst. Ich könnte in der Formel noch eine Prüfung einbauen, allerdings hättest Du dann ein Produkt 1, dann zwei Leere, und dann ein Produkt 4. Bei der vorgeschlagenen Sortierung wäre die "Lücke" nicht der Fall, weil die leeren dann ans Ende kämen.

Arbeitsblatt mit dem Namen 'gewünschtes Ergebnis'
 RSTU
1Menge
Produkt 1		Artikelnummer
Produkt 1		Menge
Produkt 2		Artikelnummer
Produkt 2
21YX-53s1YX-OS
31DB-0082+2s1Bahamas
4  Soll:STRA-H02E

ZelleFormel
R2{=INDEX('ROW-Artikelliste'!$C$1:$C$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D2);ZEILE($1:$100));ZEILE($B$1)))}
S2{=INDEX('ROW-Artikelliste'!$D$1:$D$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D2);ZEILE($1:$100));ZEILE($B$1)))}
T2{=INDEX('ROW-Artikelliste'!$C$1:$C$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D2);ZEILE($1:$100));ZEILE($B$2)))}
U2{=INDEX('ROW-Artikelliste'!$D$1:$D$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D2);ZEILE($1:$100));ZEILE($B$2)))}
R3{=INDEX('ROW-Artikelliste'!$C$1:$C$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D3);ZEILE($1:$100));ZEILE($B$1)))}
S3{=INDEX('ROW-Artikelliste'!$D$1:$D$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D3);ZEILE($1:$100));ZEILE($B$1)))}
T3{=INDEX('ROW-Artikelliste'!$C$1:$C$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D3);ZEILE($1:$100));ZEILE($B$2)))}
U3{=INDEX('ROW-Artikelliste'!$D$1:$D$100;KKLEINSTE(WENN(('ROW-Artikelliste'!$B$1:$B$100=D3);ZEILE($1:$100));ZEILE($B$2)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg
Clever-Excel-Forum > Office-Anwendungen > Excel > Daten auswerten aus zwei Tabellen in einem Blatt