Zahlenkette finden und auflisten

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
UserForm InputBox MsgBox
Bild

Betrifft: Zahlenkette finden und auflisten
von: erichm
Geschrieben am: 30.05.2015 09:39:19

Hallo,
wir müssen aus einer Übersicht von Auftragsnummern pro Zeile immer die Aufträge finden, deren Nummern in einer Zeile aufsteigend sind (aufsteigend bedeutet, dass immer in der Folgespalte die nächsthöhere Auftragsnummer steht!!)und diese dann auflisten.
Also wenn in einer Zeile in aufeinanderfolgenden Spalten aufsteigende Nummern sind müssen wir diese finden:
Zahlenkette finden

 ABCDEFGHIJKLMNO
1Auftrag1Auftrag2Auftrag3Auftrag4Auftrag5Auftrag6Auftrag7Auftrag8Auftrag9Auftrag10 Ergebnisspalten
213351678917543117876861 789 
38719119878478927359014067648     
4948717273954281820929311314 717273 
5849453793611381809493494495496 493494495496
67957166709154713887100487520     
76495715940664364464570693831 643644645 
865596220548379219197336600608     
9799802153288828385897494701230     
10182778455232256257258945726446 256257258 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Ich hoffe das Beispiel ist soweit erklärend.
Danke für eine Hilfe.
mfg

Bild

Betrifft: Zahlenfolge auflisten
von: WF
Geschrieben am: 30.05.2015 10:11:30
Hi,
mal aufgedröselt:
in V2 (Hilfszelle) schreibst Du die Arrayformel:
=VERGLEICH(1;B2:K2-A2:J2;0)
in L2:
=WENNFEHLER(INDEX($A2:$J2;$V2);0)
in M2:
=WENNFEHLER(WENN(INDEX($A2:$J2;$V2+SPALTE(A1))=L2+1;INDEX($A2:$J2;$V2+SPALTE(A1));0);0)
M2 nach rechts kopieren
L2 bis V2 nach unten kopieren
Salut WF

Bild

Betrifft: AW: Zahlenfolge auflisten
von: erichm
Geschrieben am: 30.05.2015 11:12:31
Danke; bei V2 bekomme ich als Ergebnis leider #WERT ??
als Alternative zur VBA-Lösung evtl. hilfreich für das weitere Projekt
mfg

Bild

Betrifft: AW: Zahlenfolge auflisten
von: erichm
Geschrieben am: 30.05.2015 11:16:29
DANKE!
Klappt perfekt!
Folgende Optimierung wäre interessant:
1. Die Ergebnisspalten sollten immer geleert werden, wenn neu gerechnet werden muss.
2. Die Spaltenbereiche 1 - 10 (Auftragsnummern) und die erste Spalte in die die Ergebnisse eingetragen werden, sollten mit dem Buchstaben der Spalte angegeben werden können: je nach Tabelle stehen die Werte in unterschiedlichen Spalten.
Danke.
mfg

Bild

Betrifft: V2 ist eine ARRAYFORMEL
von: WF
Geschrieben am: 30.05.2015 11:45:39
{=VERGLEICH(1;B2:K2-A2:J2;0)}
Eingabe Arrayformel:
Du kopierst Dir die Formel.
Dann gehst Du in die Bearbeitungszeile, löschst die {geschweiften Klammern} am Anfang und Ende und schließt ab mit GLEICHZEITIG: Strg Shift Enter (statt Enter allein). Dadurch werden diese Klammern erzeugt.
WF

Bild

Betrifft: AW: V2 ist eine ARRAYFORMEL
von: erichm
Geschrieben am: 30.05.2015 12:19:37
SORRY - hatte ich übersehen; Danke!
(Beitrag noch offen wegen VBA-Lösung)

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: Oberschlumpf
Geschrieben am: 30.05.2015 10:18:56
Hi erich,
und hier hätt ich ne VBA-Lösung:

Sub sbNumOrder()
    Dim lloRow As Long, lloCol As Long, laroArtNmb() As Long, liIdx As Integer, lboOk As  _
Boolean, lloColTg As Long
    
        For lloRow = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            lloColTg = 12
                For lloCol = 1 To 10
                    If Cells(lloRow, lloCol).Value + 1 = Cells(lloRow, lloCol + 1).Value Then
                            ReDim Preserve laroArtNmb(liIdx)
                            laroArtNmb(liIdx) = Cells(lloRow, lloCol).Value
                            liIdx = liIdx + 1
                            lboOk = True
                        Else
                            If lboOk = True Then
                                lboOk = False
                                ReDim Preserve laroArtNmb(liIdx)
                                laroArtNmb(liIdx) = Cells(lloRow, lloCol).Value
                                    For liIdx = 0 To UBound(laroArtNmb)
                                        Cells(lloRow, lloColTg).Value = laroArtNmb(liIdx)
                                        lloColTg = lloColTg + 1
                                    Next
                                liIdx = 0
                                Erase laroArtNmb
                            End If
                    End If
                Next
        Next
        
End Sub

Hilfts?
Ciao
Thorsten

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: erichm
Geschrieben am: 30.05.2015 11:17:30
siehe meine Antwort vorhin bzgl. VBA-Lösung

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: erichm
Geschrieben am: 30.05.2015 11:33:16
Jetzt ist bei der VBA-Lösung doch noch ein Problem aufgetreten:
Es gibt in den Zeilen teilweise zwei + weitere drei aufeinanderfolgende Zahlen
Somit werden beide in die Ergebnisspalte eingetragen
Es wäre nun sehr hilfreich, wenn immer nur ausgewertet wird:
- nur zwei aufeianderfolgende
- nur drei aufeinanderfolgende
- nur vier aufeinanderfolgende
Ob das auch geht?
Danke nochmal!
mfg

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: Oberschlumpf
Geschrieben am: 30.05.2015 12:46:55
tja erich
dann wäre mal ne bsp-datei mit allen relevanten bsp-daten schön...per upload
denn dass es passieren kann, dass in einer zeile = mehr als eine reihe aufeinander folgenden auftragsnummern erscheinen können...und du dann nur die erste, gefundene reihe haben willst, haste in deiner ersten frage nich erwähnt
bis später vllt
thorsten

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: erichm
Geschrieben am: 30.05.2015 13:26:45
SORRY - das Problem hatte ich nicht sofort erkannt.
Jetzt eine Beispieldatei mit entsprechender farblicher Markierung der gewünschten Ergebnisspalten.
https://www.herber.de/bbs/user/97939.xlsm
DANKE nochmal!
mfg

