Suche in einem bestimmten Tabellenblatt und eventuell Wert nehmen

[hemorieder]

Hey,

ich habe folgende Situation:
-----------------------------------------------------------------------------------
Blatt 1: Übersicht
Kunde Produkt A Produkt B
ABC Anzahl Summe Anzahl Summe
XYZ Anzahl Summe Anzahl Summe(*)
-----------------------------------------------------------------------------------
Blatt 2: Kunde ABC
ICH_BIN_PRODUKT_A ANZAHL SUMME
ICH_BIN_PRODUKT_B ANZAHL SUMME
-----------------------------------------------------------------------------------
Blatt 3: Kunde XYZ
ICH_BIN_PRODUKT_B ANZAHL SUMME
-----------------------------------------------------------------------------------

Nun möchte ich gerne, dass das Blatt 1 (Übersicht) sich je nach Kunde das passende Blatt sucht und schaut, ob dort ein Produkt auftaucht und wenn dem so ist, dann soll die Anzahl und Summe genommen werden und eingefügt werden.
Nicht immer taucht jedes Produkt in der exakten Reihenfolge auf oder überhaupt auf.

Hier mal in Pseudocode für das Feld mit dem (*)

ÖFFNE BLATT XYZ
SCHAU NACH OB DORT DER EINTRAG "ICH_BIN_PRODUKT_B" existiert
WENN DEM SO IST DANN NIMM AUS DIESER ZEILE DEN WERT IN SPALTE_3
WENN NICHT DANN 0

Ich hoffe ihr versteht was ich meine, ich glaube es ist eigentlich nicht so kompliziert.

LG

 

[steve1da]

http://www.office-loesung.de/p/viewtopic.php?f=166&t=868932

 

[hemorieder]

Hey, danke für deine Antwort an beiden Orten.
Kannst du mir vielleicht für meinen Fall ein Beispiel machen, ich habe davon leider absolut gar keine Ahnung.

 

[steve1da]

Und ich weiß leider nicht wo genau die Daten stehen. Eine Beispieldatei wäre hilfreich.

 

[hemorieder]

Ich habe eine Beispiel Datei angehangen.
Wichtig ist eigentlich nur, dass nicht jeder Kunde immer alle Produkte hat.
Der erste Wert neben einem Produkt ist immer die Anzahl und der daneben die Summe.
Es muss nicht so dynamisch sein, dass ich mal schnell einen neuen Kunden anlegen muss etc.
Eigentlich soll immer jedes Feld auf genau das Tabellenblatt des Kunden gehen und dort nach dem Produkt suchen und die Werte einfügen.

 

[steve1da]

Glückwunsch, bei dem Aufbau darfst du die Formel für jedes Produkt anpassen.
In B4:
=WENNFEHLER(SVERWEIS("ich_bin_"&$B$3&"_"&$C$3;INDIREKT(WECHSELN($A4;" ";"")&"!$A$1:$C$100");SPALTE(B1);0);"")
Die Formel nach Spalte C und dann nach unten ziehen.
In D4:
=WENNFEHLER(SVERWEIS("ich_bin_"&$D$3&"_"&$E$3;INDIREKT(WECHSELN($A4;" ";"")&"!$A$1:$C$100");SPALTE(B1);0);"")
Also für jedes Produkt die Werte aus Zeile 3 anpassen.

 

[hemorieder]

Also erstmal vielen Dank, dass du dir so Mühe gibst.
Leider klappt das nur so halb. Vermutlich liegt das aber eher an mir.
Ich musste den Produktnamen statisch machen, da dieser in "echt" auch abweichen kann.
Dennoch stimmt die Tabelle nicht mit den passenden Blättern überein.

 

[steve1da]

Es muss immer im Sverweis Spalte(B1) und dann Spalte(C1) heißen (alternativ geht auch 2 bzw. 3).

 

[hemorieder]

Das verstehe ich leider nicht ganz, was ist denn z.B in B5 falsch?

 

[steve1da]

Was soll denn da falsch sein? Kunde2 hat Produkt A mit den hinterlegten Werten.

 

[hemorieder]

Ok stimmt, mein Fehler.
Ich habe nun alles ausgefüllt, dennoch gibt es Fehler.
Kunde4 hat Produkte, Kunde3 mehr als nur eins. Usw

 

[steve1da]

In B4 fragt der Sverweis den Kunden ab, der in A4 steht. Soweit, so richtig.
Ab F4 fragt der Sverweis den Kunden ab, der in A6 steht. Warum? Dadurch verschiebt sich alles.
Und da auf einmal bei den Kunden in Spalte A kein Leerzeichen mehr steht, kann man sich das Wechseln() sparen.
=WENNFEHLER(SVERWEIS("ICH_BIN_PRODUKT_A";INDIREKT($A4&"!$A$1:$C$100");SPALTE(B1);0);"")

 

[hemorieder]

Ach mega danke! Das klappt echt super.
Nur eine Sache habe ich noch, wie kann ich in einem Feld sagen, dass der Wert ICH_BIN_PRODUKT_A oder ICH_BIN_PRODUKT_B sein kann?

Also wie baue ich da ein oder ein?

 

[Majestro1337]

