Office: Spalte nach Namen durchsuchen und Ergebnisszeile nach Wert durchsuchen

Hallo zusammen,

nachdem ich lange stiller Mitleiser war, habe ich aktuell ein Problem bei welchem ich nicht weiterkomme und auf eure Hilfe hoffe....

Ich möchte im Tabellenblatt "Historie" in Spalte A den Namen aus Tabellenblatt "Plan" Spalte A suchen. In der Ergebniszeile soll dann die im Tabellenblatt "Plan" in Zeile 17 hinterlegte Station gesucht werden und bei einem Treffer die Zeile 20 der jeweiligen Spalte in das Tabellenblatt "Plan" zurückgeschrieben werden. Ich möchte zur Verdeutlichung ein Beispiel modellieren:

Das Makro sucht im Reiter "Historie" den Namen Alex. Dann durchsucht es die Zeile nach dem Wert 0 aus Zeile 17. Diese wird in Spalte C gefunden und somit in die Zelle B3 im Reiter "Plan" die Zahl 26 weggeschrieben. Dann wird in der Ergebniszeile nach der 1 gesucht. Da diese nicht vorkommt geht es mit der 3 weiter usw.

Ich hoffe das ihr aus meinem Text schlau werdet und mir vielleicht einen Lösungsansatz geben könnt.

Danke schon im voraus!

Grüße Anja

:)

 

Hi, was das als Denksportaufgabe gedacht? Oder welchen Zweck erfüllt das?
Versuchs mal mit diesem Code:

Code:

 

Hi,

das ist eine simple Formel:

Plan!B3:

=WENNFEHLER(INDEX(Historie!$B$20:$I$20;VERGLEICH(B$17;Historie!$B3:$I3;));"")

und bis K15 kopieren.

 

... vielleicht irre ich mich, aber setzt das nicht voraus, dass die Namen in beiden Blättern in der gleichen reihenfolge stehen?
Ich hatte die Anforderung so verstanden, dass der Name erst gefunden werden muss und dann die jeweilige Zeile bestückt wird.

 

Danke für die Antworten. Wie schafft man es in so kurzer Zeit ein Makro zu schreiben *eek.gif*

Nein die Aufgabe hat tatsächlich eine sinnvolle Relevanz*Smilie
Ich habe einen Großteil bereits mit Excelformeln umsetzen können. Aus diesem Grund ist diese Aufgabe ein wenig aus dem Kontext gerissen. Leider ließ sich die beschriebene Anforderung nicht per Excelformel lösen da ich immer mal wieder Zeilen lösche. Dann verschiebt sich der Matrixbezug.
Die Reihenfolge der Namen ist höchstwahrscheinlich immer gleich. Da hab ich mich ein wenig missverständlich ausgedrückt...

@regina: Ich werde deinen Code wenn ich zu Hause bin gleich ausprobieren.

Grüße

 

Hi Regina,

in der Beispieldatei war die Reihenfolge identisch. Aber auch das ist kein größeres Problem. Muss man die passende Zeile eben auch noch variabel ermitteln:

Plan!B3:
=WENNFEHLER(INDEX(Historie!$B$20:$I$20;VERGLEICH(B$17;INDEX(Historie!$B$3:$I$15; VERGLEICH($A3;Historie!$A$3:$A$15;););));"")

und bis K15 kopieren.

Und auch das Argument mit Löschen von Zeilen in Verbindung "Verschiebung des Matrixbezugs" lässt sich problemlos formulieren. Dazu würde ich aber gerne genau hören, was wann passieren kann bzw. worin dann das Problem bestehen soll.

 

Hi,

@regina: ich hab dein Makro ausprobiert.
Leider bekomme ich es nicht zum laufen obwohl es in meiner Beispieldatei super arbeitet. Es findet einfach keine Werte.
Ich hab dir die Datei einmal angehangen. Könntest du vielleicht schauen ob du das Problem entdecken kannst?