Bild

Betrifft: diesen Threadteil nun erst angesehen und ...
von: der neopa C
Geschrieben am: 30.05.2015 17:44:18
Hallo Erich,
... damit ergibt sich wie Christian schon schreibt, eine doch nicht unerhebliche Änderung der ursprünglichen Aufgabenstellung. Auch das wäre bei eindeutigen Angaben noch ohne VBA lösbar, wenn auch sicherlich dann nicht ganz so einfach.
Hinzu kommt aber, dass ich festgestellt habe, dass Du in Deiner neuen Datei in E6:J6 einen "6er" hast und Du von diesen nur den letzten Teil dessen als "4er" gelistet hast. Warum auch immer so und nicht anders.
Das wird mir dann doch momentan alles zu uneindeutig. Ich steig dann erst einmal wieder aus.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: diesen Threadteil nun erst angesehen und ...
von: erichm
Geschrieben am: 30.05.2015 19:53:37
Danke für die Rückmeldung - das muss ich mir morgen noch anschauen.
mfg

Bild

Betrifft: Ganz so schwierig ist eine FmlLösung nun ...
von: Luc:-?
Geschrieben am: 31.05.2015 15:09:28
…auch nicht, Werner,
aber mit der Anforderung, 2 Folgen in einer Zeile auch physisch zu trennen, wird's dann ganz ohne VBA (also auch ohne UDFs) wirklich kompliziert, Erich,
weil es dafür außer INDEX kaum eine XlFkt gibt. Ein leider bisher doch sehr individueller Versuch (unter Hilfszellen­Benutzung) ist im unteren Teil der nach­folgenden Tabelle dargestellt. Wenn man das mit Einzelzell-Fmln darstellen könnte, wäre es vermutlich allgemeiner gestaltbar. Aber das wird mir dann doch zu aufwendig, zumal ich davon ausgehe, dass nun eine VBA-Komplett­Lösung vorliegt. Eine UDF in einer ZellFml könnte es aber ggf auch tun.

 ABCDEFGHIJKLMNOPQRST
11234567891011121314151617181920
2Auftrag1Auftrag2Auftrag3Auftrag4Auftrag5Auftrag6Auftrag7Auftrag8Auftrag9Auftrag10 Ergebniszeilen für alle Folgen (auch unterschiedlicher Länge)
313351678917116117200861 789116117    
487191198784789273590140141648 140141       
5948717273954281820929311314 717273      
6849850793611491492493494495496 849850491492493494495496 
77957166709154713887100487520          
86495715940664364464570670731 643644645706707    
965596220520679219197336600608 205206       
10799802153288828385897494701230          
11182778455232256257258945726446 256257258      
12L3[:T11]: {=WENNFEHLER(INDEX(A3:J3;KKLEINSTE(WENNFEHLER(SPALTE($A$1:$J$1)*(WENNFEHLER(WVERWEIS("Auftrag"&SPALTE(A$3:J$3)+1;$A$2:$J$11;ZEILE($A2);0)-A3:J3=1;0)+WENNFEHLER(A3:J3-WVERWEIS("Auftrag"&SPALTE(A$3:J$3)-1;$A$2:$J$11;ZEILE($A2);0)=1;0))^0;"");SPALTE($A$1:$J$1)));"")}
13Hilsspalten mit Folgenmarkierung pro Datensatz (0-keine, 1-Anfangs-, 2-End-, 3-Innenposition) Ergebniszeilen für maximal 2 Folgen pro Zeile mit Leerfeld zwischen beiden (mit Hilfszellen, Formeln z.T. individuell)*
140013201200 789 116117#NV#NV#NV
150000000120 140141#NV#NV#NV#NV#NV#NV#NV
160132000000 717273#NV#NV#NV#NV#NV#NV
171200133332 849850 491492493494495496
180000000000          
190000132120 643644645 706707#NV#NV#NV
200012000000 205206#NV#NV#NV#NV#NV#NV#NV
210000000000          
220000132000 256257258#NV#NV#NV#NV#NV#NV
23A14[:J22]: {=WENNFEHLER(WVERWEIS("Auftrag"&SPALTE(A$3:J$3)+1;$A$2:$J$11;ZEILE($A2);0)-A3:J3=1;0)+2*WENNFEHLER(A3:J3-WVERWEIS("Auftrag"&SPALTE(A$3:J$3)-1;$A$2:$J$11;ZEILE($A2);0)=1;0)}* #NV farblich ausblendbar (hier weiß)
24L14[:T22]: {=WENN(ZÄHLENWENN(A14:J14;1)=0;"";WENN(ZÄHLENWENN(A14:J14;1)=1;INDEX(A3:J3;VERGLEICH(1;A14:J14;0)):INDEX(A3:J3;VERGLEICH(2;A14:J14;0));INDEX((INDEX(A3:J3;VERGLEICH(1;A14:J14;0)):INDEX(A3:J3;VERGLEICH(2;A14:J14;0));K3;
25INDEX(INDEX(A3:J3;VERGLEICH(2;A14:J14;0)+1):J3;VERGLEICH(1;INDEX(A14:J14;VERGLEICH(2;A14:J14;0)+1):J14;0)):INDEX(INDEX(A3:J3;VERGLEICH(2;A14:J14;0)+1):J3;VERGLEICH(2;INDEX(A14:J14;VERGLEICH(2;A14:J14;0)+1):J14;0)));1;{1.2.3.1.1.2};{1.1.1.2.3.3})))}Hinweis:
26L17:T17: {=WENN(ZÄHLENWENN(A17:J17;1)=0;"";WENN(ZÄHLENWENN(A17:J17;1)=1;INDEX(A6:J6;VERGLEICH(1;A17:J17;0)):INDEX(A6:J6;VERGLEICH(2;A17:J17;0));INDEX((INDEX(A6:J6;VERGLEICH(1;A17:J17;0)):INDEX(A6:J6;VERGLEICH(2;A17:J17;0));K6;K3:K11 enthält
27INDEX(INDEX(A6:J6;VERGLEICH(2;A17:J17;0)+1):J6;VERGLEICH(1;INDEX(A17:J17;VERGLEICH(2;A17:J17;0)+1):J17;0)):INDEX(INDEX(A6:J6;VERGLEICH(2;A17:J17;0)+1):J6;VERGLEICH(2;INDEX(A17:J17;VERGLEICH(2;A17:J17;0)+1):J17;0)));1;{1.2.1.1.2.3.4.5.6};{1.1.2.3.3.3.3.3.3})))}Textpräfix!

Gruß, Luc :-?

Bild

Betrifft: hatte ich aber mit Formel einfacher gelöst, ...
von: der neopa C
Geschrieben am: 31.05.2015 17:54:08
Hallo Luc,
... wenigstens was den ersten Teil Deiner Ausführungen angeht. Offensichtlich hast Du meinen unten stehenden Beitrag hier: https://www.herber.de/forum/messages/1428459.html und die dort getroffene Aussage: "Ergänzender Hinweis: Sollten mehrere entsprechende Zahlenreihen in einer Zeile stehen, werden diese unmittelbar hintereinander gelistet" noch nicht gelesen!?!
Auch hast Du wohl meine, Deinem Beitrag vorstehenden Anmerkungen, nicht in Zusammenhang mit Erichs präzisierter Fragestellung überlesen.
Gruß Werner
.. , - ...

Bild

Betrifft: Ich hatte alles gelesen, deinen 1.Versuch aber ...
von: Luc:-?
Geschrieben am: 31.05.2015 18:29:17
…nicht noch mal rekapituliert, Werner,
da du dich ja hier auf neuere Anforderungen beziehst, was ich eher mit mehreren Folgen in einer Zeile als mit Lücken dazwischen oder gar Aufteilung auf Einzelbereiche in Zusammenhang gebracht hatte, obwohl du dich hier ja wohl schon auf die BspDatei beziehst. Deine Bemerkung zu mehreren Folgen kommt mir im Nachhinein aber bekannt vor.
Mir ging's jetzt eher um einen Ansatz für den 2.Teil meines Bsps und den habe ich auf diesem Weg zu realisieren versucht. Dabei fiel nebenbei der 1.Teil ab, nachdem ich das ursprünglich etwas anders versucht hatte. Aber so beschränkt sich das auf den tatsächlich verwendeten ZellBereich.
Ich hätte jetzt eher erwartet, dass du eine allgemeine Fml für die Abbildung von Lücken zwischen den Fmln findest, denn für die Wiedergabe in seinen EinzelBereichen schwebt mir schon eine Lösung vor. Muss nur noch testen, ob das auch klappt. Allerdings ist mir ggw noch nicht ganz klar, wie man das elegant auf evtl noch mehr Folgen/Zeile ausdehnen könnte und wie man das in EinzelBereichen darstellen wollte, wenn mehrere gleichlange Folgen in einer Zeile auftreten. Letzteres wäre dann aber auch für eine VBA-Komplett­Lösung problematisch, so wie das bisher aussieht.
Luc :-?

Bild

Betrifft: AW: Ich hatte alles gelesen, deinen 1.Versuch aber ...
von: erichm
Geschrieben am: 31.05.2015 22:08:41
Kurzer Zwischenbescheid:
Hatte heute leider nicht soviel Zeit wie erwartet; muss meine Rückmeldung verschieben - zudem gibt es ja eine Menge neuer Überlegungen, die muss ich erst noch analysieren
mfg

Bild

Betrifft: Na, dann analysiere mal, aber nimm das ...
von: Luc:-?
Geschrieben am: 01.06.2015 04:24:00
…Folgende mit, Erich;
hier noch eine udf-gestützte FmlLösung mit unsortierten, aber vereinzelten Zahlenfolgen diverser Elemente­Anzahl (auch mehrere gleich-anzahlige in einer Zeile):

 ABCDEFGHIJKLMNOPQRSTUVW
11234567891011121314151617181920212223
2Auftrag1Auftrag2Auftrag3Auftrag4Auftrag5Auftrag6Auftrag7Auftrag8Auftrag9Auftrag10 Ergebniszeilen mit allen leerzelle-getrennten Folgen aller Längen
313351678917116117200861 789 116117      
487191198784789273590140141648 140141          
5948717273954281820929311314 717273         
6849850793611491492493494495496 849850 491492493494495496   
77957166709154713887100487520             
86495715940664364464570670731 643644645 706707      
965596220520679219197336600608 205206          
10799802153288828385897494701230             
11182778455232256257258945726446 256257258         
12529530531532727728729730732733 529530531532 727728729730 732733
13FolgenPos⇒WENNFEHLER(WVERWEIS("Auftrag"&SPALTE($A3:$J3)+1;$A$2:$J$12;ZEILE($A2);0)-$A3:$J3=1;0)+2*WENNFEHLER($A3:$J3-WVERWEIS("Auftrag"&SPALTE(A$3:J$3)-1;$A$2:$J$12;ZEILE($A2);0)=1;0)L3:W3[:W12]:{=WENN(SUMME(FolgenPos)=0;"";VSplit(VJoin(WENN(FolgenPos=0;"";A3:J3&WENN(FolgenPos=2;" ";""));;-1)&WIEDERHOLEN(" ";SPALTEN(A3:J3)-1);;1))}

FolgenPos ist der definierte Name einer benannten Fml. Die beiden UDFs findest du u.a. in dieser Datei!
Morrn, Luc :-?

Bild

Betrifft: Hier nun noch eine weitere Variante, die ...
von: Luc:-?
Geschrieben am: 02.06.2015 03:09:52
…die einzelnen Zahlenfolgen nicht durch Leerzellen, sondern eindeutig als solche erkenn­bare voran­gestellte Element­Anzahl­Angaben trennt, Erich,
womit ggf auch eine Sortierung der Zahlen­folgen nach dieser Anzahl möglich wäre.

 ABCDEFGHIJKLMNOPQRSTUVWX
