Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1528to1532
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1528to1532
1528to1532
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Anzahl Duplikate per Formel (2 Datenbereiche)

Anzahl Duplikate per Formel (2 Datenbereiche)
23.12.2016 10:20:30
Joerschi
Hallo liebes Forum,
ich möchte durch den Vergleich zweier Wertebereiche per Formel die Anzahl an Duplikaten ermitteln, welche in beiden Wertebereichen auftauchen. Leerzeilen sollen ignoriert werden; die Werte können sowohl Zahlen als Buchstaben sein.
Beispiel:
Userbild
Musterdatei: https://www.herber.de/bbs/user/110181.xlsx
Wertebereich 1 ist A2:G2 / Wertebereich 2 ist H4:P4.
Das richtige Ergebnis der Anzahl Duplikate wäre 5 ("1", "B", "A", "Y").
Vielen Dank für Eure Hilfe vorab und weihnachtliche Grüße!
Joerschi

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Korrektur richtige Musterantwort
23.12.2016 10:25:10
Joerschi
Das richtige Ergebnis der Anzahl Duplikate wäre 5 ("1", "B", "A", "Y").
Die richtige Antwort ist natürlich 4...
VBA Lösung
23.12.2016 10:29:04
ChrisL
Hi Joerschi
Vielleicht so...
Sub t() Dim rng As Range, iZaehler As Long Dim rng1 As Range: Set rng1 = Range("A2:G2") Dim rng2 As Range: Set rng2 = Range("H4:P4") For Each rng In rng1.Cells If WorksheetFunction.CountIf(rng2, rng) > 0 Then _ iZaehler = iZaehler + 1 Next rng MsgBox "Ergebnis: " & iZaehler End Sub cu
Chris
AW: VBA-Lösung hilft leider nicht :-(
23.12.2016 10:35:34
Joerschi
Hallo Chris,
vielen Dank für Deinen Lösungsvorschlag, aber ich benötige unbedingt eine Formellösung wie im Betreff benannt. VBA hilft mir hier nicht weiter (aber womöglich mal einem stillen Mitleser irgendwann, welcher das Problem damit lösen kann).
Beste Grüße
Joerschi
Anzeige
AW: Anzahl Duplikate per Formel (2 Datenbereiche)
23.12.2016 10:35:16
ransi
Hallo,
Schau mal hier:
Tabelle1

 ABCDEFGHIJKLMNOPQRST
21AB4Y             54
3                    
4       YB1 89AA     

Formeln der Tabelle
ZelleFormel
S2=SUMMENPRODUKT(ZÄHLENWENN(H4:P4;A2:G2))
T2=SUMMENPRODUKT(ISTZAHL(VERGLEICH(A2:G2;H4:P4;0))*1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi
Anzeige
AW: Danke - aber Lösung ohne Hilfszelle mgl?
23.12.2016 10:38:57
Joerschi
Hallo ransi,
vielen Dank für Deinen Vorschlag - funktioniert bestens.
Da es sich bei mir um große zu vergleichende Datenmengen handelt würde ich eine Lösung ohne Hilfszelle bevorzugen (sorry - hatte ich im Eingangspost nicht ausdrücklich erwähnt).
Ich schalte die Frage daher vorsorglich weiter offen, falls noch jemand solch eine Lösung vorschlägt.
Einstweilen nochmals herzlichen Dank - bis dahin arbeite ich mit Deinem Vorschlag :-)
Beste Grüße
Joerschi
{=ANZAHL(VERGLEICH(A2:G2;H4:P4;0))}
23.12.2016 10:42:42
WF
als Arrayformel
WF
AW: Danke - aber Lösung ohne Hilfszelle mgl?
23.12.2016 10:43:30
Hajo_Zi
es ist doch nur die Formel in T2 relevant. Wo möchtest Du das Ergebnis haben, wenn nicht in einer Zelle?
Gruß Hajo
Anzeige
AW: Ja, stimmt - selbst geschlafen...
23.12.2016 10:45:23
Joerschi
Ich habs grad gesehen. Argh - völlig gepennt.
ransi hat die zweite Formel direkt als Alternative bei Doppelnennungen innerhalb eines Wertebereiches mitgeliefert.
Danke nochmals an alle Helfer für Qualität und Schnelligkeit der Ergebnisse.
Weihnachtliche Grüße
joerschi
Ergänzungsfrage für zusätzliche Auswahl Datensätze
23.12.2016 11:16:47
Joerschi
Hallo @ransi bzw. auch andere Helfer,
die Ergänzungsfrage bezieht sich unmittelbar auf meine vorherige Frage, weil sie eine weitere Bedingung zum Lösungsvorschlag beinhaltet, aber eng verwandt ist. Daher hänge ich sie mal hier ran und habe die Frage wieder geöffnet.
Wenn das nicht gewünscht ist, bitte ich um kurzen Hinweis und eröffne einen neuen Thread.
Skizze vorab, Erklärung darunter:
Userbild
Aktualisierte Musterdatei: https://www.herber.de/bbs/user/110183.xlsx
Zusätzlich zu dem Vergleich oben sind jetzt mehrere Datensätze im Bereich B2:BH5 gegeben und deren Namen in Spalte A2:A5.
In den Zellen U4:X4 soll per Abgleich der Daten-Satz-Nummern (Überschriften in U3:X3 mit Vergleich zu Datensatz-Nr. A2:A5) die Lösungsformel ergänzt werden (richtige Musterergebnisse kursiv in Zeile 5 darunter ergänzt).
Mit Index/Vergleich fällt mir keine Lösung ein, aber vielleicht denke ich falsch - daher frage ich lieber nach.
Vielen Dank noch einmal vorab für Eure Hilfe und viele Grüße
Joerschi
Anzeige
AW: mit INDEX() ...
23.12.2016 11:42:04
...
Hallo Joerschi,
... so: =SUMMENPRODUKT(ISTZAHL(VERGLEICH(INDEX($B:$B;SPALTE(B1)):INDEX($H:$H;SPALTE(B1));$J4:$R4;))*1)
und Formel nach rechts kopieren.
Gruß Werner
.. , - ...
AW: Ergänzungsfrage für zusätzliche Auswahl Datensätze
23.12.2016 11:43:46
Daniel
Hi
probier mal das in Spalte U4, dann nach rechts ziehen:
=SUMMENPRODUKT(1*(ZÄHLENWENN(INDEX($B$2:$H$5;SPALTE(A1);0);$J$4:$R$4)>0))
Gruß Daniel
AW: ja, mit ZÄHLENWENN() geht es auch ...
23.12.2016 11:51:16
...
Hallo David,
... allerdings reicht dann schon: =SUMMENPRODUKT(ZÄHLENWENN(INDEX($B$2:$H$5;SPALTE(A1);0);$J$4:$R$4))
Gruß Werner
.. , - ...
AW: ja, mit ZÄHLENWENN() geht es auch ...
23.12.2016 11:59:07
Joerschi
Hallo Werner, Hallo Daniel,
besten Dank Euch beiden.
Ich habe mich jetzt für die letztgenannte Formel von neopa entschieden, welche mit Zählenwenn arbeitet. Finde ich sehr übersichtlich und begreifbar :-)
Falls es nicht nervt, hätte ich eine weitere Ergänzungsfrage:
Angenommen in Spalte I2:I5 trägt man noch den Buchstaben für eine Bedingung (j/n) je Ausgangsdatensatz ein.
In der Lösungsformel sollen jetzt nur dort Ergebnisse errechnet werden, wo die Bedingung = "J" ist.
Wie könnte man das sinnvoll in die Lösung 
=SUMMENPRODUKT(ZÄHLENWENN(INDEX($B$2:$H$5;SPALTE(A1);0);$J$4:$R$4))

