Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

10.07.2025 18:29:52

Andreas

Ihr ganz lieben Menschen hier in diesem Forum. Schon oft habe ich Hilfe gebraucht und immer wieder erhalten. Auch diesmal hoffe ich sehr, sehr auf Eure Unterstützung.

Zum Problem:
Ich möchte in einer Datenliste von Aufträgen die Firmenbezeichnungen vereinheitlichen. Bekannt sind dazu die Firmen-IDs. Diese stehen in Spalte A. Die Firmen-IDs wiederholen sich sehr oft (immer dann, wenn mehrere Aufträge von dieser Firma bearbeitet wurde). In Spalte B stehen die Firmenbezeichnungen. Hier kann durch Tippfehler oder Abkürzungen manchmal die Eingabe variieren. Ich möchte aber diese Bezeichnungen vereinheitlicht in Spalte D ausgeben (s. Beispieltabelle: https://www.herber.de/bbs/user/178060.xlsx)
Dazu soll für jede Firmen-ID die Firmenbezeichnung ermittelt werden, die am häufigsten in Spalte B genannt ist. Dies soll dann in Spalte D für die jeweilige ID ergänzt werden.
Alle Firmen-IDs mit den jeweilig ermitteltem häufigsten Eingabewert soll darüber hinaus in einem eigenen Datenblatt "Firmenstammdat" aufgelistet werden.
Ich möchte gern eine Makro-Lösung, weil das Makro in andere Makro-Prüfprozeduren eingebettet werden soll.
Real besteht meine Tabelle aus 16.000 Zeilen. Die Prozedur soll aber nur einmal durchgeführt werden. Insofern ist eine lange Berechnungszeit zumutbar. Vielmehr wäre mir wichtig, dass ich den VBA-Code verstehen kann, um ggf. Anpassungen auf die reale Tabelle vornehmen zu können.

Ich hoffe ich konnte mein Problemgut beschreiben. Danke für das bis hierhin lesen. Das ist schon toll!

Mir fehlt tatsächlich ein grundlegender Ansatz. Ich weiß nicht welche Prozeduren und Schleifen hier geeignet sind, um dieses Problem anzugehen. Insofern kann ich auch keinen Lösungsansatz präsentieren. Ich weiß, dass es daher sehr, sehr viel verlangt ist, so um Hilfe zu bitten. Ich probiere es trotzdem, weil ich weiß dass die Hilfsbereitschaft hier riesig ist. Ich würde mich so sehr freuen, wenn mir von Euch unter beide Arme gegriffen werden würde. Dafür schon hier an dieser Stelle mein herzlicher Dank.
Das wäre so toll wenn ich hier mit Eurer Hilfe weiterkommen würde.

Liebe Grüße, Andreas

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

10.07.2025 18:41:23

cysu11

Hallo Andreas,

ihr habt doch sicher ein Warenwirtschaftssyste etc. mit den Kunden ID´s. Zieh von dort einmal die ID´s samt Kundennamen ab und per SVERWEIS, dann zuordnen! ;)
Alles andere ist zu kompliziert bis gar nicht möglich, denn woher soll Excel wissen, wenn er bei Müller sucht ob er nach Mueller GmbH, Müller GmbH, oder Müller suchen soll und dann das häuftigste Vorkommen! Aus euren System abziehen und dann hast du eine saubere Sache! :)

LG, Alexandra

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

10.07.2025 19:18:14

schauan

Hallo Alexandra,

es soll doch anhand der ID eine Zuordnung der bei der ID am häufigsten vorkommenden Bezeichnung vorgenommen werden. Ich habe das im Anhang mal beispielhaft als Formellösung ...
https://www.herber.de/bbs/user/178061.xlsx

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

10.07.2025 19:49:31

daniel

Hi
reicht dir eine Ablaufbeschreibung, was du tun musst?
der Recorder sollte dir dann helfen, dieses in ein Makro umzusetzen.
btw: ist die Versionsangabe korrekt? 2007 ist schon sehr alt.

aber egal, du kannst es von Hand so machen, aber auch ein Makro schreiben, dass diese Schritte ausführt.

1. kopiere aus der Tabelle1 die Spalten A und B nach Firmenstammdaten
2. führe das Duplikate-Entfernen mit Spalte A und B als Kriterium durch, so dass du jede Kombinantion aus aus Firmen-ID und Schreibweise nur einmalig hast
3. füge in Spalte C folgende Formel hinzu, die dir zählt, wie oft jede Kombination vorkommt:
=ZählenWenns(Tabelle1!A:A;A2;Tabelle2!B:B;B2)
4. kopiere Spalte C und füge an gleicher Stelle als Wert ein
5. sortiere die Liste nach Spalte A und Spalte C, nach Spalte C dann absteigend, so dass die häufigste Firmenbezeichnung dann oben steht
6. führe dann nochmal mit der ganzen Tabelle das Duplikate-Entfernen durch, mit Spalte A als Kriterium. Hierbei bleibt dann immer die oberste Zeile einer Firmen-ID stehen, durch die Sortierung sollte das die am häufigsten verwendete sein.
7. lösche die Spalte C

Das sieht als Makro dann so aus:

Sub Häufigster_Firmenname1()

With Sheets("Firmenstammdat")

    Sheets("Tabelle1").Range("A:B").Copy .Cells(1, 1)

    With .Cells(1, 1).CurrentRegion.Resize(, 3)

        .RemoveDuplicates Array(1, 2), xlYes

        .Columns(3).FormulaR1C1 = "=CountIfs(Tabelle1!C1,RC1,Tabelle1!C2,RC2)"

        .Columns(3).Formula = .Columns(3).Value

        .Sort Key1:=.Cells(1, 1), order1:=xlAscending, order2:=.Cells(1, 3), order2:=xlDescending, Header:=xlYes

        .RemoveDuplicates 1, xlYes

        .Columns(3).ClearContents

    End With

End With

End Sub

ansonsten, eine reine VBA-Lösung könnte so aussehen.
Hier ist aber mit zwei geschachtelten Dictionarys schon etwas Programmierer-Verständnis erforderlich, dafür sollte die Durchlaufzeit bei großen Datenmengen erheblich schneller sein.

Sub Häufigster_Firmenname2()

Dim arr, erg

Dim dic1, dic2

Dim z As Long

Dim x, y

Dim Anz As Long



Set dic1 = CreateObject("scripting.dictionary")



arr = Sheets("Tabelle1").Cells(1, 1).CurrentRegion.Value

'--- Vorkommen zählen

For z = 2 To UBound(arr, 1)

    If Not dic1.exists(arr(z, 1)) Then

        Set dic2 = CreateObject("scripting.dictionary")

    Else

        Set dic2 = dic1(arr(z, 1))

    End If

    

    dic2(arr(z, 2)) = dic2(arr(z, 2)) + 1

    Set dic1(arr(z, 1)) = dic2

Next

    

    

'--- Ergebnis ermitteln

z = 0

ReDim erg(1 To dic1.Count, 1 To 2)

For Each x In dic1.keys

    z = z + 1

    erg(z, 1) = x

    Set dic2 = dic1(x)

    Anz = 0

    For Each y In dic2.keys

        If dic2(y) > Anz Then

            Anz = dic2(y)

            erg(z, 2) = y

        End If

    Next

Next



'--- Ergebnis in Tabelle

With Sheets("Firmenstammdat")

    .Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContents

    .Cells(2, 1).Resize(UBound(erg, 1), UBound(erg, 2)) = erg

End With

    



End Sub

jetzt kannst du über den Sverweis auf diese Tabelle die am häufigsten Verwendete Bezeichnung zuspielen.

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

11.07.2025 10:50:48

Andreas

Lieber daniel,

vielen herzlichen Dank! Es funktioniert schonmal. Das ist sehr , sehr toll! DANKE! Ich habe das erste Makro (mit Aufzeichnung) gewählt. das Andere übersteigt tatsächlich meine Fähigkeiten so was von.

Ich habe noch zwei kleine Problemchen, von denen ich hoffe das Du mir ggf. nochmal helfen könntest:
1. Dummerweise stehen (anders als in der Beispiel-Tabelle) die Firmen IDs in Spalte B und die Firmennamen in Spalte C von Tabelle1. Hätte ich gewusst, dass ich zu doof bin das Makro anzupassen, dann hätte ich das gleich auch in der Beispieltab. geändert. Ich denke das Problem liegt in der Zählenwenns-Fkt oder im Ausdruck FormulaR1C1. Ich bekomms jedenfalls nicht gelöst den Ausdruck .Columns(3).FormulaR1C1 = "=CountIfs(Tabelle1!C1,RC1,Tabelle1!C2,RC2)" auf die geänderte Situation umzuschreiben. Könntest Du hier nochmal helfen? tut mir leid.