1123456789101112131415161718192021222324
2Auftrag1Auftrag2Auftrag3Auftrag4Auftrag5Auftrag6Auftrag7Auftrag8Auftrag9Auftrag10 Ergebniszeilen mit allen jeweils auftretenden anzahltext-getrennten Folgen aller Elementanzahlen
313351678917116117200861 3:7892:116117      
487191198784789273590140141648 2:140141          
5948717273954281820929311314 3:717273         
6849850793611491492493494495496 2:8498506:491492493494495496   
77957166709154713887100487520              
86495715940664364464570670731 3:6436446452:706707      
965596220520679219197336600608 2:205206          
10799802153288828385897494701230              
11182778455232256257258945726446 3:256257258         
12529530531532727728729730732733 4:5295305315324:7277287297302:732733
13FolgenPos⇒WENNFEHLER(WVERWEIS("Auftrag"&SPALTE($A3:$J3)+1;$A$2:$J$12;ZEILE($A2);0)-$A3:$J3=1;0)+2*WENNFEHLER($A3:$J3-WVERWEIS("Auftrag"&SPALTE(A$3:J$3)-1;$A$2:$J$12;ZEILE($A2);0)=1;0)
14ModPos⇒VSplit(VJoin(WECHSELN(VSplit(WECHSELN(WECHSELN(VJoin(FolgenPos;"");"1";"#1");"#";"";1);"#");1;VSplit(DiSum(GLÄTTEN(WECHSELN(WECHSELN(VJoin(FolgenPos;"");0;" ");21;"2 1"))))/3+1);"");"";1)
15RohZeile⇒VSplit(VJoin(WENN(FolgenPos=0;"";WAHL(FolgenPos;SPALTE($A3:$J3)&"000,";"";"")&$A3:$J3);",";-1)&WIEDERHOLEN(",";SPALTEN($A3:$J3)-1);",";1)
16L3:X3[:X12]:{=WENNFEHLER(WENN(WENNFEHLER(INDEX(A3:J3;RohZeile/1000);0)=INDEX(RohZeile;SPALTE(A3:M3)+1);INDEX(ModPos;RohZeile/1000)&":";RohZeile);"")}
Für die 1. drei der unten aufgeführten Fmln steht der ihnen voran­gestellte definierte Namen (die Fmln zeigen natürlich die Form, die sie bei Auswahl von L3 haben). Allerdings habe ich hierbei der Einfachheit halber (um diese Variante ohne zu großen Aufwand auszu­probieren) eine weitere UDF benutzt, die ich weder veröf­fentlicht habe noch das beab­sichtige. Diese Fkt, DiSum, bildet Quer­summen, auch von Zahlen in Texten und für jede Zahl einzeln (oder auch von allen Ziffern), was hier genutzt wird. Deshalb dürfte es kompli­zierter sein, das mit Standard­Fktt nach­zustellen. Außerdem deckt die UDF die ganze Breite dessen, was in Wikipedia zu Quer­summe steht, und noch etwas mehr ab. Das wird hier aber nicht benötigt.
Luc :-?

Bild

Betrifft: Die Fortsetzung zur sortierten Übernahme ...
von: Luc:-?
Geschrieben am: 03.06.2015 03:19:13
…dieses Ergebnisses könnte dann so aussehen:

 LMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
11213141516171819202122232425262728293031323334353637383940414243
2Ergebniszeilen mit allen jeweils auftretenden anzahltext-getrennten Folgen aller Elementanzahlen 2er-Folgen 3er-Folgen 4er-Folgen 6er-Folgen
33:7892:116117       116117 789            
42:140141           140141                
53:717273             717273            
62:8498506:491492493494495496    849850          491492493494495496
7                                
83:6436446452:706707       706707 643644645            
92:205206           205206                
10                                
113:256257258             256257258            
124:5295305315324:7277287297302:732733 732733     529530531532       
13       Z3:AA3[:AA12;AC3:AE12;AG3:AJ12;AL3:AQ12]:727728729730       
14       {=WENNFEHLER(INDEX($L3:$X3;VERGLEICH(LINKS(Z$2)&":";$L3:$X3;0)+1):INDEX($L3:$X3;VERGLEICH(LINKS(Z$2)&":";$L3:$X3;0)+LINKS(INDEX($L3:$X3;VERGLEICH(LINKS(Z$2)&":";$L3:$X3;0))));"")}
15   AG13:AJ13:{=WENNFEHLER(WENN(ZÄHLENWENN($L12:$X12;LINKS(AG$2)&":")=2;INDEX(INDEX($L12:$X12;VERGLEICH(LINKS(AG$2)&":";$L12:$X12;0)+1):$X12;VERGLEICH(LINKS(AG$2)&":";INDEX($L12:$X12;VERGLEICH(LINKS(AG$2)&":";$L12:$X12;0)+1):$X12;0)+1):
16    INDEX($L12:$X12;VERGLEICH(LINKS(AG$2)&":";INDEX($L12:$X12;VERGLEICH(LINKS(AG$2)&":";$L12:$X12;0)+1):$X12;0)+LINKS(INDEX($L12:$X12;VERGLEICH(LINKS(AG$2)&":";INDEX($L12:$X12;VERGLEICH(LINKS(AG$2)&":";$L12:$X12;0)+1):$X12;0))));"");"")}
Die 2., extralange Fml stellt nur einen Ansatz dar, wie man das Problem ggf mehrfach auftretender Folgen gleicher Elemente­Anzahl lösen könnte.
Luc :-?

Bild