einbinden?
(optimalerweise lassen sich mit der "Bedingungsstruktur" dann noch mehr Bedingungen festlegen, welche je Datensatz erfüllt sein müssen um eine Ergebnis anzuzeigen)
Herzliche Grüße
Joerschi
Anzeige
AW: nutze dazu auch INDEX() ...
23.12.2016 12:09:16
...
Hallo Joerschi,
... als Bedingungserweiterung für SUMMENPRODUKT() analog wie ich es schon mit der VERGLEICH()-Formel getan han. Muss jetzt allerdings erst mal offline gehen. Später dann noch mal.
Gruß Werner
.. , - ...
AW: ja, mit ZÄHLENWENN() geht es auch ...
23.12.2016 13:00:13
Daniel
Hi
schau dir mal ZählenWenns an.
diese Funktion ist neu ab Excel 2007 und kann mehrer Bedingungen verarbeiten.
Gruß Daniel
AW: mit ZÄHLEWENN() wie bereits angedeutet, ...
23.12.2016 15:21:38
...
Hallo Joerschi,
... würde ich das mit einer Kombination aus dem ursprünglichen Lösungsansatz von David und INDEX() berechnen. In nachfolgender Formel hab ich nun auch die zuletzt noch vorhandenen überflüssigen $ entfernt und die Auswertungsformel für weitere Datensätze beritgestellt.
=SUMME(INDEX((ZÄHLENWENN(INDEX($B:$H;SPALTE(B1););$J$4:$R$4)*(INDEX($I:$I;SPALTE(B1))="j"));))
Gruß Werner
.. , - ...
Anzeige
AW: mit ZÄHLEWENN() wie bereits angedeutet, ...
23.12.2016 18:49:02
Joerschi
Hallo Werner,
vielen Dank vorab.
Werde es mir bis kommende Woche anschauen und Dir Feedback geben (Weihnachtsstress...)
Danke nochmal und liebe Grüße
Joerschi

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige