Bedingte Summen

Hallo zusammen,
ich möchte euch bitten mir mit folgendem Problem zu helfen:
Ich habe eine Exceldatei mit zwei Excelblättern. Auf dem Excelblatt Nr. 1 stehen Kundennamen des Unternehmens X (4.000 Einträge), der Excelblatt Nr. 2 enthält die gleichen Kundennamen, die allerdings nicht immer identisch bezeichnet werden (es fehlt z.B. ein Komma, ein Punkt, ein Leerzeichen, ein Buschstabe, etc.) sowie die Höhe der Auftragseingänge, die das Unternehmen X im Laufe des letzten Jahres mit diesen Kunden getätigt hat. Dabei wiederholen sich die Kundennamen auf dem Blatt Nr.2, je nach Anzahl der getätigten Auftragseingänge.
Beispiel:
Excelblatt Nr. 1
Kundenname: (Spalte A)
K.A.M. Switzerland
P. Müller Technologien
A+B Werke
Excelblatt Nr. 2
Kundenname (Spalte D) Auftragseingänge (Spalte E)
KA.M Switzerland 500.000
P Müllertechnologien 200.000
A und B Werke 300.000
Ich möchte jedem Kunden im Excelblatt Nr.1 die Summe der Auftragseingänge aus dem Blatt Nr. 2 zuweisen. Bis jetzt wurde folgende Formel benutzt:
=SUMMEWENNS(Excelblatt2!E:E;'Excelblatt1!$A:$A;"Switzerland*";'Excelblatt2 '!$D:$D;"="&$D2).
=SUMMEWENNS(Excelblatt2!E:E;'Excelblatt1!$A:$A;"Müller*";'Excelblatt2 '!$D:$D;"="&$D3).
etc.
Da es sich aber um 4.000 Kundennamen handelt, müsste ich in 4.000 Zeilen bzw. in jeder einzelnen Formel den Kundennamen anpassen (Switzerland, Müller, Werke etc.). Gibt es eine Formel, mit welcher ich die Auftragseingänge dem Excelblatt Nr. 1 schneller zuweisen kann? Eine Formel, die mir sagt: wenn der Kundenname im Excelblatt Nr. 1 dem Kundenname vom Excelblatt Nr. 2 ähnlich ist, dann sollen die Auftragseingänge der Spalte E vom Excelblatt Nr. 2 summiert werden?
Vielen Dank! Lucie

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Function Strip(sText As String) As String Dim ar() As Variant Dim i As Integer, newText As String ar = Array(" ", ".", ",", "+", "&") newText = UCase(sText) For i = 0 To UBound(ar) newText = Replace(newText, ar(i), "") Next i Strip = newText End Function

…lässt sich dein Ziel wie folgt erreichen, Lucie:

	A	B	C	D	E
1	Kundenname	Auftragseingänge	*(Strip)*	Kundenname	Auftragseingänge
2	K.A.M. Switzerland	625 000	625 000	P Müllertechnologien	200 000
3	P. Müller Technologien	350 000	350 000	A und B Werke	300 000
4	A+B Werke	430 000	430 000	KA.M Switzerland	500 000
5	B2[:B4]: {=SUMME(E$2:E$8(WECHSELN(WECHSELN(KLEIN(WECHSELN*			A & B Werke	100 000
6	(MaskOn(A2;"anu");" ";""));"und";"");"u";"")=WECHSELN(WECHSELN			P.Müller-Technologien	150 000
7	(KLEIN(WECHSELN(MTRANS(TxEval(WECHSELN("MaskOn(D#,""anu"")";			KAM Switzerland	125 000
8	"#";ZEILE(D$2:D$8))));" ";""));"und";"");"u";"")))}			AuB Werke	30 000
9	C2[:C4]: {=SUMME((Strip(A2;" . , - + & und u")=MTRANS(TxEval(WECHSELN("Strip(D#,"" . , - + & und u"")";"#";ZEILE(D$2:D$8)))))E$2:E$8)}*

Weil mit Chris' UDF eine kürzere Fml möglich wäre, habe ich sie ebenfalls dargestellt. Leider ist diese UDF ebensowenig wie meine alte, universellere MaskOn für hier erforderliche ZellBereiche ausgelegt, weshalb ich beide UDFs zT als US-notierten FmlText mit meiner UDF TxEval auswerten musste. Desweiteren war es erforderlich, Chris' UDF etwas zu verändern. Es sollte nämlich nicht sein, dass UDFs individuell angepasst wdn müssen. Solche Erfordernisse bedient man besser über ein zusätzliches Argument:

Function Strip(sText As String, Optional ByVal EntfZ)
Dim i As Integer, newText As String
If Not IsMissing(EntfZ) Then
EntfZ = Replace(Replace(EntfZ, " ", Chr(0)), String(2, Chr(0)), Chr(0) & " " & Chr(0))
EntfZ = Split(Mid(EntfZ, 1 - CInt(InStr(EntfZ, Chr(0)) = 1)), Chr(0))
newText = LCase(sText)
For i = 0 To UBound(EntfZ)
newText = Replace(newText, EntfZ(i), "")
Next i
Strip = newText
Else: Strip = Replace(sText, " ", "")
End If
End Function

Die UDF MaskOn befindet sich direkt im Archiv (seit 2005, Neufassung von 2014 benutzen!). Von der UDF TxEval existiert inzwischen die Version1.2, die seit Kurzem hier zu finden ist.
Feedback nicht unerwünscht! Gruß, Luc :-?
Besser informiert mit …

Beliebteste Forumthreads (12 Monate)

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread