Clever-Excel-Forum

Hi alle zusammen,
hab mein Glück bereits selber schon versucht aber das umbasteln der Formeln klappt halt leider dann doch noch nicht immer wie ich es gern möchte.

ich bräuchte eine Formel, wobei wohl eher ein VBA Code sinnvoller wäre, der mir folgenden Arbeitsschritt ausführt:

in "Tabelle 1"

Spalte: F , L , R, X, AD, AJ, AP, AV, BB, BH, BN und BT
ab Zeile "14" bis "100" nach dem Wert "F" durchsuchen.

Wenn der Wert in einer Zelle vorkommt, die 3 Zellen vor diesem Wert kopieren und in einer Liste in "Tabelle 2" eintragen

Bsp:
in Zelle:  "F18" kommt der Wert "F" vor
dann "C, D, E" kopieren und in "Tabelle 2" eintragen.


Alle Zellen die Kopiert werden, egal aus welcher Spalte der Wert "F" stammt. können in "Tabelle 2" einfach untereinander eingetragen werden.

hoff ich habs gut erklärt...

damit Ihr nicht überlegen müsst, welche 3 Zellen vor den Spalten liegen, habe ich Sie euch hier mal aufgelistet:

C D E : F
I J K : L
O P Q  : R
U V W : X
AA AB AC : AD
AG AH AI : AJ
AM AN AO : AP
AS AT AU : AV
AY AZ BA : BB
BE BF BG : BH
BK BL BM : BN
BQ BR BS : BT

Tausen dank schonmal an die, die sich der Sache annehmen.

Gruß
Stefan

A101: f

B101: {=MIN(WENN((REST(SPALTE($F$14:$BT$100);6)=0)*($F$14:$BT$100=A101);SPALTE($F$14:$BT$100)+ZEILE($F$14:$BT$100)%%))} 
kein {} eingeben, sondern Formel mit Strg-Umsch-Eingabe abschließen

C101[:E101]: =INDEX($A:$BZ;REST($B101;1)/1%%;$B101-4+SPALTE(A101))

Eine Tabellenformel lässt sich auch in VBA verwenden.

Hallöchen,

das könnte man so lösen. Der Ansatz geht davon aus, dass F nur ein mal pro Spalte enthalten ist. Kommt es öfter vor, müsste man den Code erweitern.

guten morgen ::)

is ja spitze ... da steht ma auf, die sonne scheint endlich mal wieder seit jahren und man hat zwei antworten zu seinem problem  :21:

@LCohen ich bin mir sicher dein Ansatz funktioniert, allerdings bin ich mir unsicher wo ich das ganze eingeben soll  
alles in den code von Tabelle 1 schreiben oder ein Teil auch in die Zelle ?

@schauan
den Code hab ich in Tabelle 1 geschrieben und wenn ich das ganze ausführen lass funktioniert er.
leider kann  "F" auch öfters in einer Spalte vorkommen .. sorry das hab ich dann wohl vergessen zu erwähnen

PS: mir is gerade aufgefallen, das wenn eine der 3 zellen geändert wird, oder das F gelöscht wird sie neu aufgelistet bzw nicht gelöscht werden in Tabelle2.
aber evtl hängt das damit zusammen das F häufiger vorkommen kann.

Hallo Stefan,

kannst es ja mal damit probieren:

Sub F_Kopieren_Kuwer()

 'Variablendeklaration

 Dim lngZeile As Long

 Dim rngSpalten As Range

 Dim rngFund As Range

 Dim strFundadresse As String

 

 'Bereich definieren

 Set rngSpalten = Application.Intersect(Rows("14:100"), Range("F1, L1, R1, X1, AD1, AJ1, AP1, AV1, BB1, BH1, BN1, BT1").EntireColumn)

 

 With rngSpalten

   Set rngFund = .Find(What:="F", _

                                 After:=.Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Count), _

                                 LookIn:=xlValues, _

                                 LookAt:=xlPart, _

                                 SearchOrder:=xlByRows, _

                                 SearchDirection:=xlNext, _

                                 MatchCase:=False, _

                                 SearchFormat:=False)

 

   If Not rngFund Is Nothing Then

     strFundadresse = rngFund.Address

     

     lngZeile = 2

     Sheets("Tabelle2").Cells(lngZeile, 1).Resize(Rows.Count - lngZeile, 3).Value = ""

     

     Do

       Sheets("Tabelle2").Cells(lngZeile, 1).Resize(, 3).Value = rngFund.Offset(, -3).Resize(, 3).Value

       lngZeile = lngZeile + 1

       Set rngFund = .FindNext(rngFund)

     Loop While Not rngFund Is Nothing And strFundadresse <> rngFund.Address

   Else

     MsgBox "Keine Zellen gefunden.", vbInformation

   End If

 End With

