Excel Leerzellen löschen und Liste erstellen

[worrest-t]

Hallo liebe Forengemeinde,

ich habe eine Tabelle, in der aus einer anderen Arbeitsmappe Schüler angezeigt werden, die Zeiten nacharbeiten müssen. Angezeigt werden also immer Namen und Fehlzeiten (Ist/Soll). Da die Namen aus einer Klassenliste angezeigt werden, sind dazwischen immer Leerzeilen und Sie stehen an der Stelle, in der Sie auch in der Klassenliste stehen würden.

Was ich jetzt brauche ist eine Formel, welche mir die Namen und Fehlzeiten kopiert und geordnet zu einer Liste ohne Leerzellen an einer anderen Stelle anzeigt.

Ich hoffe ihr versteht was ich meine und könntet mir weiterhelfen. Hab unten die Datei mal angehängt (Namen sind verändert und dadurch die Formel in den Zellen ersetzt).

Schönen Gruß

Christian

[GMG-CC]

Moin,
die Liste strotzt vor #BEZUG! - Fehlern, da die Verknüpfung fehlt.
Kannst/darfst/willst du Power Query -> http://www.excel-ist-sexy.de/power-query-das-add-in/ nutzen? Dann lässt sich das damit gut lösen.
Natürlich auch mit VBA. Und wahrscheinlich auch mit mehr oder weniger transparenten Formeln.

[worrest-t]

Hallo Günther,

oh das mit den Bezugsfehlern habe ich nicht vorausgesehen. Aber eigentlich ist es eben eine Liste von Namen, wo eben dazwischen Leerzellen sind. Diese sollen schön geordnet werden, so dass man schnell Namen auswendig machen kann und auch die Anzahl weiß.

Ich nutze Excel auf einem Mac und habe die Version 2016. Sollte Power Query hier möglich sein, bin ich offen dafür. VBA bin ich auch offen dafür, nur hätte ich eigentlich gerne eine Lösung, die ich nachvollziehen kann und bei VBA habe ich gemerkt, dass ich Excel nicht sooo häufig nutze, dass sich ein Lernen der Sprache lohnen würde.

Edit: Habe die Arbeitsmappe noch von den Formeln befreit, sodass man sie bearbeiten kann. Nur wird die Liste eben von einer Wenn Funktion immer dynamisch mit Daten gefüllt - falls das für die Weiterbearbeitung relevant sein sollte.

Schönen Gruß

Christian

[snb]

Code:

Sub M_snb()
   sn = Filter([transpose(if(B4:B34="",,A4:A34))], "0", 0)
   If UBound(sn) > -1 Then Cells(3, 11).Resize(UBound(sn) + 1) = Application.Transpose(sn)
   sn = Filter([transpose(if(E4:E34="",,D4:D34))], "0", 0)
   If UBound(sn) > -1 Then Cells(3, 12).Resize(UBound(sn) + 1) = Application.Transpose(sn)
   sn = Filter([transpose(if(H4:H34="",,G4:G34))], "0", 0)
   If UBound(sn) > -1 Then Cells(3, 13).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

[WillWissen]

Hi Christian,

mit Formeln und je einer Hilfsspalte könntest du das so lösen:

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Nacharbeiten

Liste mit Personen, die Nacharbeiten müssen

2

17a

17b

17c

3

17a

Soll

Ist

HS

17b

Soll

Ist

HS

17c

Soll

Ist

HS

Meyer

Löwe

Lange

4

Schulze

Meyermüller

5

Müller

Kamir

6

Löwe

6

Leopold

7

Kaiser

8

9

10

11

Lange

11

12

13

14

Meyermüller

14

15

16

17

Zelle	Formel
N3	=WENNFEHLER(INDEX($A$1:$A$33;KKLEINSTE($D$4:$D$33;ZEILE()-2));"")
O3	=WENNFEHLER(INDEX($E$1:$E$33;KKLEINSTE($H$4:$H$33;ZEILE()-2));"")
P3	=WENNFEHLER(INDEX($I$1:$I$33;KKLEINSTE($L$4:$L$33;ZEILE()-2));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013

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

Die Hilfsspalten kannst du einfach ausblenden.

Nachtrag:

Habe gerade festgestellt, dass ich versehentlich die Formeln in den Hilfsspalten nicht mitangegeben habe. In D4 gehört diese Formel rein und entsprechend runterziehen. Auf die anderen Hilfsspalten musst du sie halt noch anpassen.

Code:

=WENN(A4="";"";ZEILE())

[snb]

Ohne Hilsfspalten:

in K3: Array Formel:

PHP-Code:

=IFERROR(INDEX($A$4:$A$34;SMALL(IF($B$4:$B$34<>"";ROW($1:$30));ROW()-2));"") 


In L3

PHP-Code:

=IFERROR(INDEX($D$4:$D$34;SMALL(IF($E$4:$E$34<>"";ROW($1:$30));ROW()-2));"") 


in M3

PHP-Code:

=IFERROR(INDEX($G$4:$G$34;SMALL(IF($H$4:$H$34<>"";ROW($1:$30));ROW()-2));"") 


[GMG-CC]

Moin Christian,

nein, Mc.Xcel ist leider kastriert und da gibt es so einige "Nettigkeiten" der Win-Version nicht. So leider auch kein Power Query ...  :@

[worrest-t]

Hallo zusammen,

habe gerade Günthers Lösung ausprobiert und es klappt suuuuper und ist für mich auch reproduzierbar - auch wenn leider nicht ganz nachvollziehbar, aber daran arbeite ich jetzt mal.

Ich danke ganz herzlich für diese tolle Lösung!!!!

Schönen sonnigen Abend

Christian

[snb]

Günthers Lösung

Wie, was,  wo ?

[RPP63]

Er meint =WECHSELN("Günther";"h";"")
:21: