Zwei Datensätze auflösen und Zahlen ausgeben

Bild

Betrifft: Zwei Datensätze auflösen und Zahlen ausgeben
von: Joerschi
Geschrieben am: 07.11.2015 23:34:54

Hallo liebes Forum,
wie könnte man folgendes Problem möglichst (ressourcen)optimal lösen?
Zuerst Skizze, dann Erklärung dazu:
Userbild
https://www.herber.de/bbs/user/101314.xlsx
Es liegen je zwei Datensätze in zwei Zellen vor, die zusammengehören.
In diesen befinden sich jeweils bis zu maximal 10 Zahlen, getrennt durch Unterstrich "_".
Manchmal kommt es vor, dass als Platzhalter ein "X" steht. Diese sollen in der folgenden Aufgabe ignoriert werden.
Es soll nun beginnend ab Spalte C von li nach re die einzelnen Zahlen aufsteigend ausgegeben werden.
Bedingungen:
1. Kommt ein Wert in beiden Datensätzen vor, darf er nur einmal in der Lösung erfasst sein.
2. Ignorieren von "X".
Meine Idee wäre - wenn die Zahlen durch Teilung in einzelne Zellen aufgelöst wurden (weiß jedoch nicht, wie das in obigen Beispiel geht) - diese dann per Aggregat bzw. KGrösste einfach zu sortieren.
Aber vielleicht gehts noch besser? (wenn nicht, würde mich interessieren, wie die Teilung vorzunehmen ist - sie betrifft stets den Unterstrich. Gern mit Hilfsspalten)
Danke vorab und liebe Grüße
Joerschi

Bild

Betrifft: AW: Zwei Datensätze auflösen und Zahlen ausgeben
von: Matthias L
Geschrieben am: 08.11.2015 01:50:43
Hallo
Zuerstmal würde ich "Glätten", da der Datensatz2 immer je ein Leerzeichen am Anfang und am Ende hat
Tabelle1

 AB
3Datensatz 1Datensatz 2
41_7_12_X_X_17_21_30_32_34 38_36_35_34_32_30_21_17_15_14
518_19_20_24_25_30_32_33_34_35 39_38_36_35_34_33_32_X_X_X
62_3_4_5_6_8_9_10_11_13 39_33_16_13_11_10_9_8_6_5
714_15_16_17_21_22_23_26_27_28 37_31_29_28_27_26_23_22_21_17
818_19_20_22_23_24_25_26_27_28 37_31_29_28_27_26_25_24_23_22
9  
10  
111_7_12_X_X_17_21_30_32_34_ 38_36_35_34_32_30_21_17_15_14 
1218_19_20_24_25_30_32_33_34_35_ 39_38_36_35_34_33_32_X_X_X 
132_3_4_5_6_8_9_10_11_13_ 39_33_16_13_11_10_9_8_6_5 
1414_15_16_17_21_22_23_26_27_28_ 37_31_29_28_27_26_23_22_21_17 

Formeln der Tabelle
ZelleFormel
A11=GLÄTTEN(A4&"_"&B4)
A12=GLÄTTEN(A5&"_"&B5)
A13=GLÄTTEN(A6&"_"&B6)
A14=GLÄTTEN(A7&"_"&B7)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Danach würde ich Kopieren und als Werte einfügen
Danach würde ich Daten Text in Spalten benutzen (Trennzeichen: Leerzeichen und "_")
Tabelle1

 ABCDEFGHIJKLMNOPQRST
111712XX172130323438363534323021171514
121819202425303233343539383635343332XXX
1323456891011133933161311109865
141415161721222326272837312928272623222117


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Jetzt das "X" ersetzen durch Nichts (Strg+H)
Tabelle1

 ABCDEFGHIJKLMNOPQRST
111712  172130323438363534323021171514
121819202425303233343539383635343332   
1323456891011133933161311109865
141415161721222326272837312928272623222117


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Nun nur noch Kopieren - Inhalte einfügen / Transponieren an geeigneter Stelle
Tabelle1

 WXYZ
1118214
2719315
31220416
4 24517
5 25621
61730822
72132923
830331026
932341127
1034351328
1138393937
1236383331
1335361629
1434351328
1532341127
1630331026
172132923
1817 822
1915 621
2014 517


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Am Ende Markieren und Leerzeichen löschen
Tabelle1

 WXYZ
1118214
2719315
31220416
41430517
51532621
61732822
71733822
82133923
92134923
1030341026
1130351026
1232351127
1332361127
1434381328
1534391328
1635241629
1736253331
1838 3937
19  517
20  621


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Matthias

Bild

Betrifft: Morrn, Matti!
von: Luc:-?
Geschrieben am: 08.11.2015 01:55:55
Hatte aus Versehen meine fast fertige AW weggeklickt, sonst wärst du Zweiter geworden! ;-)
Gruß + schöSo!
GuN8, Luc :-?

Bild

