Microsoft Excel

Herbers Excel/VBA-Archiv

Listen vergleichen

Betrifft: Listen vergleichen von: simonek
Geschrieben am: 26.08.2014 09:05:49

Hallo liebe Excel Profis,
Ich habe in Spalte B Artikelnummer Spalte C den dazugehörigen Artikeltext vom Juli.
Spalte E Artikelnummer und die dazugehörige Artikelbezeichnung in Spalte F Auswertung August.
Jetzt hätte ich gerne in Spalte G die Artikel mit Artikeltext ( Spalte ). Was nicht gleich sind. ( sind neu dazu gekommen oder gibt es nicht mehr.)

Wäre sowas per Makro oder Formel möglich?
Wäre super wenn ihr mir dabei hilft.

LG Simone

  

Betrifft: nur in einer Spalte von: WF
Geschrieben am: 26.08.2014 09:22:38

siehe unsere:
http://excelformeln.de/formeln.html?welcher=84

Salut WF


  

Betrifft: AW: nur in einer Spalte von: simonek
Geschrieben am: 26.08.2014 09:30:05

Hallo Danke für deine Hilfe aber wie bekomme ich Artikelnummer und Artikeltext in die Spalte

LG simone


  

Betrifft: Fehlende und neue Artikel auflisten von: NoNet
Geschrieben am: 26.08.2014 10:07:12

Hallo Simone,

hier ein Beispiel gemäß WFs Lösungsvorschlag : Die Artikelbezeichnung kannst Du per SVERWEIS() ermitteln :

BCDEFGHIJKL
1
2
3
4
5
6
7
8
9
10
11

Funktionen im Tabellenblatt
Zelle Formel 
I3   =WENN(H3>0;SVERWEIS(H3;$B$3:$C$99;2;0);"") 
I4   =WENN(H4>0;SVERWEIS(H4;$B$3:$C$99;2;0);"") 
I5   =WENN(H5>0;SVERWEIS(H5;$B$3:$C$99;2;0);"") 
I6   =WENN(H6>0;SVERWEIS(H6;$B$3:$C$99;2;0);"") 
I7   =WENN(H7>0;SVERWEIS(H7;$B$3:$C$99;2;0);"") 
I8   =WENN(H8>0;SVERWEIS(H8;$B$3:$C$99;2;0);"") 
I9   =WENN(H9>0;SVERWEIS(H9;$B$3:$C$99;2;0);"") 
I10   =WENN(H10>0;SVERWEIS(H10;$B$3:$C$99;2;0);"") 
I11   =WENN(H11>0;SVERWEIS(H11;$B$3:$C$99;2;0);"") 
L3   =WENN(K3>0;SVERWEIS(K3;$E$3:$F$99;2;0);"") 
L4   =WENN(K4>0;SVERWEIS(K4;$E$3:$F$99;2;0);"") 
L5   =WENN(K5>0;SVERWEIS(K5;$E$3:$F$99;2;0);"") 
L6   =WENN(K6>0;SVERWEIS(K6;$E$3:$F$99;2;0);"") 
L7   =WENN(K7>0;SVERWEIS(K7;$E$3:$F$99;2;0);"") 
L8   =WENN(K8>0;SVERWEIS(K8;$E$3:$F$99;2;0);"") 
L9   =WENN(K9>0;SVERWEIS(K9;$E$3:$F$99;2;0);"") 
L10   =WENN(K10>0;SVERWEIS(K10;$E$3:$F$99;2;0);"") 
L11   =WENN(K11>0;SVERWEIS(K11;$E$3:$F$99;2;0);"") 