Betrifft: AW: Die Fortsetzung zur sortierten Übernahme ...
von: erichm
Geschrieben am: 03.06.2015 09:03:51
Vielen Dank für die Rückmeldungen. Jetzt versuche ich mal den aktuellen Stand zu beschreiben.
1. Entscheidend für die Problemlösung ist zunächst meine Beispieldatei in diesem Beitrag: https://www.herber.de/forum/messages/1428441.html
2. Deswegen scheiden leider einige Lösungsmöglichkeiten aus, da einige in der Datei erkennbare Lösungsergebnisse nicht erfüllt sind, wie nach stehend beschrieben:
3. Die Auflistung der ermittelten Zahlenreihen muss immer in der gleichen Zeile erfolgen.
4. Die Beginnspalte der jeweiligen 2er, 3er oder 4er-Reihe muss ebenfalls immer die gleiche Spalte sein
5. Zwischen den ermittelten Zahlenreihen muss jeweils eine Leerspalte sein
Für die Lösung verwendbar sind nun:
der Code von Christian in diesem Beitrag:
https://www.herber.de/forum/messages/1428453.html
die Formeln von Luc, abschließend in diesem Beitrag:
https://www.herber.de/forum/messages/1428970.html
Den Code von Christian habe ich nun in dem Teilbereich der Reihen Ermittlung dreimal kopiert und die Variablen vntcnt (=2er,3er,4er) und k =Spalte) jeweils entsprechend fix definiert. Der Code läuft jetzt quasi 3mal nacheinander und kann auf weitere (5er,6er) problemlos ausgeweitet werden.
Offen bleibt hier, wie ich in den Zeilen herausfinden kann, wenn es mehrere 2er oder 3er gibt (war aus meiner Beispieldatei leider nicht ersichtlich).
Die Formeln von Luc beinhalten jetzt alles!! Allerdings ist es ein Problem, wenn die „zweite 4er-Reihe“ in einer anderen Zeile steht (im Beispiel in der Zeile 13); die müsste man quasi in der Zeile 12 ab Spalte AS anhängen.
Noch eine Frage zur Bearbeitung von Namen:
Wenn ich die hier langen Formeln in dem Namensmanager erfasst habe und ich will diese ändern, dann geht das nicht, weil der Cursor immer auf eine aktive Celle zugreift. Wie kann man das denn ändern??
Danke.
Nochmals allerbesten Dank an alle Beteiligten für die Erarbeitung der Problemlösung!!
mfg

Bild

Betrifft: Wirklich verwendbar dürfte eher mein nun ...
von: Luc:-?
Geschrieben am: 04.06.2015 03:19:30
EndErgebnis sein, Erich;
hierbei ist wie zuvor ebenfalls zwingend ein Zwischenergebnis mit Elemente­Anzahlen erforderlich. Die entsprd Tab muss bei 10 Original­Spalten und der dadurch möglichen MaxAnzahl von 5 (2er-)Folgen exakt 15 Spalten umfassen. Ich habe nun die benannte Fml ModPos so geändert, dass die nicht verfügbare UDF DiSum nicht mehr benötigt wird. Damit sind insgesamt nur noch die beiden verfügbaren UDFs VSplit und VJoin im Einsatz:

 ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1123456789101112131415161718192021222324252627
2Auftrag1Auftrag2Auftrag3Auftrag4Auftrag5Auftrag6Auftrag7Auftrag8Auftrag9Auftrag10 Zwischenergebniszeilen mit allen jeweils auftretenden anzahltext-getrennten Folgen aller Elementanzahlen 
313351678917116117200861 3:7892:116117         
487191198784789273590140141648 2:140141             
5948717273954281820929311314 3:717273            
6849850793611491492493494495496 2:8498506:491492493494495496      
77957166709154713887100487520                 
86495715940664364464570670731 3:6436446452:706707         
965596220520679219197336600608 2:205206             
10799802803804805806897494495496 5:8028038048058063:494495496      
11182778455232256257258945726446 3:256257258            
121251265335341112210211409410 2:1251262:5335342:11122:2102112:409410 
13125126127128129231232233234235 5:1251261271281295:231232233234235    
14529530531532727728729730732733 4:5295305315324:7277287297302:732733   
15FolgenPos⇒WENNFEHLER(WVERWEIS("Auftrag"&SPALTE($A3:$J3)+1;$A$2:$J$14;ZEILE($A2);0)-$A3:$J3=1;0)+2*WENNFEHLER($A3:$J3-WVERWEIS("Auftrag"&SPALTE($A$3:$J$3)-1;$A$2:$J$14;ZEILE($A2);0)=1;0)
16ModPos⇒VSplit(VJoin(WECHSELN(VSplit(WECHSELN(WECHSELN(VJoin(FolgenPos;"");"1";"#1");"#";"";1);"#");1;LÄNGE(VSplit(GLÄTTEN(WECHSELN(WECHSELN(VJoin(FolgenPos;"");0;" ");21;"2 1")))));"");"";1)
17RohZeile⇒VSplit(VJoin(WENN(FolgenPos=0;"";WAHL(FolgenPos;SPALTE($A3:$J3)&"000,";"";"")&$A3:$J3);",";-1)&WIEDERHOLEN(",";SPALTEN($A3:$J3)-1);",";1)
18L3:Z3[:Z14]:{=WENNFEHLER(WENN(WENNFEHLER(INDEX($A3:$J3;RohZeile/1000);0)=INDEX(RohZeile;SPALTE($A3:$O3)+1);INDEX(ModPos;RohZeile/1000)&":";RohZeile);"")}

Das sortierte Endergebnis wird dann hieraus ermittelt. Dabei wdn die einzelnen Zahlen­folgen anhand des TabKopfes der jeweiligen TeilTab zugeordnet. In allen TeilTabellen hat somit jede Einzel­folge eine feste Startzelle, die ggf auch einen Leer-String enthalten kann. Das ist immer dann für den Rest der TeilTabZeile der Fall, wenn in der jeweiligen Zeile der Zwischen­Ergebnis­Tab keine weiteren Folgen dieser Elemente­Anzahl mehr gefunden wdn. Ansonsten wdn alle in der Reihenfolge ihres Auftretens gelistet, wobei pro TeilTab stets dieselbe Fml als MatrixFml über die ganze TeilTabZeile verwendet wird. Pro TeilTab muss diese also jeweils neu aufkopiert wdn (auf die 1.Zelle der TeilTab, dann die ganze TeilTabZeile markieren und die Fml als MxFml abschließen). Die 1.Zeile kann dann in Gänze kopiert und in die Folgezeilen aller Ergebnis­Teil­Tabellen als Fml eingefügt wdn.
Die SpaltenAnzahl der Ergebnis­Teil­Tabb richtet sich nach der möglichen Maximal­Anzahl von Folgen der jeweiligen Elemente­Anzahl in der Original­Tab. Bei 10 Spalten ergeben sich so max 5×2 Spalten für 2er-Folgen usw:
 ABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBR
128293031323334353637383940414243444546474849505152535455565758596061626364656667686970
22er-Folgen (max 5)3er-Folgen (max 3)4er-Folgen (max 2)5er-Folgen (max 2)6er-Folgen (max 1)
3116117        789                              
4140141                                         
5          717273                              
6849850                                   491492493494495496
7                                           
8706707        643644645                              
9205206                                         
10          494495496              802803804805806           
11          256257258                              
121251265335341112210211409410                                 
13                           125126127128129231232233234235      
14732733                 529530531532727728729730                
15AB3:AK3[:AK14]; [analog AL3:AT14;AU3:BB14;BC3:BL14;BM3:BR14]:
16{=WENNFEHLER(INDEX($L3:$Z3;WENN(ZÄHLENWENN($L3:$Z3;LINKS(AB$2)&":")=0;"";INDEX(VSplit(VJoin(WENN(LINKS(AB$2)&":"=$L3:$Z3;SPALTE($B3:$P3);"");;1);;1);(SPALTE($A3:$Z3)+LINKS(AB$2)-1)/LINKS(AB$2))+VSplit(WIEDERHOLEN(LINKS("012345";--LINKS(AB$2));GANZZAHL(10/LINKS(AB$2)));"";1)));"")}

Evtl ebenfalls mögliche 7er-, 8er-, 9er- und 10er-Folgen habe ich hier nicht angenommen, diese können aber b.Bed nach dem gleichen Prinzip ergänzt wdn.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: meine Anmerkungen hierzu ...
von: der neopa C
Geschrieben am: 04.06.2015 12:32:56
Hallo Luc,
... dem "zwingend" in Deiner Aussage "hierbei ist wie zuvor ebenfalls zwingend ein Zwischenergebnis mit Elemente­nzahlen erforderlich" widerspreche ich, weil man es wahrscheinlich auch anders lösen könnte.
Erich hatte gestern früh noch als 5. Bedingung vorgegeben: "Zwischen den ermittelten Zahlenreihen muss jeweils eine Leerspalte sein" Dies ist für mich noch nicht eindeutig. Es könnte sein, dass er damit auch meint, dass er z.B. zwischen den einzelnen 2er Reihen eine Leerzelle haben will und dementsprechend auch bei den 3er, 4er und 5er.
Entgegen Deiner früheren Aussage im Thread, dass eine prinzipielle Lösung nur mit Hilfe von VBA/UDF realisierbar ist, halte ich eine reine Formellösung durchaus für möglich, allerdings vorausgesetzt, eine Leerspalte vor den jetzigen Daten in Spalte A ist zugelassen, Erich klärt die restlichen Unklarheiten. Dazu gehört zusätzlich auch, ob die Reihenfolge gleicher 2er und analog der anderen bis 5er Reihen zwingend dem Original entsprechen müssen oder sortiert sein sollen bzw. sein können und Erich solche überhaupt nutzen würde wollen ... und ich wenigstens eine Stunde zusammenhängende ungestörte Zeit habe. Letzteres scheint mir allerdings das problematischste zu sein.
Gruß Werner
.. , - ...

Bild

Betrifft: Na, eigentlich sollte doch mit seinem letzten ...
von: Luc:-?
Geschrieben am: 04.06.2015 13:55:21
…Kommentar alles klar sein, Werner;
übrigens ist das von mir Dargestellte auch eine reine Formellösung! Ich weiß ja nicht, warum ihr reinen Fml-Freaks euch immer so schwer tut, wenn auch euer Hobby erleichternde UDFs ins Spiel kommen?! Mit den uralten XLM-Fktt, die, wie ja schon ihr Name sagt, reine Makros im alten Xl-vor-VBA-Sinn sind, habt ihr ja offensichtlich keine Probleme! Erst recht nicht mit fremd­pgmmierten Xl-Fktt, Hauptsache, sie gehören zum offiziellen (bzw offiziösen) Office-Bestand! :-|
Das Zwischenergebnis ist für meine Lösung zwingend erforderlich (wg der Anwendung des von dir so „heiß geliebten“ INDEX), nicht für irgendwelche ungeborenen anderen. Die wdn dann ihre eigenen Hilfszellen­Felder verlangen. Allerdings weiß ich nicht, wer sich die Mühe machen will, das alles Zelle für Zelle mit womöglich ellenlangen u/o lauter verschiedenen Fmln durchzugehen (um ggf MxFmln und INDEX zu vermeiden)…! Du willst mit einer Stunde auskommen? Na dann viel Erfolg, wenn du sie denn hast. Viel Zeit ist dafür wohl jedenfalls nicht mehr…
Eine reine VBA-KomplettLösung u/o eine andere Organisation wäre wahr­scheinlich rationeller und auch schneller, erfordert aber Pgmmier­Aufwand, während meine UDFs bereits anwendungs­bereit vorliegen — eben wie eine ganz normale XlFkt auch! Um das zu zeigen, habe ich das so gemacht! Die Komplett­Pgmierung interessiert mich weniger, da habe ich interessantere Projekte… ;-)
Erich sprach übrigens zuletzt von festen Startzellen für die Folgen, was ich hiermit realisiert habe.
Gruß, Luc :-?

Bild

Betrifft: im Prinzip schon ...
von: der neopa C
Geschrieben am: 04.06.2015 16:02:52
Hallo Luc,
... doch es gibt zumindest den Widerspruch, dass bei Dir im Endergebnis noch die Leerzellen zwischen den den einzelnen gleichen Folgen noch fehlen (Nr. 5 von Erichs letzten Vorgaben)
Zum Anderen wieder hier nur (m)ein Vergleich (wie immer hinkend). Als Radfahrer können sich Fahrrad-, E-Bike-, Moped-, ... Motorrad-..., Autofahrer ... bezeichnen. Wenn nur irgend vertretbar fahre ich Fahrrad. Damit bin ich bei Kurzstrecken ausreichend schnell und immer flexibel.
Gruß Werner
.. , - ...

Bild