2. Um in der Tabelle 1 die vereinheitlichten Firmennamen in eine zusätzliche Spalte zu schreiben (s. Problembeschreibung von gestern) mache ich mir heute selbst Gedanken. Ich bin optimistisch, dass ich das hinbekomme. Wenn nicht würde ich hier nochmal ein Zeichen geben. okay?

daniel, nochmal vielen lieben Dank bis hierher. Wenn du keine Zeit und Nerven hast, mir nochmal zu helfen, kann ich das gut verstehen. Ich freue mich über deine Unterstützung schon bis jetzt.

Viele Grüße, Andreas

Im Anhang ist...

11.07.2025 11:23:21

Case

Moin Andreas, :-)

... eine Möglichkeit mit VBA und Power Query: ;-)
https://www.herber.de/bbs/user/178064.xlsb

Mit Excel 2007 geht kein Power Query. Eventuell kannst du es dir in einer neueren Version anschauen. PQ Ist prädestiniert für Datenmanipulation solcher Art. ;-)

Da du es nur einmal ausführen willst, habe ich die Dictionary nicht in eine Array umgeschaufelt. Bei mir läuft das mit über 16tausend Datensätzen in einer Sekunde durch. ;-)

Bei häufiger Anwendung - und wenn es noch mehr Daten werden - würde ich es in ein Array packen und auf einen Rutsch ausgeben. ;-)

Servus
Case

AW: Im Anhang ist...

11.07.2025 16:30:35

Andreas

Lieber Case,

auch Dir vielen, vielen Dank! Für mich ist vielleicht deine Lösung keine unmittelbare Alternative, aber sicher hilft es jedem mit einem ähnlichen Problem, der hier in diesem hilfsbereiten Forum nach Antworten sucht. Daher finde ich es total gut, dass du deinen Lösungsansatz hier noch mit präsentierst!

beste Grüße und danke nochmal!

Andreas

AW: Im Anhang ist...

11.07.2025 19:12:05

schauan

Hallöchen,

ich habe hier auch noch eine Version, die zum Einen wegen der verwendeten WorksheetFuncion.Unique erst ab 2021 bzw 365 lauffähig ist und zum Anderen finden sich da Passagen von bzw. ähnlich wie bei Daniel ;-).
Die gesamte Verarbeitung erfolgt im Code - Stichwort Arrays usw. - , es werden lediglich die Firmenstammdaten auf dem entsprechenden Blatt erneuert und in Spalte C dann das Ergebnis ausgegeben.

Option Explicit

Sub NamenBereinigen()

  Dim dict, Key, arr1, arr2, arr3

  Dim iCnt&, kCnt&

  Dim rngA As Range

  'Dictionary instanzieren

  Set dict = CreateObject("Scripting.Dictionary")

  'Daten aus Bereich A:B holen - Zeilen anhand Daten in Spalte A

  With ThisWorkbook.Sheets("Tabelle1")

    Set rngA = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))

  End With

  With rngA

    'eindeutige Liste aus ID und Name erzeugen in arr1 - Funktion EINDEUTIG ab Excel 2021 / 365

    arr1 = WorksheetFunction.Unique(.Resize(, 2))

    ReDim arr2(1 To UBound(arr1))

    'Vorkommen der Kombination aus zwei Spalten zählen - Funktion ZAEHLENWENNS ab Excel 2013 / 365

    For iCnt = 1 To UBound(arr1)

      arr2(iCnt) = WorksheetFunction.CountIfs(.Offset(), arr1(iCnt, 1), .Offset(, 1), arr1(iCnt, 2))

    Next

  End With

  'Maximale Eintraege mittels Dictionary ermitteln und hinterlegen, ID --> key, Max --> Wert

  For iCnt = 1 To UBound(arr1)

    Key = arr1(iCnt, 1)

    If dict.exists(Key) Then

      If dict(Key)  arr2(iCnt) Then dict(Key) = arr2(iCnt)

    Else

      dict.Add Key, arr2(iCnt)

    End If

  Next

  'Array für Ausgabe der Firmenstammdaten dimensionieren

  ReDim arr3(1 To dict.Count, 1 To 2)

  'ermittelte Firmenstammdaten in Array arr3 uebernehmen, wenn die ID aus arr1 (eindeutige Liste)

  'mit dem key von dict und die Anzahl aus arr2 mit dem Wert aus dict uebereinstimmt

  For Each Key In dict.Keys

    Debug.Print Key & ": " & dict(Key) 'Kontrollausgabe

    For iCnt = 1 To UBound(arr1)

     If arr1(iCnt, 1) = Key And arr2(iCnt) = dict(Key) Then

       kCnt = kCnt + 1

       arr3(kCnt, 1) = arr1(iCnt, 1): arr3(kCnt, 2) = arr1(iCnt, 2)

     End If

    Next

  Next

  'Firmenstammdaten uebertragen, zuvor benutzten Bereich um Zelle A1 (1, 1) leeren

  With ThisWorkbook.Sheets("Firmenstammdat")

    .Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContents

    .Cells(2, 1).Resize(UBound(arr3, 1), UBound(arr3, 2)) = arr3

  End With

  'Maxima in Dictionary durch Firmennamen aus Array arr3 ersetzen

  For Each Key In dict.Keys

    For iCnt = 1 To UBound(arr3)

     If arr3(iCnt, 1) = Key Then

       dict(Key) = arr3(iCnt, 2)

       Debug.Print Key & ": " & dict(Key) 'Kontrollausgabe

     End If

    Next

  Next

  'Daten aus Spalte A (ID) in Array uebernehmen

  arr1 = rngA

  'ID durch Firmenname aus Dictionary ersetzen

  For iCnt = 1 To UBound(arr1)

    arr1(iCnt, 1) = dict(arr1(iCnt, 1))

  Next

  'Array in Spalte C (3) ausgeben

  With ThisWorkbook.Sheets("Tabelle1")

    .Cells(2, 3).Resize(UBound(arr1)) = arr1

  End With