Etwas performanter bei großen Datenmengen gehts auch mit diesem Makro:

xlsm darf ich leider nicht anhängen, aber es basiert auf der letzten angehängten test.xlsx mit der modifikation, dass die Produktnamen in der Zeile 3 immer den Produktnamen auf den Kundenblättern entsprechen!

Sub such()

Dim ws As Worksheet

Set wb = Workbooks(ThisWorkbook.Name)
Set ws = ActiveSheet

letztezeile = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
letztespalte = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Column

'Konstanten
erstezeile = 4 'erste zeile mit nutzdaten nach dem Header. in der beispielmappe = 4
ersteSpalte = 2 'erste spalte, die Nutzdaten enthält
spaltekunde = 1 'spalte die die kundennamen enthält
ProduktZeile = 3 'Zeile mit den Produktnummern
'voraussetzung: Der Produktname in der Produktzeile muss gleich dem namen der Produkte in den Tabellenblättern sein!
ColumnMenge = 2 'Spalte auf den Kundenblättern, auf denen die BEstrellmenge zu finden ist
ColumnUmsatz = 3 'Spalte auf den Kundenblättern, auf denen der Umsatz zu finden ist

For Each Row In ws.Range(ws.Cells(erstezeile, spaltekunde), ws.Cells(letztezeile, 1)).Rows
    Kundenname = ws.Cells(Row.Row, spaltekunde)
    For Each Sht In ActiveWorkbook.Sheets
        If Sht.Name = Kundenname Then
            For Each Column In ws.Range(ws.Cells(ProduktZeile, ersteSpalte), ws.Cells(ProduktZeile, letztespalte)).Columns
                If ws.Cells(3, Column.Column) <> "" Then
                    Produktname = ws.Cells(3, Column.Column)
                    Set Hit = Sht.Range("A:A").Find(What:=Produktname)
                    If Not Hit Is Nothing Then
                        HitRow = Hit.Row
                        ws.Cells(Row.Row, Column.Column) = Sht.Cells(Hit.Row, ColumnMenge)
                        ws.Cells(Row.Row, Column.Column + 1) = Sht.Cells(Hit.Row, ColumnUmsatz)
                    End If
                End If
            Next Column
        End If
    Next Sht
Next Row

End Sub

 

[hemorieder]

In B4 fragt der Sverweis den Kunden ab, der in A4 steht. Soweit, so richtig.
Ab F4 fragt der Sverweis den Kunden ab, der in A6 steht. Warum? Dadurch verschiebt sich alles.
Und da auf einmal bei den Kunden in Spalte A kein Leerzeichen mehr steht, kann man sich das Wechseln() sparen.
=WENNFEHLER(SVERWEIS("ICH_BIN_PRODUKT_A";INDIREKT($A4&"!$A$1:$C$100");SPALTE(B1);0);"")

Hey,

ich habe noch zwei Fragen, die eine habe ich bereits gestellt nämlich:

1. Wie kann ich in einem Feld sagen, dass der Wert ICH_BIN_PRODUKT_A oder ICH_BIN_PRODUKT_B sein kann?
2. Es kann passieren, dass bei den einzelnen Kunden ein Produkt zwei Mal auftaucht, also so:
ICH_BIN_PRODUKT_A 25 15125
ICH_BIN_PRODUKT_A 133 58866
Ich würde gerne in diesem Fall die Summe übertragen wollen. Also sollte in der Hauptübersicht stehen:
158 und 73991

Kann man diese beiden Punkte irgendwie realisieren?

 

[steve1da]

Dann muss man eine ganz andere Formel nehmen. Ich fang mal mit Aufgabe2 an:
B4=SUMMEWENN(INDIREKT($A4&"!A1:A3");"ich_bin_produkt_a";INDIREKT(A4&"!B1:B3"))
C4=SUMMEWENN(INDIREKT($A4&"!A1:A3");"ich_bin_produkt_a";INDIREKT(A4&"!C1:C3"))

Aufgabe1:
B4=SUMME(SUMMEWENN(INDIREKT($A4&"!A1:A3");{"ich_bin_produkt_a"."ich_bin_produkt_b"};INDIREKT(A4&"!B1:B3")))
C4=SUMME(SUMMEWENN(INDIREKT($A4&"!A1:A3");{"ich_bin_produkt_a"."ich_bin_produkt_b"};INDIREKT(A4&"!C1:C3")))

 

[hemorieder]

Und beides kombinieren? Also Summe wenn Produkt A mehrfach vorkommt und Summe wenn Produkt A oder B ?

 

[steve1da]

Dann nimm einfach die 2. Formel. Die addiert beides. Und wenn nur ProduktA in der Liste ist, halt nur ProduktA.

 

[hemorieder]

Perfekt! Vielen vielen Dank.
Auch wenn es nichts mehr mit der eigentlichen Frage zutun hat, aber ich würde gerne den Inhalt von A15 bis A53 mit einem Klick auf einen Button in die Zwischenablage kopieren (ohne ") .
Wie kann ich das realisieren? Ich finde viele Skripte, aber ich habe absolut keine Ahnung wo ich dies eingeben soll, dass dort ein Button erscheint.

 

[cumulonimbus8]

Stichwort Steuerelemente 😉
CN8