MATRIX-Funktionen im Tabellenblatt :
ZelleFormel
H3   {=INDEX(B$3:B$99;KKLEINSTE(WENN(ZÄHLENWENN(E$3:E$99;B$3:B$99)=0;ZEILE($1:$97));ZEILE()-2)
K3   {=INDEX(E$3:E$99;KKLEINSTE(WENN(ZÄHLENWENN(B$3:B$99;E$3:E$99)=0;ZEILE($1:$97));ZEILE()-2)

Tabelle eingefügt mit Syntaxhighlighter 4.15

Du musst nur die Funktionen in H3, I3, K3 und L3 eingeben und dann nach unten kopieren.
Beachte bitte, dass die Funktionen in H3 und K3 ohne die geschweiften Klammern { } eingegeben werden müssen, dafür jedoch mit Strg+Shift+Enter bestätigt werden müssen (da MATRIX-Funktionen !)

Salut, NoNet


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: simonek
Geschrieben am: 26.08.2014 12:00:20

Hallo super danke:-)

Aber mein PC schafft das Matrix Funktion nicht da es 12000 Zeilen sind. Ist daß normal ?

LG simone


  

Betrifft: überdimensionales Datenvolumen von: WF
Geschrieben am: 26.08.2014 12:16:36

Hi,

darum steht in meinem obigen link unten eine Hilfsspaltenlösung für große Bereiche.

WF


  

Betrifft: AW: überdimensionales Datenvolumen von: simonek
Geschrieben am: 26.08.2014 12:23:12

Hallo ich habe deine formeln einfach übernommen. Die ersten 10 Zeilen gehen sehr gut. Aber dann bleibt es hängen :-(
Was kann ich denn machen ?
LG simone.


  

Betrifft: 12000 Zeilen sind zu viel für MATRIX-Funktion von: NoNet
Geschrieben am: 26.08.2014 12:22:20

Hallo Simone,

Aber mein PC schafft das Matrix Funktion nicht da es 12000 Zeilen sind. Ist daß normal ?

Ja, es ist normal, dass die MATRIX-Funktion den PC bei 12.000 Zeilen in die Knie zwingt und Excel "hängt" (bzw. sehr lange rechnet). Für solch große Tabellen sind MATRIX-Funktionen aus Performance-Gründen ungeeignet !

Verwende stattdessen diese Funktion, jetzt kannst Du die Zeilen über die Spalte "Entfallen" oder "Neu" filtern und woanders hinkopieren (oder in einem anderen Blatt den Spezialfilter verwenden) :

BCDEFGH
1
2
3
4
5
6
7
8
9
10
11

Funktionen im Tabellenblatt
Zelle Formel 
D3   =WENN(ISTFEHLER(VERGLEICH(B3;F:F;0));"x";"") 
D4   =WENN(ISTFEHLER(VERGLEICH(B4;F:F;0));"x";"") 
D5   =WENN(ISTFEHLER(VERGLEICH(B5;F:F;0));"x";"") 
D6   =WENN(ISTFEHLER(VERGLEICH(B6;F:F;0));"x";"") 
D7   =WENN(ISTFEHLER(VERGLEICH(B7;F:F;0));"x";"") 
D8   =WENN(ISTFEHLER(VERGLEICH(B8;F:F;0));"x";"") 
D9   =WENN(ISTFEHLER(VERGLEICH(B9;F:F;0));"x";"") 
D10   =WENN(ISTFEHLER(VERGLEICH(B10;F:F;0));"x";"") 
D11   =WENN(ISTFEHLER(VERGLEICH(B11;F:F;0));"x";"") 
H3   =WENN(ISTFEHLER(VERGLEICH(F3;B:B;0));"x";"") 
H4   =WENN(ISTFEHLER(VERGLEICH(F4;B:B;0));"x";"") 
H5   =WENN(ISTFEHLER(VERGLEICH(F5;B:B;0));"x";"") 
H6   =WENN(ISTFEHLER(VERGLEICH(F6;B:B;0));"x";"") 
H7   =WENN(ISTFEHLER(VERGLEICH(F7;B:B;0));"x";"") 
H8   =WENN(ISTFEHLER(VERGLEICH(F8;B:B;0));"x";"") 
H9   =WENN(ISTFEHLER(VERGLEICH(F9;B:B;0));"x";"") 
H10   =WENN(ISTFEHLER(VERGLEICH(F10;B:B;0));"x";"") 

Tabelle eingefügt mit Syntaxhighlighter 4.15

Gruß, NoNet


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: Daniel
Geschrieben am: 26.08.2014 12:28:44

Hi
ja, das ist normal.
12.000 Zeilen sind für in Massen eingestzte Matrixformeln, die zudem auch noch rechenintensive ZählenWenn-Funktionen enthalten, einfach zu viel.

nur mal zum verdeutlichen:
ein ZählenWenn(A1:A12000;"x") musst 12.000 mal den Wert X mit einer der Zellen vergleichen.
ein ZählenWenn(A1:A12000;B1:B12000) so wie in deiner Matrixformel vergleicht jeden Wert aus B mit jedem Wert aus A, also 12.000 * 12.000 = 144.000.000 Zellvergleiche und von diesen ZählenWenns hast du viele in der Tabelle.

Hier mal eine Lösungsbeschreibung, wie es bei 12.000 Artikeln funktionieren könnte:
Ist geschieben für die Beispieltabelle von NoNet, aber mit einem Formelwerk, welches grosse Datenmengen verarbeiten kann.

1. die beiden Tabellen (B:C und E:F) müssen nach der Artikelnummer aufsteigend sortiert sein.

2. mit folgender Formel in Spalte D prüfst du, welche Werte aus Juli nicht mehr im August vorkommen und kennzeichnest diese mit der Zeilennummer, hierzu folgende Formel in D3 und nach unten ziehen:
=Wenn(SVerweis(B3;$E$3:$E$12000;1;1)=B3;"";Zeile();"")

3. mit der gleichen Formel prüfst du in Spalte G die Augusttabelle, welche Werte hinzugekommen sind:
=Wenn(SVerweis(E3;$B$3:$B$12000;1;1)=E3;"";Zeile();"")

4. um jetzt die entfallenen / neuen Artikel zu sehen, kannst du in Spalte D / G nach "nicht leer" filtern.

Wenn die Liste per Formel erstellen willst, kannst du so vorgehen (Formeln eintragen und soweit nach unten ziehen wie benötigt):
Formel in H3: =Index(B:B;KKleinste(D$3:D$12000;Zeile(A1))
Formel In I3: =Wenn(IstFehler(H3);"";SVerweis(H3;B$3:C$12000;2;1))
Formel in K3: =Index(E:E;KKleinste(G$3:G$12000;Zeile(A1))
Formel In L3: =Wenn(IstFehler(K3);"";SVerweis(K3;E$3:F$12000;2;1))

Gruß Daniel


  

Betrifft: Empfehlung von: WF
Geschrieben am: 26.08.2014 12:57:26

Hi,

Deine Beiträge sind OK.

ABER
investiere 5 Sekunden in einen eigenen, aussagekräftigen Betreff.

AW: "Betreffwiederholung" ist primitiv/langweilig und bei einem langen thread liest Dich keiner.

WF


  

Betrifft: AW: Antwort auf Empfehlung von: Daniel
Geschrieben am: 26.08.2014 13:59:08

Hi
kann man machen, aber:
1. in der Beitragsliste ist es besser, wenn du die Überschrift wiederholst, weil du nur dann erkennst, zu wechem Tread der neue Beitrag gehört. Wenn du jedesmal eine neue Überschrift ersetllst, verliert man hier die Übersicht.

2. in der Archiv-Sicht ist die Überschrift ebenfall obsolet, weil hier dann alle Beiträge vollständig gelistet werden.

Ausserdem ist Herber das einzige Forum mit der Baumstruktur (und nur in dieser ist das Ändern der Überschrift sinnvoll), wenn man in mehreren Foren unterwegs ist, vergisst man das manchmal.

Gruß Daniel


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: simonek
Geschrieben am: 26.08.2014 13:24:46

Hallo daniel,

Leider kommt eine Fehlermeldung
#Zahl!
h3
Index(B:B;KKleinste(D$3:D$12000;Zeile(A1))

hast es was mir A1 zu tun?

LG simone


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: Daniel
Geschrieben am: 26.08.2014 15:26:55

HI
da fehlt jetzt noch am Schluss eine Klammer.
Entscheidend ist dann, wieviele Zahlen in Spalte D stehen (erzeugt durch die Formel aus 2.
wobei die dann korrekterweise lauten muss: =Wenn(SVerweis(B3;$E$3:$E$12000;1;1)=B3;"";Zeile())

die Formel aus H3 zeigt jetzt lückenlos alle Zellen aus Spalte B, bei denen in Spalte D die Zeilennummer in der selben Zeile steht.
Wenn du mehr Formeln in Spalte H hast als Zahlen in Spalte D stehen, zeigen die überzähligen Formeln aus H den Fehler #Zahl! (das ist aber auch erstmal OK so).

nochmal die grosse Idee dahinter, vielleicht kannst dir dazu dann die Formeln selber erstellen:
1. in Spalte D: prüfe, ob die Artikelnummer aus B auch in Spalte E vorkommt. Wenn ja, schreibe den Leerstring, wenn nein, schreibe die Zeilennummer
2. in Spalte H: liste von Spalte B alle Zeilen, bei denen in Spalte D eine Zahl steht.

Gruß Daniel


  

Betrifft: formel prüfen von: simonek
Geschrieben am: 26.08.2014 17:44:54

Hallo daniel,
Ich werde nochmal alle formeln durchgehen. Danke

LG simone


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: Adis
Geschrieben am: 26.08.2014 15:28:03

Hallo

gibt es eine kleine Beispiel Datei mit wenigen Beispieldaten? Damit ich das Problem verstehe.
Ich würde bei der Datenmenge an eine VBA Makro Lösung denken. Die halte ich für effektiver.

Dazu waere ein kleines Beispiel gut damit ich sehen kann wieviel Spalten und Zeilen es gibt und wonach genau gesucht wird.
Der genau Suchlauf per Makro ist dann ein anderes Thema. Waere gut eine Bildhafte Vorstellung von der Aufgabe zu haben.

Gruss Adis


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: simonek
Geschrieben am: 26.08.2014 17:43:45

Hallo Adis,
Genau so wie Nonet um 10:07:12 dir Tabelle hereingestellt hat, wäre richtig nur mit ca. 10000 Zeilen
LG simone


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: Daniel
Geschrieben am: 26.08.2014 19:34:00

Hi
Makrolösung geht auch, man muss dann aber schon etwas tiefer in die VBA-Trickkiste greifen.
Hier würde sich die Verwendung des Dictionary-Objektes anbieten, um das Vorhandensein in beiden Listen zu prüfen.

ist sehr schnell und eine Sortierung wie bei den Formeln ist auch nicht zwingend erforderlich.
aber alt etwas "theoretischer":

Sub testDictionary()
Dim arrMo1
Dim arrMo2
Dim arrEntf
Dim arrNeu
Dim arrK
Dim arrI
Dim dic As Object
Dim z As Long
Dim zN As Long
Dim zE As Long
arrMo1 = Range(Cells(3, 2), Cells(3, 3).End(xlDown)).Value
arrMo2 = Range(Cells(3, 5), Cells(3, 6).End(xlDown)).Value

Set dic = CreateObject("Scripting.Dictionary")

For z = 1 To UBound(arrMo1, 1)
    dic(arrMo1(z, 1) & "|" & arrMo1(z, 2)) = 1
Next
For z = 1 To UBound(arrMo2, 1)
    dic(arrMo2(z, 1) & "|" & arrMo2(z, 2)) = Val(dic(arrMo2(z, 1) & "|" & arrMo2(z, 2))) + 2
Next

arrK = dic.Keys
arrI = dic.Items

ReDim arrEntf(1 To UBound(arrMo1, 1), 1 To 2)
ReDim arrNeu(1 To UBound(arrMo2, 1), 1 To 2)


For z = 0 To UBound(arrK)
    Select Case arrI(z)
        Case 1
            zE = zE + 1
            arrEntf(zE, 1) = Split(arrK(z), "|")(0)
            arrEntf(zE, 2) = Split(arrK(z), "|")(1)
        Case 2
            zN = zN + 1
            arrNeu(zN, 1) = Split(arrK(z), "|")(0)
            arrNeu(zN, 2) = Split(arrK(z), "|")(1)
        Case Else
    End Select
Next

Cells(3, 8).Resize(UBound(arrEntf, 1), UBound(arrEntf, 2)).Value = arrEntf
Cells(3, 11).Resize(UBound(arrNeu, 1), UBound(arrNeu, 2)).Value = arrNeu


End Sub
Alternativ kann man natürlich auch die von mir vorgeschlagene Formellösung per VBA nachprogrammieren.
Das ist dann vielleicht nicht ganz so schnell, aber immer noch schnell genug und etwas weniger "abstrakt" (und leichter im Einzelstep zum testen weils keine Schleifen gibt ;-) ):
Sub testFormel()
Dim Z1 As Long
Dim Z2 As Long

Z1 = Cells(Rows.Count, 2).End(xlUp).Row
Z2 = Cells(Rows.Count, 5).End(xlUp).Row

Range("B3:C" & Z1).Sort key1:=Range("B3"), order1:=xlAscending, Header:=xlNo
Range("E3:F" & Z1).Sort key1:=Range("E3"), order1:=xlAscending, Header:=xlNo

With Range("D3:D" & Z1)
    .FormulaR1C1 = "=IFERROR(IF(VLOOKUP(RC[-2],R3C5:R" & Z2 & "C5,1,1)=RC[-2],"""",ROW()),ROW()) _
"
    .Formula = .Value
End With

With Range("G3:G" & Z2)
    .FormulaR1C1 = "=IFERROR(IF(VLOOKUP(RC[-2],R3C2:R" & Z1 & "C2,1,1)=RC[-2],"""",ROW()),ROW()) _
"
    .Formula = .Value
End With

With Range("H3").Resize(WorksheetFunction.Count(Columns(4)), 2)
    .Columns(1).Formula = "=INDEX(B:B,SMALL($D$3:$D$" & Z1 & ",ROW(A1)))"
    .Columns(2).FormulaR1C1 = "=VLookUp(RC[-1],R3C2:R" & Z1 & "C3,2,1)"
    .Formula = .Value
End With

With Range("K3").Resize(WorksheetFunction.Count(Columns(7)), 2)
    .Columns(1).Formula = "=INDEX(E:E,SMALL($G$3:$G$" & Z1 & ",ROW(A1)))"
    .Columns(2).Formula = "=VLookUp(RC[-1],R3C5:R" & Z1 & "C6,2,1)"
    .Formula = .Value
End With
Range("D:D,G:G").ClearContents
End Sub
Gruß Daniel


  

Betrifft: AW: Fehlende und neue Artikel auflisten von: simonek
Geschrieben am: 27.08.2014 09:07:34

Hallo Daniel,

Einfach nur " PERFEKT"
Danke danke für alles

LG Simone


 

Beiträge aus den Excel-Beispielen zum Thema "Listen vergleichen"