Betrifft: AW: Zwei Datensätze auflösen und Zahlen ausgeben
von: Joerschi
Geschrieben am: 08.11.2015 08:23:20
Guten Morgen Matthias,
besten Dank für den ausführlichen Lösungsweg.
Werde ich heute Vormittag mal durchprobieren bzw. schauen, wie ich den Ansatz am besten in meine Datei einbinden kann.
Text in Spalten etc. als "manuellen" Lösungsweg hatte ich auch schon in Betracht gezogen, allerdings die Glättung zu Beginn zum Beispiel ist interessant und neu.
Danke auch @ Luc, aber ja: mit VBA kann ich derzeit wirklich nicht viel anfangen.
Da habe ich nur absolut rudimentäres Grundwissen.
Bei den anderen Lösungen, insbesondere von neopa, weißt Du ja: teils ähnlich klingende Probleme, jedoch gänzlich unterschiedlich (komplexe) Lösungen ;-)
Viele Grüße
Joerschi

Bild

Betrifft: AW: Zwei Datensätze auflösen und Zahlen ausgeben
von: Matthias L
Geschrieben am: 08.11.2015 08:57:41
Guten Morgen
So kannst Du die String auch zusammenführen
Tabelle1

 AB
3Datensatz 1Datensatz 2
41_7_12_X_X_17_21_30_32_34 38_36_35_34_32_30_21_17_15_14
518_19_20_24_25_30_32_33_34_35 39_38_36_35_34_33_32_X_X_X
62_3_4_5_6_8_9_10_11_13 39_33_16_13_11_10_9_8_6_5
714_15_16_17_21_22_23_26_27_28 37_31_29_28_27_26_23_22_21_17
818_19_20_22_23_24_25_26_27_28 37_31_29_28_27_26_25_24_23_22
9  
10  
111 7 12 17 21 30 32 34 38 36 35 34 32 30 21 17 15 14 
1218 19 20 24 25 30 32 33 34 35 39 38 36 35 34 33 32 
132 3 4 5 6 8 9 10 11 13 39 33 16 13 11 10 9 8 6 5 
1414 15 16 17 21 22 23 26 27 28 37 31 29 28 27 26 23 22 21 17 
1518 19 20 22 23 24 25 26 27 28 37 31 29 28 27 26 25 24 23 22 

Formeln der Tabelle
ZelleFormel
A11=GLÄTTEN(WECHSELN(WECHSELN(A4&"_"&B4;"_";" ");"X";" "))
A12=GLÄTTEN(WECHSELN(WECHSELN(A5&"_"&B5;"_";" ");"X";" "))
A13=GLÄTTEN(WECHSELN(WECHSELN(A6&"_"&B6;"_";" ");"X";" "))
A14=GLÄTTEN(WECHSELN(WECHSELN(A7&"_"&B7;"_";" ");"X";" "))
A15=GLÄTTEN(WECHSELN(WECHSELN(A8&"_"&B8;"_";" ");"X";" "))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Wie geschrieben Daten Text in Spalten und Transponieren
Am Ende spaltenweise Duplikate löaschen
Gruß Matthias

Bild

Betrifft: UDFs sind allgemeine Fertiglösungen, die du ...
von: Luc:-?
Geschrieben am: 08.11.2015 13:11:58
…nur in ein allgemeines Modul des VBA-Projekts deiner Mappe kopieren musst, um sie dann in einer ZellFml wie gezeigt einsetzen zu können, Joerschi;
das, nahm ich an, wäre kein Problem für dich. Außerdem kannst du sie auch in anderen Zusammenhängen genau wie eine Standard-Xl-Fkt einsetzen, sie sind also ohne weiteres wiederverwendbar. Der einzige Nachteil von UDFs im Besonderen und VBA allgemein besteht darin, dass du Mappen mit UDF-haltigen Fmln nur mit den Pgmm der UDFs oder ohne Fmln weitergeben kannst, während für Mappen mit VBA-Prozeduren ggf eine Weitergabe ohne diese Pgmm ausreicht, nicht aber bei mit VBA automatisierten Abläufen.
Luc :-?

Bild

Betrifft: AW: Danke - UDF´s kannte ich noch nicht!
von: Joerschi
Geschrieben am: 08.11.2015 14:47:02
Hallo Luc,
oh - das hatte ich in meiner Unwissenheit anders verstanden.
Von den UDF´s höre ich tatsächlich das erste Mal. So wie Du sie beschreibst, sind sie wohl ein Pendent zu Bibliotheken in anderen Programmiersprachen.
Das klingt hochinteressant und kann ich bestimmt gebrauchen - schaue ich mir auf jeden Fall mal an.
Besten Dank und viele Grüße
Joerschi

Bild

