Drop Down Liste an Hand von Suchkriterien

[Sevemiyen]

Guten Abend,

ich habe es zwar in einem anderen Excel-Forum versucht aber da ist es irgendwie im Sande verweht. Hoffe Ihr könnt mir vlt. weiterhelfen.

Weil das Ganze bisschen kompliziert ist um es gegliedert und verständlich in Worte zu fassen habe ich eine selbsterklärende Beispieldatei in den Anhang beigefügt.

Grob gesagt:
Tabelle 1: Mitarbeiter & PLZ
Tabelle 2: *viele Daten gelistet pro Zeile mit:* PLZ - Ort - Anlaufstellenname - UID - Info1 - Info2 - Info3

3 relevante Funktionen bekomme ich nicht hin:
[Suchkriterium: erste 3 Stellen der PLZ in Tabelle 1 müssen mit der PLZ in Tabelle 2 übereinstimmen]
1: Automatisch eine zufällige Anlaufstelle an Hand des Suchkriteriums auswählen
2: Drag & Drop mit allen an Hand der Suchkriteriums passenden erstellen
3: Infos 1-3 der gewählten Anlaufstelle listen

Sorry vorab: Soll nicht so rüberkommen als würde ich euch die Ganze arbeit aufschieben und nur noch copy & paste.
Sitz echt schon seit Morgens um 7 an der Liste und hab echt soweit ich konnte vieles selbst gestemmt aber hier hören meine Latein einfach auf.

Optimal wäre es mit Formeln / Arrayformeln ohne Makros aber wenn es gar nicht anders geht nehme ich natürlich auch eine Lösung mit Makros.

Falls man mir weiterhelfen könnte wäre ich sehr dankbar.


  Beispiel.xlsx (Größe: 11,58 KB / Downloads: 7)

[shift-del]

Moin,

ist ja unerhört dass die Servicekräfte von Office-Loesung.de nicht in der Lage waren innerhalb von 12 Stunden eine zufriedenstellende Lösung zu liefern.

Ich habe mal ein Ticket bei der Hotline aufgemacht. Die Nummer ist: #R000000000001.

[Sevemiyen]

War nicht so negativ gemeint. Mir ist bewusst, dass die Schnittmenge der Stammuser zwischen den beiden Foren nahezu 100% ist.

Der „Umzug“ von dem Thread war hauptsächlich deswegen, dass ich mir hier nun mit meinem Stamm-Nick einen Acc. angelegt habe und nicht zwischen den beiden Foren hin und her switchen wollte.

Selbstverständlich ist keine 12-Stunden Erwartungshaltung da. Office-Lösung hat mir oft und immer umfangreich geholfen. Und weil ich eben an Hand der Nicks, Avatare und Signaturen auf einen Blick gesehen habe, dass hier nahezu alle Stammis ebenfalls aktiv sind war mein Gedanke nicht Richtung Wettbewerb sondern einfach eher Komfort alles unter einem Dach zu haben.

Sry, falls es missverständlich rüber kam.  :100:

[Jockel]

Wellcome to my I-list. Schon allein wegen der letzten Antwort...

[Sevemiyen]

  Wtf!? Was verpasse ich hier gerade? Es gibt 2 Foren in denen mehr oder weniger die selben User aktiv sind. Wieso reagiert ihr so allergisch wenn man einen Thread doppelt postet?

ich sah es eher im Sinne von: Rechte Tasche, linke Tasche > Es wandert ähh ins selbe Pool.
Weitergehend habe ich auch beim ersten Post direkt ehrlich und transparent geschrieben, dass die Anfrage schon in einem anderen Excel-Forum gepostet ist.

  Ist ja nicht so als hätte ich geschrieben „Die im anderen Forum kriegen es nicht hin, nur Noobs dort.“ o.Ä..

[Rabe]

Hi,

Du hast in der Liste der Anlaufstellen die ersten 3 Zeilen mit identischer PLZ, identischem Ort, identischer UID, aber unterschiedlichen Infos.

Wenn nun im Blatt "Basis" automatisch und zufällig eine dieser 3 Anlaufstellen ausgewählt würde, nach welchem Kriterium soll dann Excel entscheiden, ob es Eintrag 1, 2 oder 3 ist und dementsprechend die Info für Spalte E-G raussuchen?

MMn geht das nicht.

[RPP63]

Ich finde übrigens, dass Sevemiyen nichts falsch gemacht hat.
Eher das Gegenteil:
Ich habe bisher noch niemals eine so schlüssige Erklärung für ein Crossposting gelesen.

Von mir aus also Alles gut!
Gruß Ralf

[Sevemiyen]

@Rabe
Da hast du vollkommen recht. Denkfehler meinerseits. Ich habe nun einfach eine 9-Stellige und jeweils nur einmal vorkommende Hilfsspalte (Spalte J) zur Einordnung der Datensätze gemacht.

@RPP63
Danke fürs Verständnis.

[Rabe]

Hi,

ich habe die Datensatznummer-Spalte nach links vor die PLZ verschoben, dann die Daten in eine intelligente Tabelle mit Namen "Datentabelle" umgewandelt (STRG-L),
dann folgende Formeln in die Info-Zelle E2 in "Basis", diese nach rechts und unten ziehen:

Arbeitsblatt mit dem Namen 'Basis'
	A	B	C	D	E	F	G
