Office: 4 Spalten zusammenführen, Duplikate entfernen und Werte zählen

Hi,

ein letztes Mal bräuchte ich noch Hilfe, dann ist mein aktuelles Excel Proble mit eurer tollen Hilfe endlich gelöst und ich kann weiter arbeiten *Smilie

Mein Problem gliedert sich in mehrere Unterpunkte:
1. Ich muss in meiner Exceltabelle die Werte der Spalten A, C, E und G in Spalte M zusammenführen.
2. Ich muss hier Duplikate entfernen.
3. In Spalte L will ich nun die ursprüngliche Anzahl jedes Elements. Diesen Teil hab ich.


Beispielhaft so:





Vorab: Ich habe das ganze natürlich gegoogled und anhand der Ergebnisse auch selber versucht. Mit manuellem Eingreifen (selbstständig 4 Spalten untereinander kopieren, auf Duplikate entfernen drücken, ...) würde ich es schaffen. Da mein Ziel aber eine Mustertabelle ist, in der man zukünftig nur noch Rohdaten einträgt und das gewünschte Ergebnis vollautomatisch herauskommt, will ich manuelle Schritte möglichst umgehen.


Wenn mir jemand auch noch bei dem Problem helfen kann, wäre das wirklich toll!

Viele Grüße
Ben

:)

 

Hallo Ben,

ich hatte Dich beim letzten Post gelobt, dass Du eine Beispieldatei hochgeladen hattest. Wieso diesmal nicht? In Deinem Bild kann ich so schlecht arbeiten.

LG Ingo

 

Ehrlichgesagt weil ich dachte, dass ich mich nur blöd anstell und es für dieses Problem eine relativ leichte Lösung gibt, die ihr mehr oder weniger auswendig kennt *Smilie

Hier aber die jetzt die Beispieldatei!

 

Hallo,

Code:

 

Hallo Ben,

Du stellst Dich nicht zu blöde an, die Sache formeltechnisch zu lösen ist sauschwer! Das kriegen formeltechnisch nur sehr wenige auf die Reihe.

Formel für M2:

PHP:

         {=WENN(M1="";"";WENNFEHLER(INDEX($A$1:$G$6;
RUNDEN(AGGREGAT(15;6;WENN((CODE($A$2:$G$6)*1000000+CODE(TEIL($A$2:$G$6;2;1))*1000+CODE(TEIL($A$2:$G$6;3;1))*1)=KKLEINSTE(WENN($A$2:$G$6>M1;(CODE($A$2:$G$6)*1000000+CODE(TEIL($A$2:$G$6;2;1))*1000+CODE(TEIL($A$2:$G$6;3;1))*1));1);ZEILE($A$2:$G$6)*1000+SPALTE($A$2:$G$6));1)/1000;0);
REST(AGGREGAT(15;6;WENN((CODE($A$2:$G$6)*1000000+CODE(TEIL($A$2:$G$6;2;1))*1000+CODE(TEIL($A$2:$G$6;3;1))*1)=KKLEINSTE(WENN($A$2:$G$6>M1;(CODE($A$2:$G$6)*1000000+CODE(TEIL($A$2:$G$6;2;1))*1000+CODE(TEIL($A$2:$G$6;3;1))*1));1);ZEILE($A$2:$G$6)*1000+SPALTE($A$2:$G$6));1);1000));""))}


 

hallo, ich bin natürlich nicht so ein FF (formelfuchs) wie Ingo, aber ich würde das so lösen...:

Arbeitsblatt mit dem Namen 'Tabelle1'
*	O	P	Q
2	1	Garten	3
3	2	Haus	5
4	3	Swimming Pool	3
5	4	Leiter	4
6	5	Dach	2
7	6	Antenne	3
8	*	*	*
9	*	*	*
10	*	*	*
11	*	*	*
12	*	*	*

Name	Bezug
Nummer	=Tabelle1!$B:$B00;Tabelle1!$D:$D00;Tabelle1!$F:$F00;Tabelle1!$H :$H00

