Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1848to1852
1848to1852
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Doppelte Werte erweitern

Forumthread: Doppelte Werte erweitern

Doppelte Werte erweitern
23.09.2021 16:41:03
Chris
Hallo Forum,
ich möchte per VBA folgendes lösen:
In Spalte 1 stehen Zahlen. Es kann vorkommen, dass einzelne Zahlen zweimal in der Spalte stehen, nicht immer direkt untereinander.
Sobald einen Zahl zweimal vorkommt, sollte die erste um den String "A", die zweite um den String "B" erweitert werden.
Bsp:
1
2A
3A
2B
4
3B
usw.
Wie löst man dies per VBA?
Schon mal danke
Gruß
Chris
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Doppelte Werte erweitern
23.09.2021 16:56:50
ChrisL
Hi Chris
Geht am einfachsten per Formel.
Im VBA Beispiel kurzfristig die Spalte X als Hilfsspalte missbraucht:

Sub t()
Dim lZ As Long
With ActiveSheet
lZ = .Cells(Rows.Count, 1).End(xlUp).Row
'=WENN(ZÄHLENWENN(A:A;A1)=1;A1;A1&ZEICHEN(ZÄHLENWENN(A$1:A1;A1)+64))
.Range("X1:X" & lZ).Formula = "=IF(COUNTIF(A:A,A1)=1,A1,A1&CHAR(COUNTIF(A$1:A1,A1)+64))"
.Range("A1:A" & lZ) = .Range("X1:X" & lZ).Value
.Range("X1:X" & lZ).ClearContents
End With
End Sub

Anzeige
AW: Doppelte Werte erweitern
23.09.2021 18:54:28
Chris
Hallo ChrisL,
danke, funktioniert wunderbar. Was ist Zeichen 64?
Gruß
Chris
AW: Doppelte Werte erweitern
23.09.2021 19:22:50
ChrisL
Hi
ZÄHLENWENN(A$1:A1;A1) = 1 +64 = 65
ZÄHLENWENN(A$1:A1;A1) = 2 +64 = 66
usw.
Guckst du z.B. hier:
https://www.gaijin.at/de/infos/ascii-ansi-zeichentabelle#ansilow
Nach dem Buchstaben Z kommen dann Sonderzeichen. Darum weist Daniel auf die Limitierung von 26 Buchstaben bei diesem Vorgehen hin.
cu
Chris
Anzeige
AW: Doppelte Werte erweitern
23.09.2021 19:35:42
Daniel
Hi
du kannst diese Methode zur Ermittlung der Buchstaben aber auch in deiner Formel anwenden.
Anstelle von Zeichen(ZählenWenn()+64) dann: WECHSELN(ADRESSE(1;ZählenWenn();4);1;"")
Gruß Daniel
AW: Doppelte Werte erweitern
23.09.2021 17:52:15
Daniel
Hi
da du nach einer reinen VBA-Lösung gefragt hast, probier mal das als alternative zum Formelansatz von Chris:

Sub test()
Dim arr, arr2
Dim dic
Dim z As Long
Set dic = CreateObject("Scripting.dictionary")
With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
arr = .Value
arr2 = .Value
For z = 1 To UBound(arr, 1)
dic(arr(z, 1)) = dic(arr(z, 1)) + 1
arr(z, 1) = arr(z, 1) & Split(Columns(dic(arr(z, 1))).Address(0, 0), ":")(1)
Next
For z = 1 To UBound(arr2, 1)
If dic(arr2(z, 1)) = 1 Then arr(z, 1) = arr2(z, 1)
Next
.Value = arr
End With
End Sub
bei großen Datenmengen (mehrere Tausend Zeilen) dürfte das etwas schneller sein.
außerdem verarbeitet das Makro auch mehr als 26 wiederholungen, mit dem Weg über die Spaltenadressen wird nach Z mit AA weitergemacht.
Gruß Daniel
Anzeige
AW: Doppelte Werte erweitern
23.09.2021 18:56:42
Chris
Hallo Daniel,
danke für das Makro, das ich leider so gar nicht verstehe (funktioniert!)
Kannst du es mir erklären?
Gruß
Chris
AW: Doppelte Werte erweitern
23.09.2021 19:28:30
Daniel
Hi
das Makro nutzt das Dictionary als Methode.
ein Dictionary ist im Prinzip ein eindimensionales array, bei man statt der vorgegebenen Zahlenreihe von 1-x jeden beliebigen Wert als Index verwenden kann.
(dieser Wert wird dann im Dictionary auch sehr schnell gefunden)
somit habe ich über das Dicitonary für jeden Wert aus der Liste eine Zählervariable, die für diesen Wert jedesmal um 1 erhöht wird, wenn die Schleife über den Wert läuft.
aus dem Zählerwert ermittle ich dann den Buchstaben (hier über kunstgriff, dass Spalte 1 = Spalte A ist und ich so aus der Zahl einfach den dazugehörigen Buchstaben ermitteln kann) und hänge den sofort an den Wert hinten dran.
somit kann ich das ganze mit einem einzigen Schleifendurchlauf erledigen.
die zweite schleife ist dann nur deshalb drin, weil du ja haben wolltest, dass das A nicht erscheint, wenn der Wert nur 1x vorkommt.
Das kann ich aber erst wissen, wenn ich einmal vollständig über die Werte drüber gelaufen bin, daher muss ich das am Ende wieder rausnehmen.
und um schneller zu sein, übernehme ich die Zellwerte erstmal in ein array und arbeite direkt mit dem Array, anstatt mit den Zellwerten direkt.
Gruß Daniel
Anzeige
;

Beliebteste Forumthreads (12 Monate)

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