End Sub

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

11.07.2025 11:26:33

daniel

HI
in der Formular1C1-Schreibweise wird ein Zellbezug nicht durch die Kombination "Spaltenbuchstabe + Zeilennummer" beschrieben, sondern aus "R" (Row) gefolgt von der Zeilennummer und "C" (Column) gefolgt von der Spaltennummer (A=1, B=2, C=3 usw)
Fehlt die Zahl nach dem "R" oder "C", geht der Bezug auf die selbe Zeile oder Spalte, in welcher auch die Formel steht.
Fehlt das R oder C ganz, dann geht der Bezug auf die ganze Zeile oder Spalte.

ein Tabelle1!C2 in ForumlaR1C1 entspricht also einem Tabelle!1$B:$B in üblicher A1-Addressschreibweise.

damit solltet du dir die Formel an deine Echt-Tabelle anpassen können.
du kannst auch die Formel so wie du es brauchst von Hand in die Zelle schreiben und dir dann im Direktfenster mit: ?Selection.FormulaR1C1 den Formeltext für das Marko anzeigen lassen. Es ist empfehlenswert, dabei alle Zellbezüge, die nicht zwingend Relativ sein müssen, auf Absolut zu setzen ($), da absolute Zellbezüge in der R1C1-Schreibweise einfacher sind.

Gruß Daniel

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

11.07.2025 16:09:25

Andreas

Hallo Daniel,

vielen, vielen Dank!
Deine Erklärung war entscheidend. Damit habe ich es hinbekommen:
.Columns(3).FormulaR1C1 = "=CountIfs(Bericht1!C2,RC1,Bericht1!C3,RC2)"

Mit den anderen "offiziellen" Beschreibungen kam ich nicht zurecht und ich habe wild die falschen Variablen geändert. Jetzt klappt es aber perfekt.
Ich bin Dir sehr, sehr dankbar.

Ich wünsche Dir ein schönes Wochenende! Und danke nochmal!

Andreas

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

10.07.2025 19:20:53

cysu11

Hi schauan,

ja! :) wer lesen kann! :)

LG, Alexandra

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

11.07.2025 10:32:16

Andreas

Hallo schauan,

vielen herzlichen Dank für deine Mühe. Ich mag prinzipiell Formellösungen, weil ich in VBA nicht so gut aufgestellt bin. Aber hier hätte ich diesmal gern eine VBA-Lösung am Ende. Ich finde es trotzdem Klasse, dass Du geantwortet hast. Jeder Schritt und jede Hilfe kann sehr wertvoll sein, wenn man wie ich so leider gar keinen Ansatz hat. Vielen lieben Dank nochmal!

AW: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

11.07.2025 10:28:34

Andreas

Hallo Alexandra,

Danke Dir trotzdem, dass Du Dir Gedanken gemacht hast.

Liebe Grüße, Andreas

Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

Forumthread: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

Forumthread: Häufigste Firmenbezeichnung von gleichen Firmen-IDs finden

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread