Excelliste mit Vorwahlen Vergleichen

Question

Huhu, ich hab da eine für mich ziemlich wichtige frage:

ich hab ein erstes tabellenblatt, in dem viele vorwahlen stehn. die vorwahlen haben unterschiedlich viele stellen. und den vorwahlen wiederum werden gebiete zugeordnet. die vorwahlen stehen ohne führende 0 (also z.b. statt 030 -> 30)

im zweiten tabellenblatt muss ich die telefonrechnungen von mienem chef analysieren. dazu gebe ich die gewählten rufnummern mit vorwahlen in einer!! spalte ein und möchte, dass aus dem ersten tabellenblatt die vorwahl erkannt wird und das dazugehörige gebiet im zweiten tabellenblatt angezeigt wird.

Tabellenblatt 1:

A3:A50 = Vorwahlen ohne führende 0
B3:B50 = Gebiete

Tabellenblatt 2:
B3:B50 = hier wird die komplette nummer ohne führende 0 und leerzeichen hintereinander weg eingegeben

D3:D50 = hier soll das gebiet angezeigt werden, also muss dort die formel rein


ich habe absolut keine ahnung wie ich das bewerkstelligen soll, eine beschreibung der formel würde mir ungemein helfen, damit ich weiß was wozu dient in der formel...

vielen dank schon im vorraus

Answer 1

Hallo,

da brauchst Du wahrscheinlich eine VBA-Lösung.

Habe mal eine Formel gebastelt, die für 2 bis 5-stellige Vorwahlen funktioniert, wenn... nicht zufällig z.B. eine 2-stellige Vorwahl mit den ersten 3 Stellen der Telefonnummer, auch eine vorhandene 5-stellige Vorwahl ergibt.

=INDEX(Tabelle1!B$3:B$50;WENN(NICHT(ISTNV(VERGLEICH(LINKS(B3;5)*1;Tabelle1!A$3:A$50;0)));VERGLEICH(LINKS(B3;5)*1;Tabelle1!A$3:A$50;0);WENN(NICHT(ISTNV(VERGLEICH(LINKS(B3;4)*1;Tabelle1!A$3:A$50;0)));VERGLEICH(LINKS(B3;4)*1;Tabelle1!A$3:A$50;0);WENN(NICHT(ISTNV(VERGLEICH(LINKS(B3;3)*1;Tabelle1!A$3:A$50;0)));VERGLEICH(LINKS(B3;3)*1;Tabelle1!A$3:A$50;0);WENN(NICHT(ISTNV(VERGLEICH(LINKS(B3;2)*1;Tabelle1!A$3:A$50;0)));VERGLEICH(LINKS(B3;2)*1;Tabelle1!A$3:A$50;0);"")))))

Teste mal selbst.

Eine voll funktionierende Formel kann man nur erstellen wenn in Tabelle2 ein Trennzeichen zwischen Vorwahl und Nummer eingefügt wird.

Gruß
Rainer

Answer 2

hi all

sind denn makros erlaubt ?

gruss nighty

Answer 3

hi all

falls makros erlaubt sind,was waere dann erwuenscht,eine function oder ein makro

function waere wie eine formel zu handhaben

makro waere aufzurufen und wuerde die tel liste in einem rutsch befuellen

gruss nighty

Answer 4

jhuhu, gewünscht wäre eine funktion... Die vorwahlen haben immer verschieden viele stellen, dass muss berücksichtigt werden. ich weiß in dem moment wo ich die telefonnummern eingebe leider nicht was ist vorwahl und was nicht.

lg

Answer 5

hi all ^^

wie gewuenscht

gruss nighty

nach deinen angaben waere das ein beispiel

1 bereichsangabe vorwahlen von tab1
2 bereichsangabe orte tab1
3 bereichsangabe nummer tab2

Function OrtAusgabe(SuchBereich As Range, AusgabeBereich As Range, QuellenAngabe As Range) As String
Application.Volatile
Dim zelle As Range
For Each zelle In SuchBereich
If zelle = Mid(QuellenAngabe, 1, Len(zelle)) Then
OrtAusgabe = Cells(zelle.Row - 1, AusgabeBereich.Column)
Exit For
End If
Next zelle
End Function

Answer 6

hi all

ich wusste es doch ^^

hier korrigiert und immer noch fehlerhaft

da noch eine sheet angabe vorhanden ist

bin noch am ueberlen bzw wer ideen hat ohne zusaetzliche parameter,immer raus damit :-))

Function OrtAusgabe(SuchBereich As Range, AusgabeBereich As Range, QuellenAngabe As Range) As String
Application.Volatile
Dim zelle As Range
For Each zelle In SuchBereich
If zelle = Val(Mid(QuellenAngabe, 1, Len(zelle))) Then
OrtAusgabe = Sheets(1).Cells(zelle.Row, AusgabeBereich.Column)
Exit For
End If
Next zelle
End Function

Answer 7

Hallo nighty,

die Funktion rechnet genau so falsch wie meine Formel, wenn das zutrifft, wie ich oben beschrieben habe.

Gruß
Rainer

Answer 8

hallo rainer ^^

meinst du diese problem ?

30123
3012345

was haelst davon wenn wir dann immer die zahl nehmen die von dem stellenwert am hoechsten ist ?

gruss nighty

Answer 9

Hallo nighty,

was haelst davon wenn wir dann immer die zahl nehmen die von dem stellenwert am hoechsten ist ?

...das wir nicht viel nützen.

Beispiel:

Tabelle 1

VW_____Ort
37______Musterstadt
37297___Musterdorf

Tabelle2

gemeinsame. Nr
3729723456 (37297=VW; 23456=Tel-Nr.)
3729754 (37=VW; 29754=Tel-Nr.)

In beiden Fällen ist das Ergebnis "Musterdorf" obwohl die zweite Suche "Musterstadt" ergeben müsste.

Ich wage fast zu behaupten, ohne Trennzeichen ist auch mit VBA nichts zu machen.
Kannst ja mal weiter tüffteln, vielleicht habe ich nicht recht.

Gruß
Rainer

Answer 10

hi rainer ^^

ichglaub auch das es ohne neue bezugspunkte keine loesung gibt dafuer :-(

gruss nighty

meine letzte variante

Function OrtAusgabe(SuchBereich As Range, AusgabeBereich As Range, QuellenAngabe As Range) As String
Application.Volatile
Dim zelle As Range
For Each zelle In SuchBereich
If zelle = Val(Mid(QuellenAngabe, 1, Len(zelle))) Or Len(Sheets(1).Cells(zelle.Row, AusgabeBereich.Column)) > Len(OrtAusgabe) Then
OrtAusgabe = SuchBereich.Parent.Cells(zelle.Row, AusgabeBereich.Column)
End If
Next zelle
End Function

Rem =OrtAusgabe(Tabelle1!A2:A4;Tabelle1!B2:B4;Tabelle2!A2)