Zelle	Formel
O2	=KKLEINSTE(Nummer;ZEILE(A1))
P2	=WENNFEHLER(INDEX($A:$A00;VERGLEICH(O2;$B:$B00;0));WENNFEHLER(INDEX($C:$C00;VERGLEICH(O2;$D:$D00;0));WENNFEHLER(INDEX($E:$E00;VERGLEICH(O2;$F:$F00;0));WENNFEHLER(INDEX($G:$G00;VERGLEICH(O2;$H:$H00;0));""))))
Q2	=WENN(P2="";"";ZÄHLENWENN(A:H;P2))
O3	=WENN(MAX(Nummer)=MAX($O:O2);"";KGRÖSSTE(Nummer;ZÄHLENWENN($B:$H00;">"&O2)))
P3	=WENNFEHLER(INDEX($A:$A00;VERGLEICH(O3;$B:$B00;0));WENNFEHLER(INDEX($C:$C00;VERGLEICH(O3;$D:$D00;0));WENNFEHLER(INDEX($E:$E00;VERGLEICH(O3;$F:$F00;0));WENNFEHLER(INDEX($G:$G00;VERGLEICH(O3;$H:$H00;0));""))))
Q3	=WENN(P3="";"";ZÄHLENWENN(A:H;P3))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

Hallo Frank,

Respekt für Deine Lösung!
Kleiner Schönheitsfehler: In Spalte P fehlt der INDEX/VERGLEICH für die Spalte E/F.

Ich habe mir das Leben viel zu schwer gemacht! Dank Deiner Lösung habe ich die Spalten mit der Nummer jetzt erst verstanden. *stupid (Versuche mal eine Lösung zu finden, ohne diese Nummernspalten zu benutzen....dann wird es Dir aber ganz anders.....) Somit ist meine "Einzellerlösung" noch viel verschlankbarer möglich. Aber dazu komme ich erst am Montag.

.....das mache ich nur, wenn ich absolut keine Möglichkeit sehe einzellig zu arbeiten.... das geht mir gegen meine Excel-Programmier-Ehre!

LG Ingo

P.S.: Ok, den Schönheitsfehler hast Du selber schon entdeckt!!!

 

Vielleicht auch noch.

Matrixformul.
In N2, und
durchführen.

In der niederländischen Version arbeite ich mit Zeile & Spalte "Rij & Kolom" (R0K???) in der Formel.
Ich hoffe, es funktioniert mit Zeille & Spalte (Z0S???)

PHP:

         =WENN(ISTFEHLER(INDIREKT(TEXT(MIN(WENN(ISTZAHL(A$2:$G$6)-ISTLEER($A$2:$G$6)+ZÄHLENWENN(N$1:$N1;$A$2:$G$6);"";ZEILE($2:$6)*1000+SPALTE(A$1:G$1)));"Z0S???");));"";INDIREKT(TEXT(MIN(WENN(ISTZAHL($A$2:$G$6)-ISTLEER($A$2:$G$6)+ZÄHLENWENN(N$1:$N1;$A$2:$G$6);"";ZEILE($2:$6)*1000+SPALTE(A$1:G$1)));"Z0S???");))


 

Hallo Ben,

meine VBA-Lösung sortiert Dir die Ausgabe noch alphabetisch.

Gruß Peter

Public Sub Als_SortList() Dim objArrLi  As Object   ' das Objekt Collections.ArrayList Dim lLetzte   As Long     ' die letzte belegte Zeile der Eingabedaten Dim vTemp     As Variant  ' ein temporärer Array zur Aufnahme der Eingabedaten Dim lZeile    As Long     ' die Zeilen  im temporären Array Dim iSpalte   As Integer  ' die Spalten im temporären Array Dim lIndx     As Long     ' der For/Next Schleifenindex für die Collections.ArrayList    Set objArrLi = CreateObject("System.Collections.ArrayList")    With ThisWorkbook.Worksheets("Tabelle3") ' den Tabellenblattnamen ggf. anpassen!       lLetzte = .Columns("A:G").Find("*", SearchDirection:=xlPrevious).Row       If lLetzte < 2 Then lLetzte = 2 ' die Überschriften "retten", falls es keine Daten gibt '         den Ausgabe-Bereich leeren/löschen       .Range("L2:M" & .Columns("L:M").Find("*", SearchDirection:=xlPrevious).Row + 1).ClearContents '        die Eingabe-Daten in einen temporären Array speichern - ggf. höhere Geschwindigkeit       vTemp = Range("A2:G" & lLetzte) '        die Daten aus dem temporären Array an die Collection übergeben       With objArrLi          For lZeile = 1 To UBound(vTemp, 1)             For iSpalte = 1 To 7 Step 2                If Not Trim$(vTemp(lZeile, iSpalte)) = "" And _                   Not .contains(Trim$(vTemp(lZeile, iSpalte))) Then                   .Add Trim$(vTemp(lZeile, iSpalte))                End If             Next iSpalte          Next lZeile          .Sort ' die Collection (hier die Namen) sortieren       End With '        die Collection ausgeben und die jeweilige Anzahl Vorkommen ermitteln un d ausgeben       If objArrLi.Count > 0 Then ' wurden gefüllte Zellen im Bereich gefunden?          .Range("M2").Resize(objArrLi.Count).Value = WorksheetFunction.Transpose(objArrLi.toarray())          For lIndx = 0 To objArrLi.Count - 1             .Range("L" & lIndx + 2).Value = Application.WorksheetFunction.CountIf( _             .Range("A2:G" & lLetzte), objArrLi(lIndx))          Next lIndx       End If    End With '     die verwendeten Ressourcen zurücksetzen - freigeben    objArrLi.Clear    Set objArrLi = Nothing End Sub