Betrifft: Nein, idR keine Bibliotheken (DLL), sondern ...
von: Luc:-?
Geschrieben am: 08.11.2015 17:49:47
…einfach nur mehr oder weniger kleine selbständige Pgmm (Functions) wie sie in nahezu jeder Pgmmiersprache vorkommen, unter VBA/Xl aber - je nach Inhalt - auch in ZellFmln eingesetzt wdn können, genauso wie XlFktt, die letztlich ja auch mehr oder weniger kleine selbständige Pgmm sind. Die Abkürzung UDF bedeutet userdefined function (benutzerdefinierte Fkt), also eine eigene Fkt, die Xl dadurch bekannt wird, dass sie im VBA-Projekt der ausführenden Mappe oder in einem Xl zugeordneten aktiven AddIn gespeichert sind. Die Inhalte eines sog Com-AddIn sind dagg in DLLs gespeichert, was auch für die XlFktt gilt, nur gehören deren DLLs zum Leistungsumfang von Xl und benötigen daher idR kein AddIn (die Xl-Standard-AddIns enthalten keine Pgmm, ggf nur DLL-Parameter).
Meine UDFs VSplit und VJoin setzen übrigens die vbFktt Split und Join etwas komplexer um als die Originale. Sie bieten also mehr Einsatzmöglichkeiten.
Luc :-?

Bild

Betrifft: Ja, mit 3 UDFs in 1zeiligen MatrixFmln, ...
von: Luc:-?
Geschrieben am: 08.11.2015 01:52:32
…Joerschi:
C4:U4[;C5:U8]:{=WENNFEHLER(KKLEINSTE(VSplit(VJoin(VSplit(MaskOn(VJoin(A4:B4;"_");"num"));;-1);;1);SPALTE(A1:S1));"")}
2 findest du in dieser BspMappe aus dem Archiv und MaskOn hier.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: Hey Luc, ... bei VBA Nein ? ;-) Gruß Matthias owT
von: Matthias L
Geschrieben am: 08.11.2015 01:55:15


Bild

Betrifft: Det kann der schon, Matti, nur nich selbst! ;-)
von: Luc:-?
Geschrieben am: 08.11.2015 01:58:26
Hat doch schon öfter solche Lösungen bekommen!
Luc :-?

Bild

Betrifft: Oha ;-) owT
von: Matthias L
Geschrieben am: 08.11.2015 02:01:24


Bild

Betrifft: VBA Lösung
von: Sepp
Geschrieben am: 08.11.2015 09:34:28
Hallo Joerschi,

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub joerschi()
Dim varIn As Variant, varItem As Variant, varSplit As Variant
Dim objAL As Object
Dim lngI As Long, lngN As Long, lngR As Long

Set objAL = CreateObject("System.Collections.Arraylist")

With Tabelle1
  For lngR = 4 To Application.Max(4, .Cells(.Rows.Count, 1).End(xlUp).Row)
    lngI = 0
    objAL.Clear
    varIn = .Range(.Cells(lngR, 1), .Cells(lngR, 2))
    For Each varItem In varIn
      varSplit = Split(varItem, "_")
      For lngN = 0 To UBound(varSplit)
        If IsNumeric(varSplit(lngN)) Then
          If Not objAL.Contains(Clng(varSplit(lngN))) Then objAL.Add Clng(varSplit(lngN))
        End If
      Next
    Next
    objAL.Sort
    .Cells(lngR, 3).Resize(1, objAL.Count) = objAL.toArray
  Next
End With

Set objAL = Nothing
End Sub

Gruß Sepp


Bild

Betrifft: noch eine Formelalternative mit AGGREGAT() ...
von: ... neopa C
Geschrieben am: 08.11.2015 18:27:36
Hallo Joerschi,
... auf Basis der alten Excel4 Makrofunktion AUSWERTEN(). Dazu musst Du anschließend die Datei jedoch als XLSm abspeichern aber das war es auch schon. Den Bereichsnamen Zahl musst Du bei aktivierter Zelle C4 definieren:

 ABCDEFGHIJKLMNOP
3Datensatz 1Datensatz 2Zahlen einzeln aufsteigend
41_7_12_X_X_17_21_30_32_34 38_36_35_34_32_30_21_17_15_14 171214151721303234353638 
518_19_20_24_25_30_32_33_34_35 39_38_36_35_34_33_32_X_X_X18192024253032333435363839 
62_3_4_5_6_8_9_10_11_13 39_33_16_13_11_10_9_8_6_5 2345689101113163339 
714_15_16_17_21_22_23_26_27_28 37_31_29_28_27_26_23_22_21_17 14151617212223262728293137 
818_19_20_22_23_24_25_26_27_28 37_31_29_28_27_26_25_24_23_22 18192022232425262728293137 

Formeln der Tabelle
ZelleFormel
C4=Zahl
Namen in Formeln
ZelleNameBezieht sich auf
C4Zahl=WENNFEHLER(AGGREGAT(15;6;AUSWERTEN("{"&GLÄTTEN(WECHSELN(WECHSELN(Tabelle1!$A4&"_"&Tabelle1!$B4;"_";".");".X";""))&"}")/(ZÄHLENWENN(Tabelle1!$B4:B4;AUSWERTEN("{"&GLÄTTEN(WECHSELN(WECHSELN(Tabelle1!$A4&"_"&Tabelle1!$B4;"_";".");".X";""))&"}"))=0);1);"")
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zwei Datensätze auflösen und Zahlen ausgeben"