Betrifft: Ist das eine 'Kurzstrecke', ...
von: Luc:-?
Geschrieben am: 04.06.2015 16:25:07
…Werner;
dann müsstest du doch schon angekommen sein?! Und Kurzstrecken bergauf können auch recht kraft­raubend sein… ;-]
Die LeerSpalten zwischen den FolgeTypen habe ich wieder rausgeworfen, da ich sie nach seiner letzten Bemerkung für überflüssig halte. Es sollte allemal (für ein FolgePgm)* reichen, dass sie in immer gleichen Spalten beginnen. Leer­Spalten sind im End­Ergebnis also nur fürs Auge des Betrachters und damit überflüssig. Ansonsten könntest du meiner Beschreibung entnehmen, dass ihre Einfügung kein Problem wäre, da jeder TypBlock seine eigenen (in allen Blöcken identischen) MxFmln hat. Selbst die Einfügung von Leer­zellen zwischen den Folgen eines Blocks wäre kein Problem, denn so habe ich ja das Zwischen­Ergebnis erzeugt.
* Es sei denn, das FolgePgm verwendet die ganze Zeile (ab 1.Spalte EndErgebnis) und setzt für eine tatsächlich leere Zelle ein spezielles Ende­Zeichen. Aber dann könnte man das auch schon hier machen und einen ErgebnisText erzeugen.
Luc :-?

Bild

Betrifft: ist es nicht & das andere hab ich überlesen owT
von: der neopa C
Geschrieben am: 04.06.2015 16:40:02
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Ist das eine 'Kurzstrecke', ...
von: erichm
Geschrieben am: 07.06.2015 13:10:01
Hallo,
leider kann ich mich erst jetzt melden.
Also an der Diskussion kann ich mich irgendwie nicht beteiligen, da kenne ich mich zu wenig aus.
Als Ergebnis erfüllt diese Lösung aber alle Voraussetzungen:
https://www.herber.de/forum/archiv/1428to1432/t1428418.htm#1429196
Leider gelingt mir aber die Umsetzung nicht; das hängt wohl an dem Fml's die ich nicht richtig erfasse - jeweils als Name?
Bei mir wird immer der aktive Cursor angezeigt; siehe "Tabelle test":
Userbild
Da würde ich noch eine Hilfe benötigen; die Formeln bringen im Ergebnis derzeit lauter leere Zellen; auch keine Fehlerhinweise oder #NV o.ä..
Deswegen Thread nochmal auf "nicht gelöst" gesetzt.
Ansonsten kann ich den weiter oben beschriebenen Code als Ersatzlösung genauso verwenden.
Besten Dank nochmals.
mfg

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: Christian
Geschrieben am: 30.05.2015 13:43:04
hallo Erich,
ist ja ein bisschen viel, was du da gerne hättest. Das klingt doch sehr nach eine Komplettlösung, sprich Auftrags-Programmierung.
Das Grundprinzip hat Thorsten dir ja aufgezeigt, wobei man das Array direkt in den entspr. Bereich schreiben kann und nicht Eintrag für Eintrag abklappern muss. Eleganter wäre Dictionary statt Array, aber der Code von Thorsten funktioniert ja erst mal und darauf kommt es in erster Linie an.
Hier ein paar Tipps zu deinen Fragen:
- für die Eingabe von variablen Start- und Endspalten der Auftragnummern kannst du zB. InputBoxes verwenden - zu InputBox: siehe VBA-Hilfe
- ebenso für die Eingabe der Ergebnisspalte
- wenn du zuvor auswählen willst, wie viele aufeinanderfolgende Nummern berücksichtigt werden sollen, geht das auch wieder via InputBox.
Bei Start des Makros ist es aber müßig, erst mal 4 InputBoxes auszufüllen. Als Alternative würde sich ein Userformular mit entsprechenden Eingabe- und Auswahlfeldern anbieten.
Das Forum bietet Antworten auf konkrete Fragen zu einzelnen Punkten. Der Fragesteller ist gefordert, die Antworten zu verstehen und nachzuvollziehen und auf seine Bedürfnisse anzupassen / zu erweitern. Sollten dabei wieder Fragen enstehen, ist an erster Stelle das Archiv und im Weiteren das Forum die die richtige Adresse.
Komplettlösungen musst du bei einem Programmierer beauftragen.
Viel Grüße
Christian

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: erichm
Geschrieben am: 30.05.2015 14:15:38
Danke Christian für die Rückmeldung und Erläuterungen.
Mit einer Inputbox oder einem User-Formular komme ich dann schon klar.
Das Problem liegt momentan eben da dran, dass meine VBA-Kenntnise für den Code nicht ausreichen. Derzeit listet der Code alle aufeinanderfolgenden Zahlen auf.
Ich weiß leider nicht wo und wie ich die Einschränkungen auf zwei oder drei oder vier Zahlenfolgen einfügen muss bzw. ändern muss.
Die abschließende Komplettlösung muss ich mir selber zusammenbauen - aber bei den Zwischenschritten hakt es momentan.
mfg

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: Christian
Geschrieben am: 30.05.2015 16:24:05
hallo Erich,
Das Problem liegt momentan eben da dran, dass meine VBA-Kenntnise für den Code nicht ausreichen.

An welcher Stelle hängt's denn? Bedingungen?, Schleifen?, Arrays?
Wobei du die genannte Aufgabe in VBA auch ohne Arrays lösen könntest, ist dann bestimmt etwas umständlicher und langsamer, aber das ist ja erst mal nicht relevant.
Um dich nicht im Regen stehen zu lassen, hier ein Ansatz mit einem Dictionary (assoziatives Array).
Zu deiner Frage "Ich weiß leider nicht wo und wie ich die Einschränkungen...":
Siehe hierzu im Code die Kommentarzeile: "wenn Anzahl der Vorgabe entspricht:"