Code eingefügt mit Syntaxhighlighter 4.15

 

Hallo,

Etwas kürzer.
Code:

Oder der verkürzten Matrixformel für Excel 2007 und höher (nicht alphabetisch).

PHP:

         =WENNFHELER(INDIREKT(TEXT(MIN(WENN(ISTZAHL($A$2:$G$6)-ISTLEER($A$2:$G$6)+ZÄHLENWENN(N$1:$N1;$A$2:$G$6);"";ZEILE($2:$6)*1000+SPALTE(A$1:G$1)));"Z0S???"););"")


 

Hallo Harry,

Deine Lösung über das Textformat "R0K???" den INDIREKTen Zellbezug zu erstellen finde ich genial, und wollte ich im deutschen Excel nachbauen. Leider klappt "Z0S???" nicht, obwohl Z1S1 die deutsche Schreibweise wäre, wobei man bei INDIREKT dann noch die Bezugsart "0" angeben muss.
Aber es hakt schon, dass ich Excel dieses Textformat nicht beigebracht bekomme.
Der Einfachheit habe ich als Wert mal 2003 vorgeben, woraus Z2S3 werden soll (2.Zeile und 3.Spalte).
Mit

PHP:

         =TEXT(2003;"Z0S???")


 

Hi Leute,

hier hat sich übers Wochenende ja nochmal viel getan.
Ingos Lösung hat in seiner angehängten Lösung solange funktioniert, bis ich in irgendeine Zelle geklickt habe. Warum auch immer, dann war das Ergebnis leer...

Ich habe in meiner Beispieldatei aber leider eh einen Fehler gemacht: Bei mir ist die 1. Zeile frei. Und Spalte J und K fallen raus, sprich die gewünschten Spalten verschieben sich jeweils um 2 nach links.

Anbei die aktualisierte - und diesmal korrekte - Beispieldatei. Vielleicht kann ja jemand seine Lösung darauf anpassen? *Smilie Mit den Formeln habe ich es probiert, aber leider nicht geschafft, mit vba kenn ich mich gar nicht aus...

Viele Grüße
Ben

 

Hallo Ben,

anbei Deine Datei mit funktionierenden Formeln.
Ich halte Harry´s niederländische Formel als die genialste Lösung.
Leider bekomme ich die nicht auf einfachem Weg "verdeutscht".
Trotzalledem habe ich seine Formel als Vorlage benutzt und mit einem "riesigen" Umweg passend gemacht!

Dein Problem:

liegt hier dran:

Wer lesen kann wäre klar im Vorteil!!!

LG Ingo

 

Hey Ingo,

das mit der Matrixformel hatte ich schon gelesen und berücksichtigt. Das Ergebnis war aber nicht nur verschwunden wenn ich in die Zelle mit der Formel geklickt habe, sondern auch bei jeder anderen Zelle... Ist aber ja inzwischen egal.

Deine letzte Formel funktioniert in der Beispieldatei super!
Wenn ich in der richtigen Datei jetzt über 1140 Zeilen in den Spalten habe, die zusammengeführt werden müssen, muss ich einfach jede 7 durch 1140 ersetzen?
Und ist es wichtig, dass in A2, C2, E2 und G2 das gleiche ("Name") steht oder können hier auch verschiedene Sachen stehen?

Viele Grüße
Ben