@Boris: Ich hatte mit WVerweis gearbeitet was auch gut funktioniert. Dann kam aber die Zusatzanforderung, das in dem Reiter Historie immer mal eine Zeile entfernt wird. Somit rutschen die unteren Zeilen nach oben und mein Zeilenindex hat nicht mehr gepasst.
Dann war ich der Annahme das hier ein Makro her muss.... Aber ich hab das Gefühl das per Formel doch so einiges möglich ist *Smilie

Danke für eure Hilfe.

 

... könnte es evtl. daran liegen, dass die Suchspalten nicht in Zeile hier nicht in Zeile 20 und 17, sondern in 66 und 61 liegen? Ein identischer Aufbau der Beispiel und der Hilfsdatei wäre schon gut ...

 

Ja ich habe nur eine Beispieldatei hochgeladen um die Problemstellung zu verdeutlichen. Ich war der Meinung das Spalten und Zeilenanzahl variabel sind und ich dein Makro entsprechend anpassen kann.
Der Aufbau der beiden Dateien ist ja gleich. Darum verstehe ich nicht warum ich das Makro nicht entsprechend adaptieren kann.

Sorry wenn das für Verwirrung gesorgt hat.

 

Hi,

sorry - aber vergiss das Makro - so gut und liebevoll es auch programmiert ist. Das ist eine total KLASSISCHE Formellösung.
Warum möchtest Du diese nicht verwenden?

 

Hi,

Deine Formel in Hilfsmatrix!B6 lautet nun:

=WENNFEHLER(INDEX(Historie_Einsatzplan!$B$61:$P$61;VERGLEICH(B$66;INDEX(Historie _Einsatzplan!$B$6:$P$51; VERGLEICH($A6;Historie_Einsatzplan!$A$6:$A$51;0);0);0));"")

Kopieren bis AC51.

Glaub mir: Das ist eine Aufgabe für eine FORMEL und nicht für VBA.

 

Hi,
ob Du nun die Formel oder die VBA-Lösung nutzt, bleibt Dir rüberlassen. Ich habe den Code nochmal angepasst, so dass Du jetzt beliebig Spalten und Zeilen hinzufügen bzw. löschen kannst. Die Anforderung hatte ich zuerst so nicht wahrgenommen.

 

Guten Morgen,

danke für eure beiden Lösungen. Ich habe beide ausprobiert. Sie machen zu 95% was sie sollen. Aber leider nicht zu 100%. Zum Beispiel bleibt bei beiden H46 leer obwohl hier die 74 zurückgemeldet werden müsste. Könnt ihr mir sagen woran das liegt?
@regina: Das Makro gibt auch nicht den ersten Treffer von links zurück sondern manchmal den ganz rechten manchmal einen mittleren. Eine Systematik ist für mich nicht erkennbar.
@Boris: Ich habe gar nichts gegen Formeln. Hab nur leider die Anforderung allein nicht in einer Formel verpacken können *wink.gif*

Liebe Grüße und einen guten Start in die Woche Anja

 

Die Formel von Boris dürfte genau das machen, was Du willst. Ist Dir eigentlich aufgefallen, dass der Name "Zim" zweimal vorkommt? Dadurch ergibt sich das falsche Ergebnis in H46.

 

Ach ja.... Montag Morgen. Danke für den Hinweis. Jetzt kann die Formel auch sauber arbeiten. Super!!!!

Ich habe noch ein kleines Problem. Und zwar ändert sich immer mal der Mitarbeiterkreis. Dann habe ich folgendes per Makro eingepflegt: Wenn in Workshhet Spalte B (Namen) geändert wird lösche mir bitte die komplette entsprechende Zeile in Arbeitsplatt "Historie_Einsatzplan". Dies funktioniert super. Nur Leider löscht er mir in der Spalte A "Historie_Einsatzplan" auch die Zelle welche ich anwähle zum kopieren. Wäre es möglich nur die Zeile, bei welcher der Name gelöscht wird, zu löschen?

Mein Makro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
ergebnis = MsgBox("M?chtest du den Mitarbeiter und die archivierten _ Sationen wirklich loeschen?", vbYesNo)
If ergebnis = vbYes Then
Sheets("Historie_Einsatzplan").Cells(Target.Row, 1).EntireRow.Delete
End If
End If

End Sub