Option Explicit
Sub TestIt()
    Dim objRes As Object
    Dim blnFnd As Boolean
    Dim i As Long, j As Long, k As Long
    Dim vntCnt As Variant
    
    vntCnt = InputBox("Anzahl aufeinanderfolgende Nummern:", , 3)
    If Not IsNumeric(vntCnt) Or vntCnt < 2 Then
        MsgBox "Zahl > 1 eingeben", 16, "Abbruch"
        Exit Sub
    End If
    
    Set objRes = CreateObject("Scripting.Dictionary")
    
    With ThisWorkbook.Sheets("Tabelle1")
        .Cells(2, 12).Resize(.Rows.Count - 1, 10).ClearContents
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            blnFnd = False
            k = 12
            For j = 1 To 10
                If .Cells(i, j + 1) = .Cells(i, j) + 1 Then
                    objRes(.Cells(i, j).Value) = 0
                    blnFnd = True
                Else
                    If blnFnd Then
                        objRes(.Cells(i, j).Value) = 0
                        ' wenn Anzahl der Vorgabe entspricht:
                        If objRes.Count = CLng(vntCnt) Then
                            .Cells(i, k).Resize(, objRes.Count) = objRes.Keys
                            k = k + objRes.Count
                        End If
                        objRes.RemoveAll
                        blnFnd = False
                    End If
                End If
            Next
        Next
    End With
    
    Set objRes = Nothing
End Sub
Grüße
Christian

Bild

Betrifft: AW: Zahlenkette finden und auflisten
von: erichm
Geschrieben am: 30.05.2015 19:58:55
DANKE für die Erweiterung; kann erst morgen dazu Stellung nehmen; melde mich auf alle Fälle!
mfg

Bild

Betrifft: mit nur einer Formel und ganz ohne {} lösbar ...
von: der neopa C
Geschrieben am: 30.05.2015 17:11:52
Hallo Erich,
... allerdings wird hierbei schon eine Matrixfunktionalität und zwar die der Funktion AGGREGAT() genutzt (geht so erst ab ExcelVersion 2010; in früheren Versionen geht es mit einer analogen {}-Formel; Du hast aber schon XL2013) und eine zusätzliche Leerspalte in Spalte A eingeführt (um die Formel nicht unnötig lang zu machen). Diese ist natürlich ausblendbar.
Formel M2 einfach nach rechts und und kopieren. Warum als dafür VBA?
Ergänzender Hinweis: Sollten mehrere entsprechende Zahlenreihen in einer Zeile stehen, werden diese unmittelbar hintereinander gelistet.

 ABCDEFGHIJKLMNOPQ
1 Auftrag1Auftrag2Auftrag3Auftrag4Auftrag5Auftrag6Auftrag7Auftrag8Auftrag9Auftrag10 Ergebnisspalten 
2 1335167891754311876861 789  
3 8719119878478927359014067648      
4 948717273954281820929311314 717273  
5 849453793611381809493494495496 493494495496 
6 7957166709154713887100487520      
7 6495715940664364464570693831 643644645  
8 65596220548379219197336600608      
9 799802153288828385897494701230      
10 182778455232256257258945726446 256257258  
11                 

Formeln der Tabelle
ZelleFormel
M2=WENNFEHLER(INDEX(2:2;1+AGGREGAT(15;6;SPALTE(2:2)/(($C2:$L2-$B2:$K2=1)+($B2:$K2-$A2:$J2=1)>0); SPALTE(A1))); "")


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

Bild

Betrifft: AW: mit nur einer Formel und ganz ohne {} lösbar ...
von: erichm
Geschrieben am: 30.05.2015 19:57:42
Danke für die Alternative.
VBA: Grundsätzlich angedacht damit die Datei mit sukzessiver Weiterentwicklung nicht zuviel Speicher benötigt und beim Rechnen nicht zu lamgsam wird (hängt natürlich auch von alternativen Formeln ab.
mehrere Zahlenreihen nacheinander: das können wir leider nicht weiterverarbeiten; da benötigen wir eine Trennung und die erste Zahl einer Zahlenreihe (2er oder 3er muss immer in der gleichen Spalte beginnen (siehe Beispieldatei).
mfg

Bild

Betrifft: AW: Zahlenkette finden und auflisten mit Makro
von: Daniel
Geschrieben am: 31.05.2015 17:36:31
Hi
probier mal folgenen Code, passend für deine Beispieldatei:

Sub test()
Dim arr
Dim erg
Dim Zähler As Long
Dim z As Long
Dim s As Long
Dim s1 As Long
Dim Check
Zähler = 0
ReDim erg(1 To 4, 1 To 1)
'--- Zahlenfolgen ermitteln
arr = Cells(1, 1).CurrentRegion
For z = 2 To UBound(arr, 1)
    For s = 1 To UBound(arr, 2) - 1
        If arr(z, s) + 1 = arr(z, s + 1) Then
            Zähler = Zähler + 1
            ReDim Preserve erg(1 To 4, 1 To Zähler)
            erg(1, Zähler) = z
            erg(2, Zähler) = s
            erg(4, Zähler) = arr(z, s)
            For s1 = 1 To UBound(arr, 2) - s
                If arr(z, s) + s1 = arr(z, s + s1) Then
                    erg(3, Zähler) = s1 + 1
                    erg(4, Zähler) = erg(4, Zähler) & ";" & arr(z, s + s1)
                Else
                    Exit For
                End If
            Next
            s = s + s1 - 1
        End If
    Next
Next
'--- Ergebnis zuückschreiben
With Cells(1, 1).End(xlToRight).Offset(0, 2)
    .Resize(, 4).Value = Array("Zeile", "ab Spalte", "Anzahl", "Aufträge")
    .Offset(1, 0).Resize(UBound(erg, 2), UBound(erg, 1)) = WorksheetFunction.Transpose(erg)
    '--- Aufträge einzeln in Zellen verteilen
    With .Offset(0, 3).EntireColumn
        .TextToColumns _
            Destination:=.Cells(1, 2), DataType:=xlDelimited, _
            Tab:=False, Semicolon:=True, Comma:=False, Space:=False
    End With
            
End With
End Sub
die Ergebnisdarstellung erfolgt automatisch am Ende der Tabelle mit einer Spalte abstand.
die Darstellungsform ist etwas anders, es werden alle Folgen untereinander mit Angabe von
Zeile, Spalte ab, Anzahl der Folge und die Aufträge der Folge
dargestellt.
das weicht jetzt etwas von deiner form ab, aber anders gehts nicht, wenn innerhalb einer Zeile Folgen mit gleicher Anzahl auch mehrfach vorkommen können.
Gruß Daniel
https://www.herber.de/bbs/user/97954.xlsm

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zahlenkette finden und auflisten"