1	Mitarbeiter	PLZ	Automatisch zufällig gewählte Datensatznummer	Optional selbst wählbare Datensatznummer	Anlaufstelle Info 1	Anlaufstelle Info 2	Anlaufstelle Info 3
2	xyz	70174	123456789		esfgrewf	fwefwef	breverb
3	zxy	70174		987654321	grgwvgwrvw	fwefwef	breverb
4	abc	73728

Zelle	Formel
E2	=WENNFEHLER(WENN($D2="";SVERWEIS($C2;Datentabelle;SPALTE(E$1);0);SVERWEIS($D2;Datentabelle;SPALTE(E$1);0));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

Eine Lösung für die zufällige Auswahl der Datensatznummer in Spalte C und das Dropdown in Spalte D habe ich noch nicht.

[Elex]

Hi
 
Tab: Basis   (Spalte B ist Text)
____|______A______|___B___|_________C________|_________D________|____E___|____F___|____G___|___H__|___I__|
   1|Mitarbeiter  |PLZ    |Zufällige Anlauf  |Optionale Anlauf  |Info 1  |Info 2  |Info 3  |HS 1  |HS 2  |
   2|xyz          |  73035|                  |                  |        |        |        |      |      |
   3|zxy          |  70174|                  |                  |        |        |        |      |      |
   4|abc          |  01067|                  |                  |        |        |        |      |      |
   5|hgf          |  73728|                  |                  |        |        |        |      |      |
   6|gfre         |  70174|                  |                  |        |        |        |      |      |
 
E2  =WENNFEHLER(WENN($D2="";INDEX(Anlaufstellen!D$2:D$40000;$H2;);INDEX(Anlaufstellen!D$2:D$40000;$I2;));"")
Nach rechts bis G2 und nach unten kopieren

H2  =WENNFEHLER(VERGLEICH(1*(LINKS(C2;9));Anlaufstellen!$G$2:$G$40000;0);"")
Nach rechts bis i2 und nach unten kopieren. Formel an die Länge der Datensatznummer anpassen. Im Moment (9)
 

Tab: Anlaufstellen  (Spalte A ist Text)
____|___A___|__________B__________|____C___|____D___|____E___|____F___|________G________|_H_|
   1|PLZ    |Ort                  |UID     |Info 1  |Info 2  |Info 3  |Datensatznummer  |   |
   2|  70174|Stuttgart            |  123456|st 1    |st 2    |st 3    |        123456789|   |
   3|  70174|Stuttgart            |  123456|st 4    |st 5    |st 6    |        987654321|   |
   4|  70174|Stuttgart            |  123456|st 7    |st 8    |st 9    |        147852369|   |
   5|  73033|Göppingen            |  454545|gö 1    |gö 2    |gö 3    |        415975321|   |
   6|  70174|Stuttgart            |  654321|st 10   |st 11   |st 12   |        963258741|   |
   7|  73035|Göppingen            |  898989|gö 4    |gö 5    |gö 6    |        415746894|   |
   8|  73728|Esslingen am Neckar  |  888888|ess 1   |ess 2   |ess 3   |        869874170|   |
   9|  01067|Dresden              |  333333|d 1     |d 2     |d 3     |        454545458|   |
  10|  01068|Dresden              |  333334|d 4     |d 5     |d 6     |        454545459|   |
 
Für Spalte C und D in Basis den Code in das Modul (Basis).

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim PBasis, DatenAnlauf As Variant
Dim dict As Object
Dim i, n, a, m As Long
Dim DatPrü As String
Dim rng As Range

'**************************** Doppelklick in Spalte C ************************
If Target.Column = 3 Then
  Set dict = CreateObject("Scripting.Dictionary")
  DatenAnlauf = Sheets("Anlaufstellen").Range("A1").CurrentRegion
 
  If Target.Address = "$C$1" Then
     Set rng = Range("B1", Range("B1").End(xlDown))
  Else
     Set rng = Target.Offset(-1, -1).Resize(2)
     m = Target.Row - 2
  End If
  PBasis = rng.Value
 
  For i = 2 To UBound(PBasis, 1) 'Vergleich ersten drei Stellen der PLZ
     For n = 1 To UBound(DatenAnlauf, 1)
        If Left(PBasis(i, 1), 3) = Left(DatenAnlauf(n, 1), 3) Then 'bei gleich Datensatznummer merken
           dict(a) = DatenAnlauf(n, 7) & " - " & DatenAnlauf(n, 1) & " - " & DatenAnlauf(n, 2) & " - " & DatenAnlauf(n, 3)
           a = a + 1
        End If
     Next n
     
     DatPrü = Join(dict.items, ",")
     If DatPrü <> "" Then
        With Cells(i + m, 4).Validation 'Dropdown in Spalte D ausgeben
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=DatPrü
        End With
        Randomize 'in Spalte C eine der ---Neunstelligen--- Datensatznummern ausgeben (zufällig)
        Cells(i + m, 3).Value = dict(Int(dict.Count * Rnd))
        Cells(i + m, 4) = ""
     End If
     
      a = 0   'zurücksetzen für nächsten Vergleich
      dict.RemoveAll
  Next i
End If
'**********************************************************************************
Set dict = Nothing
Cancel = True
End Sub

Doppelklick auf C1 
- Zufall in ganzer Spalte C
- Drop Menü in ganzer Spalte D wird erstellt
 
Doppelklick auf eine andere Zelle in Spalte C
- Zufall für diese Zelle 
- Drop Menü in der Zeile für Spalte D wird erstellt

Gruß Elex