Microsoft Excel

Herbers Excel/VBA-Archiv

Duplikate in Zelle

Betrifft: Duplikate in Zelle von: Therese
Geschrieben am: 08.10.2020 12:28:42

Hallo Zusammen,


ich habe - bedingt durch vorherige Formeln

C2:

=IF(A2<>A1;F2;C1 & "," & F2)


D2:

=IF(A2<>A3;CONCATENATE("";C2;"");"")

Zellen, welche redundante Einträge haben.

Ich möchte diese gern konsolidieren, weiß aber nicht wie. Vielleicht ist mein vorheriges Vorgehen (s.o.) auch schon zu kompliziert.


https://www.herber.de/bbs/user/140727.xlsx


Ziel soll sein eine Formel zu finden, damit ich in Spalte B nur die finale Konsolidierung habe, also Duplikate aus den Zellen (nicht Spalten!) entfernt werden und mit den Unikaten je Profil zusammengeführt werden.

Hoffe das ist verständlich :)

Hat jemand eine Idee?

Betrifft: 2 Varianten (nur xl365 oder xlWeb)
von: lupo1
Geschrieben am: 08.10.2020 12:50:42

a) in Deiner Tabelle:
B2[:B12]: =WENN(A2<>A3;TEXTVERKETTEN(", ";;EINDEUTIG(GLÄTTEN(MTRANS(TEIL(WECHSELN(D2;", ";WIEDERHOLEN(" ";299));SPALTE(A2:Z2)*299-298;299)))));"")

b) in einer Tabelle nur mit Spalte A und F (als A:B), da Hilfsspalten nicht gewollt sein werden:
D1: =EINDEUTIG(A:A)
E1[:E5]: =TEXTVERKETTEN(", ";;WENN(INDEX(EINDEUTIG(A:B);;1)=D1;INDEX(EINDEUTIG(A:B);;2);""))

Betrifft: AW: 2 Varianten (nur xl365 oder xlWeb)
von: Therese
Geschrieben am: 08.10.2020 13:48:44

Hi lupo1, da bekomme ich ein herzhaftes #NAME? in B6, B8 und B12 (ansonsten leer). Ich habe hier ein O365, müsste mit dem Textjoin also gehen. Habs mir strg+shift+enter versucht und nur enter.
Könntest du mir das als xls mal hochladen, damit ich sehe wie es aussehen sollte?

Betrifft: Sollst Du haben:
von: lupo1
Geschrieben am: 08.10.2020 13:54:29

https://www.herber.de/bbs/user/140732.xlsx

Wenn falsches 365, dann in XLWeb https://office.live.com/start/Excel.aspx hochladen.

Betrifft: AW: 2 Varianten (nur xl365 oder xlWeb)
von: Therese
Geschrieben am: 08.10.2020 21:01:26

Also Luop1, das klappt, aber mein Excel rechnet wie ein Gestörter. Um einmal 100% zu rechnen dauerts gut 30 Minuten. Wie könnte ich denn Excel unter die Arme greifen und alles etwas rechnungsärmer machen? Ich wende die Formel 2x an, da ich die erste Konsolidierung brauche um eine zweite vorzunehmen. Bei der ersten habe ich ca 1.000 Zeilen, bei der zweiten bin ich schon bei 13.000. Bevor ich die zweite mache, habe ich das Ergebnis der ersten einmal entformelt (kopieren, Werte einfügen), damit da nicht zwei Berechnungen parallel laufen.

Ich versuche noch deinen anderen Vorschlag mit der Hilfsspalte und dann versuche ich noch Daniels Vorschlag. Aber ich möchte erstmal meine ganzen Verknüpfungen durchmachen und schauen ob ich am Ende das gewünschte Ergebnis habe.

Betrifft: Tja ... mangels genauer Spezifikation ...
von: lupo1
Geschrieben am: 09.10.2020 00:35:49

... stochern wir da wohl im Dunkeln. Bei mir zuhause wäre es ein Renner.

Betrifft: Und wenn Deine Daten nicht sortiert sind ...
von: lupo1
Geschrieben am: 09.10.2020 00:40:51

... oder nicht sortiert sein dürfen, muss halt das ganze Material durchforstet werden.

Bei 13.000 Datensätzen ist das dann halt 13.000^2, also knapp 200.000.000 Vorgänge.

Betrifft: AW: Tja ... mangels genauer Spezifikation ...
von: Therese
Geschrieben am: 09.10.2020 10:17:14

Ich habs jetzt nochmal versucht in einer neuen Liste vereinfacht nachzubauen.
https://www.herber.de/bbs/user/140757.xlsx
Möglicherweise klappt das alles so nicht, weils einfach zuviele Themen sind.
Im Prinzip habe ich am Anfang die Rolle. Diese bekommt genau 1 Zuweisung.
Ein Profil hat mehrere Rollen. Daher kann ein Profil n Zuweisungen haben.
Ein User hat mehrere Profile. Daher kann ein User n Zuweisungen haben (auf Basis der Profile und Rollen).

Das summiert sich leider unglücklicherweise dadurch, dass ich bei der Zuordnung User zu Profil ~13.000 Einträge habe.
Ich denke das wird so nix ... Das ist doch nichts ungewöhnliches, was ich da machen will oder? Ich habe das ganze leider nur als Excel und bekomme einmal im Monat eine aktualisierte Liste mit den aktuellen Rollen der User. Ich brauch also was, das ich ohne viel Umstand einmal im Monat anwenden kann.

Betrifft: hier käme bei mir jetzt VBA ins Spiel
von: Daniel
Geschrieben am: 09.10.2020 16:14:42

so komplex wie das ist, wird's mit reinen Formeln schwierig.
mit VBA ließe sich entweder ein Makro schreiben, dass die Zuordnung macht und das einmal im Monat ausgeführt wird, oder ggf eine benutzerdefinierte Funktion schreiben.
etwas Abstaktionsvermögen und die Kenntnis des Dictionary-Objekts wären allerdings die Voraussetzung.
Gruß Daniel

Betrifft: AW: Duplikate in Zelle
von: Daniel
Geschrieben am: 08.10.2020 13:48:54

Hi
wenn du nicht die von Lupo genannten Excelversionen verwendest, ist die Hilfsspalte der beste Weg.
ich würde es etwas vereinfachen mit dieser Formel in C2:
=WENN(A2=A1;G1&", ";"")&F2

dann hast du immer nur die Zwischenkommas und kein zu entfernendes Endkomma.

ohne Hilfsspalten nur mit den neuen Excelversionen oder was selbstprogrammiertes in VBA.

ggf würde ich mir überlegen, die Konsolidierung nicht zur letzten Zeile hin zu machen, sondern zur ersten.
was sinnvoller ist, hängt davon ab wie du damit weiter arbeiten willst.
wenn du nachher von jedem Profil nur eine Zeile mit der Konsolidierung behalten willst, wäre es sinnvoller, zur ersten Zeile hin zu konsolidieren.
Dann kannst du direkt das Duplikate-Entfernen dazu verwwenden, da bei diesem die erste Zeile stehen bleibt.
Auch wenn du mit SVerweis mit 4. Parameter = FALSCH nach der Konsoliedierung auswerten willst, brauchst du diese oben.
Nur wenn du mit SVerweis mit 4. Parameter = WAHR auswertest, brauchst du sie beim letzten Wert.

Gruß Daniel

Betrifft: AW: Duplikate in Zelle
von: Therese
Geschrieben am: 08.10.2020 14:40:21

Hi Daniel,
danke für den Input! Ich habe die von lupo erwähnte Version und versuche gerade seine Idee umzusetzen (muss dabei allerdings Tab.blatt-übergreifend formulieren) und mein Excel rechnet daran nun erstmal wie verrückt ... ich fass es mal lieber nicht an. Denke aber, wenn das so rechenintensiv ist, hilft mir das nicht weiter, da ich ja ggfs Änderungen an "Zuweisung" vornehmen will und wenn er dann jedesmal derart rechnet, komme ich nicht weiter. Wenn er durch ist versuche ich mal deins.
Blöde Frage am Rande, wie mache ich die Konsolidierung zur ersten Zeile?

Viele Grüße
Therese

Betrifft: AW: Duplikate in Zelle
von: Daniel
Geschrieben am: 08.10.2020 14:55:52

Hi
naja einfach tauschen, Formel für G2:
=F2&Wenn(A2=A3;", "&G3;"")
Duplikatsprüfung könnte man natürlich auch einbauen, dann wird's etwas aufwendiger:
=Wenn(A2<>A3;F2;Wenn(IstZahl(Finden(", "&F2&", ";", "&G3&", "));G3;F2&", "&G3))
bei Großen Datenmengen ist die Hilfsspaltenlösung diejenige die weniger Rechenzeit erfordert.
das liegt daran, dass hier es ausreicht, wenn die Formel sich auf die direkt darunter liegende Zeile bezieht.
Willst du ohne Hilfsspalte arbeiten, muss jede Formel, in der auch kumuliert wird, jede Zeile des Datensatzes durchsuchen, weil sie ja nicht weiß, wie weit die Werte auch zusammen gehören.

Gruß Daniel

Betrifft: AW: Duplikate in Zelle
von: Therese
Geschrieben am: 09.10.2020 12:58:08

So lieber Daniel, jetzt deine Lösung. Funktioniert auch! Vielen Dank! Allerdings ... die Liste habe ich schon in der Lupo-Antwort drin, führt meine weitere Bearbeitung dazu, dass ich dann doch wieder doppelte Einträge habe, wie bspw
ZW1, ZW2, ZW3, ZW5, ZW3, ZW4
Da ich 3 Zellen habe mit:
ZW1, ZW2, ZW3, ZW5
ZW3, ZW4
ZW3, ZW4
Und die Formel korrekterweise dann die letzten beiden Zellen zusammenfügt (da Duplikat), aber natürlich ist der Inhalt der ersten Zelle anders als die der zweiten, obwohl für mich als Endnutzer natürlich ZW3 doppelt ist.
Da hab ich mir ein kleines Ei gelegt ;) Ich hatte noch überlegt, ob ich irgendeinen Suche/Ersetzen Spaß machen kann, aber da muss ich noch weiter forschen. Mglw ist mein Ansatz einfach schlecht durchdacht.