Office: Schwieriges Such- und Anzahlproblem in Excel

Ich hoffe jemand kann mir bei einem schwierigerem Suchproblem mit Excel helfen:

Also

ich habe eine Excel Datei mit verschiedenen Suchbegriffen z.B. ABC, ABD, ACD etc.

Jetzt soll Excel in einer zweiten Datei die ganze Liste der Suchbegriffe abarbeiten, also nach ABC, ABD;... suchen und gleichzeitig mitzählen, wie oft dieser Begriff vorkommt.

In einer dritten Datei soll dann abgelegt werden: 1 Spalte: Suchbegriff ABC,...... 2 Spalte: Anzahl gefunden: wie oft in der zweiten Excel Datei der Suchbegriff vorgekommen ist.

Ich hoffe es hat jetzt jeder verstanden: 1 Excel Datei: eine Reihe von Begriffen nach der Excel sucht, sozusagen eine Indexliste oder so.

2 Excel Datei: Hier wird nach den Begriffen aus der 1 Exceldatei gesucht und es soll gleichzeitig gezählt werden, wie oft dieser vorkommt (nur in 1 Spalten suchen, nicht das ganze Blatt). In einer 3 Excel Datei soll dann abgelegt werden, nach was gesucht wurde und wie oft vorgekommen).

Das ganze soll dann noch wie folgt aufgerufen werden:

Alle 3 Dateien liegen in einem Verzeichnis. Ich offne Datei 3 und es soll dann alles automatisch losgehen.

Office Version 2003

Danke schon mal für eure Hilfe.

 

Hallo! Obwohl sich mir der Unterschied zwischen deiner zweiten und dritten Datei nicht erschließt (in beiden soll gesucht und gezählt werden), hier vielleicht ein Ansatz:

=SUMMENPRODUKT(N('[datei1.xls]Tabelle1'!B1:B1000="ABC"))

zählt alle ABC aus B1:B1000 von Tabelle1 der ersten Datei.

Hilft das schon zum weiterbasteln?

 

die suchfunktion funktioniert schon mal teilweise einwandfrei, d.h. ich habe sie so angepasst, das automatsich eine datei geöffnet wird und darin nach dem begriff z.B. "ABC in Spalte B gesucht wird und die anzahl zurückgeliefert

aber ich möchte keinen festen Wert als suchwert, im beispiel von dir "ABC" haben, sondern ich nehme mir aus einer excel datei, aus einer spalte z.b. spalte A, solange suchbegriffe, bis die ganze spalte abgearbeitet wurde.

z.B. steht in dieser excel tabelle:
Spalte A

ABC
ABCD
ABD
....
nach diesen einzelnen begriffen soll jetzt gesucht werden und zurückgeliefert soll werden der begriff in einer extra zelle z.b. spalte C und gleiche zeilenzahl aber spalte D dann die anzahl.
als beispiel:

excel datei 1, tabelle 1:

Spalte A

ABC
ABD
ABDD
ABDF
.
.

excel datei 2, tabelle 1:
Spalte A

ABD
ABC
ABD
ABDD
ABD
ABD
ABDC
.
.
.


excel datei 3, tabelle1:

Spalte A______Spalte B
ABC____________3
ABD____________5
ABDD__________ 4
ABDF___________0

 

Hallo!

Für das SummenProdukt braucht die Quelldatei nicht geöffnet zu sein. In dem Fall reicht auch ZÄHLENWENN.
Wenn du den Suchbegriff variabel gestalten möchtest, dann passe die Formel an:

=SUMMENPRODUKT(N('[datei1.xls]Tabelle1'!B1:B1000=A1))

So sucht die Formel nach dem Wert, der in A1 steht.

 

wie mache ich es aber, dass er automatisch die verschiedenen suchbegriffe aus spalte A datei 1 sucht, bis in datei 1 alle suchbegriffe abgearbeitet wurden, geht bestimmt nur mit VBA oder?

zur verdeutlichung nochmal:

datei 1, tabelle1:

spalte A

ABC
ABD
ABDD
ABBD

er nimmt jetzt ABC und sucht in datei 2 dannach und gibt in datei 3 den wert nach welchen gesucht wurde und in der zelle daneben die anzahl wieder. jetzt nimmt er ABD sucht in datei 2 dannach und liefert das ergebnis eine zeile weiter drunten.... das ganze macht er solange bis in datei 1 spalte A nichts mehr steht.

 

Ehrlich gesagt durchschaue ich dein Anliegen immer noch nicht...
Wenn es dir darum geht, alle Einträge aus Datei1 ohne Duplikate aufzulisten, dann nutze dafür den Spezialfilter oder schau mal hier, wie es auch mit Formeln lösbar ist. (Dafür muss die Quelldatei aber höchstwahrscheinlich geöffnet sein.)

 

nein nicht auflisten:

also nochmal hoffe es wird klar:

in datei 2 stehen wahllos begriffe in einer spalte A:

Spalte A

ABC
ABC
ABD
ABC
ABF
ABD
ABC
AB8
8
ABD
ABC
7
7

in Datei 1 definiere ich jetzt in einer spalte eine reihe von suchbegriffen, nach welchen in datei 2 in spalte A gesucht werden soll.

nach all diesen suchbegriffen, welche in Datei 1 in einer spalten stehen soll hintereinander gesucht werden, also zuerst der 1 suchbegriff, dann der 2 suchbegriff... bis er die ganzen suchbegriffe in Datei 1 abgearbeitet hat.