End Sub

Gruß Uwe

Hallo Lion,


da auf der Zieltabelle nicht steht, woher die Daten kommen - außerdem sollen ja alle schön untereinander - wird es fast unmöglich, sie auch wieder zu entfernen. Es sei denn, Du sagst jetzt, dass die Daten in jeder Zeile der verschiedenen Spalten in den 3 Zellen daneben mit 100%iger Sicherheit unterschiedlich sind. Dann könnte man Bei Änderung in einer der Zellen prüfen, ob die zugehörigen Daten dort sind und sie entfernen.

Oder Du schreibst in einer zusätzlichen Spalte die Herkunft ein.

Bei einer reinen Formellösung würde es auch so gehen. Eine Formel muss normalerweise an die Stelle, wo Du das Ergebnis haben willst es sei denn, sie erzeugt ein Zwischenergebnis. Ich habe die Formel von LCohen aber nicht ausprobiert ...

So Hi ...
sorry in letzter Zeit is so viel los das ich nich dazu gekommen bin hier zu Antworten. Aber ich hab den Code immerhin ausprobieren können und hab ihn soweit ich es rausgefunden habe auch erweiter   

1. der Code funzt an sich soweit super 

2. nachdem ich deinen nachträglichen Post gelesen habe, hab ich mal paar Testeingaben mehr durchgeführt und dabei ist mir folgendes aufgefallen.

Da das Jahr ja noch ganz Jung ist, gibt es im Moment noch recht wenig Zellen mit dem Wert "F"
wenn ich nun immer wieder einen entferne und das Makro neu laufen lasse, dann verschwinden die Einträge auch in Tabelle2.

NUR das letzte F, sollte es verschwinden wird nicht gelöscht. da kommt die Meldung : keine Zelle gefunden und der Eintrag bleibt bestehen.
Ich denke mal das du dass mit deinem letzten Post gemeint hast oder ?

Und ja ich kann mit ziemlicher Sicherheit sagen das auf alle Fälle 2 der 3 Spalten immer Unterschiedliche Werte haben werden.

Bsp: in Spalte H - wird der Wert "F" gesucht
dann sind die zwei daneben immer unterschiedlich ( G / F )
Evtl. steht in der Spalte E das ein oder andere mal öfters an der gleich Position.
Aber ich habe nun noch eine 4. Spalte hinzugemogelt in der immer Zwei Werte vorkommen aber bei jede Bereichsabfrage unterschiedlich sind. ( ich hoff das war jetzt verständlich :20: )

Ich post mal zur Sicherheit den Code damit ich auch nix kaputt gemacht habe :19: 



IngZeile 4 = die Zeile in der die ergebenisse einträgt
Cell(IngZeile 4 ) = hat aus irgend einem Grund mit der Spalte zu tun so wie ich das gesehen habe
Rows.Count - IngZeile, 7) ich bin mir echt nicht sicher funktioniert irgendwie mit jeder Zahl aber 7 is mit sicherheit falsch :D
und die anderen bedeuten bzw bestimmen die anzahl der Zellen neben dem Wert F die übernommen werden solln oder eben gelöscht werden ?!?! oder

Gruß Stefan - PS lach nich  :s    :21:

servus nomoi ... hab vorhin leider erst gesehen das ich dich voll übersehen habe kuwer und der code ja eigentlich von dir stammt.
ka warum ich des net gesehn hab. Auf jeden fall wollt ich mich damit kurz entschuldigen. war echt keine absicht.

Gruß Stefan