er soll den begriff solange in datei 2 suchen, bis er das ende der spalte erreicht hat.

dann soll er in einer 3.ten datei zurückliefern:

Spalte A Zeile 1:erster suchbegriff Spalte B Zeile 1: anzahl wie oft dieser in in der spalte vorkommt.

Spalte A Zeile 2: nächsten suchbegriff Spalte B Zeile 2: anzahl wie oft dieser vorkommt

....

das ganze soll wer solange machen, bis alle suchbegriffe, welche in datei 1 in einer spalte stehen, abgearbeitet wurden.

datei 1 ist sozusagen ein suchbegriffeindex
datei 2 enthält in einer spalte unsortierte werte
datei 3 soll enthalten: suchbegriff daneben anzahl wie oft dieser in datei 2 vorkommt und das zeilenweise für einen suchbegriff

 

Dann hast du's doch schon längst:
Schreibe in Datei3 SpalteA Formeln in der Art
=[Datei1.xls]Tabelle1!A1
=[Datei1.xls]Tabelle1!A2
=[Datei1.xls]Tabelle1!A3
usw.

Und in Datei3 SpalteB Formeln in der Art
=SUMMENPRODUKT(N([Datei2.xls]Tabelle1!A$1:A$1000=A1))
=SUMMENPRODUKT(N([Datei2.xls]Tabelle1!A$1:A$1000=A2))
=SUMMENPRODUKT(N([Datei2.xls]Tabelle1!A$1:A$1000=A3))
usw.

 

guten morgen,

das problem an deiner lösung ist nur, dass er jetzt alle suchbegriffe aus datei1 in datei3 einträgt und hinter suchbegriffen welcher er in datei 2 findet, schreibt er anzahl 0.

aber ich möchte die mit anzahl 0 nicht drin haben, sondern nur die, wo er auch wirklich was gefunden hat.

klar könntem an es jetzt sicher auch so machen, dass er erst alle einträgt und dann die wo 0 steht wieder löscht, aber das ist ja mehr aufwand als gleich die mit 0 anzahl nicht zu nehmen oder?

edit: das problem an deiner lösung ist auch, ich habe im suchindex (datei1) bestimmt 1000 begriffe stehen, aber im es kommen in datei 2 vielleicht nur 150-200 vor, also sind 80 % leer, das ganze wird dann sehr sehr unübersichtlich in datei3, wenn dort fast alles anzahl 0 steht.

 

Hallo!

Nein, ganz im Gegenteil:
Füge deiner Liste in Datei eine Überschrift hinzu.
Markiere die Liste und wähle Menü Daten > Filter > Autofilter.
Mit diesem Filter kannst du alle Nullwerte heraus filtern.

Das ist wesentlich einfacher als eine Filterabfrage auf eine andere Datei durchzuführen.

 

so das ganze funktioniert jetzt, es besteht aber nur noch ein kleines problem.

führe ich jetzt das ganze aus, braucht es ca. 5 minunten bis excel alles abgearbeitet hat.

das ganze ist auch nicht verwunderlich:

der suchindex besteht aus ca. 2000 wörtern und die spalte in der dann gesucht wird wiederrum aus 3000 zeilen.

also muss er 2000 mal 3000 zeilen durchsuchen, das dauert, da muss es doch aber ne schnellere lösung geben, weil 5 minunten warten ist untragbar.

 

Hallo!

Tja, die Daten müssen nun mal durchsucht werden, daran führt kein Weg vorbei...
Aber 5min klingt tatsächlich sehr lang.
Hast du den Bereich der Summenprodukt-Formel vielleicht unnötig groß angegeben?

 

so noch ne kleine frage zu folgendem code:

=WENN(ISTLEER(A42);B42=" "; (SUMMENPRODUKT(N('D:\[var.xls]Tabelle1'!$C$13:$C$3000=A42))))

er soll einfach wenn in A nichts mehr drin stehet, in B nichts reinschreiben, ansonsten das summenprodukt ausführen.

er schreibt aber dauernd 0 in diese zellen rein statt gar nix, wieso das ?

 

und gleich noch eine frage :)

ich habe jetzt sagen wir in spalte a, b und c von zeile 10 bis zeile 100 was stehen. dann verändere ich meinen suchindex und nun stehet in spalte a, b und c bis zeile 150 was.

wie kann ich jetzt eine bedingte formatierung durchführen (rand senkrecht, also spaltenrand), so dass er einmal bis zeile 100 rand zieht und einmal bis zeile 150).

hier mal ein codebeispiel:

Sub rand()

Dim intRow As Integer
intRow = 10

Do Until IsEmpty(Cells(intRow, 1))

Range(Cells(intRow, 1), Cells(intRow, 8)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B61").Select
intRow = intRow + 1
Loop
End Sub

funktioniert :),

ist doch net so schwer vba^^

 

Hallo!

Zu deiner ersten Frage:

=WENN(A42="";"";(SUMMENPRODUKT(N('D:\[var.xls]Tabelle1'!$C$13:$C$3000=A42))))

Zu deiner zweiten Frage:

Der Anfang muss lauten

Code:

Do Until Cells(intRow, 1) = ""

Aber: Wenn du eine bedingte Formatierung suchst, warum nutzt du dann VBA und nicht die bedingte Formatierung selbst?