Microsoft Excel

Herbers Excel/VBA-Archiv

Bitte um Hilfe bei Auswertung Adressdatei | Herbers Excel-Forum


Betrifft: Bitte um Hilfe bei Auswertung Adressdatei von: Tom Finnland
Geschrieben am: 05.08.2012 09:30:05

hallo liebe experten,

habe da ein problem bei dem ich leider nicht mehr weiterkomme und muß euch deshalb wieder mal um hilfe bitten: ich habe eine riesengroße adressdatei in der es von duplikaten nur so wimmelt (da 6 filialen ihre adressbestände einfach reinkopieren) und muß jetzt auswerten wieviel unterschiedliche adressen es sind - also summe oder anzahl ohne duplikate. das würd ich grad noch selber schaffen, ABER ich müßte zusätzlich noch wissen wieviele von diesen "unikaten" welches länderkennzeichen haben-und das schaff ich nicht.

zur erläuterung:

Adresse1 A
Adresse3 D
Adresse1 A
Adresse2 A
Adresse1 A
Adresse1 A
Adresse3 D
Adresse4 CH
Adresse5 D

soll ergeben 5 Adressen davon 2 mit A
2 mit D
1 mit CH
weiters ist es so das ich die länderkennzeichen auch nicht fix vorgeben und danach suchen kann, da ich ja nicht weiß welche vorkommen können.
würde mich freuen wenn einer von euch da eine idee hätte und bedanke mich schon im voraus für eure hilfe
lg thomas

  

Betrifft: AW: Bitte um Hilfe bei Auswertung Adressdatei von: Hajo_Zi
Geschrieben am: 05.08.2012 09:40:32

Hallo Thomas,

mit Hilsspalte.

Tabelle1

 ABC
1Adresse1 AA5
2Adresse3 DD3
3Adresse1 AA 
4Adresse2 AA 
5Adresse1 AA 
6Adresse1 AA 
7Adresse3 DD 
8Adresse4 CHCH1
9Adresse5 DD 

verwendete Formeln
Zelle Formel Bereich
B1 =TEIL(A1;FINDEN(" ";A1)+1;LÄNGE(A1)) 
C1 =WENN(ZÄHLENWENN($B$1:B1;B1)=1;ZÄHLENWENN(B1:B9;B1);"") 
B2 =TEIL(A2;FINDEN(" ";A2)+1;LÄNGE(A2)) 
C2 =WENN(ZÄHLENWENN($B$1:B2;B2)=1;ZÄHLENWENN(B2:B10;B2);"") 
B3 =TEIL(A3;FINDEN(" ";A3)+1;LÄNGE(A3)) 
C3 =WENN(ZÄHLENWENN($B$1:B3;B3)=1;ZÄHLENWENN(B3:B11;B3);"") 
B4 =TEIL(A4;FINDEN(" ";A4)+1;LÄNGE(A4)) 
C4 =WENN(ZÄHLENWENN($B$1:B4;B4)=1;ZÄHLENWENN(B4:B12;B4);"") 
B5 =TEIL(A5;FINDEN(" ";A5)+1;LÄNGE(A5)) 
C5 =WENN(ZÄHLENWENN($B$1:B5;B5)=1;ZÄHLENWENN(B5:B13;B5);"") 
B6 =TEIL(A6;FINDEN(" ";A6)+1;LÄNGE(A6)) 
C6 =WENN(ZÄHLENWENN($B$1:B6;B6)=1;ZÄHLENWENN(B6:B14;B6);"") 
B7 =TEIL(A7;FINDEN(" ";A7)+1;LÄNGE(A7)) 
C7 =WENN(ZÄHLENWENN($B$1:B7;B7)=1;ZÄHLENWENN(B7:B15;B7);"") 
B8 =TEIL(A8;FINDEN(" ";A8)+1;LÄNGE(A8)) 
C8 =WENN(ZÄHLENWENN($B$1:B8;B8)=1;ZÄHLENWENN(B8:B16;B8);"") 
B9 =TEIL(A9;FINDEN(" ";A9)+1;LÄNGE(A9)) 
C9 =WENN(ZÄHLENWENN($B$1:B9;B9)=1;ZÄHLENWENN(B9:B17;B9);"") 

Tabellendarstellung in Foren Version 5.48



GrußformelHomepage


  

Betrifft: AW: Bitte um Hilfe bei Auswertung Adressdatei von: Tom Finnland
Geschrieben am: 05.08.2012 11:45:35

hallo hajo,
also als erstes vielen lieben dank für deine antwort und für deine lösung!! leider ist es aber so das ich meine beispieltabelle wohl recht ungeschickt dargestellt hab: die adressen und das länderkennzeichen stehen nämlich in 2 unterschiedlichen spalten, sorry-hatte eh jede menge leerzeichen gemacht um das zu verdeutlichen aber die sind wohl irgendwo verlorengegangen-ich weiß das ich dir unnötige arbeit verursache aber könntest du die formel vielleicht anpassen bitte?? 2. problem: wie bekomm ich diese formel dann am besten in meine tabelle-in eine zeile reinschreiben und runterziehen geht ja nicht, oder?? manuell kann ich es nicht eintragen-das sind ca. 40000 zeilen??:-(( oder kann ich die hilfsspalte auch per vba mit deiner formel befüllen??
auf jeden fall danke und entschuldige bitte nochmals
lg thomas


  

Betrifft: AW: Bitte um Hilfe bei Auswertung Adressdatei von: Hajo_Zi
Geschrieben am: 05.08.2012 11:59:31

Hallo Thomas,

wenn das Länderkennzeichen schon in einer Extra Spalte stedht, brauchst Du die Formel in Spalte b Nicht.
Die Formel in Spalte C möchtest Du selber anpassen, oder habe ich Deine Datein übersehen?

Gruß Hajo


  

Betrifft: Ohne Dubletten mit Bedingung von: Erich G.
Geschrieben am: 05.08.2012 12:01:08

Hi Thomas,
so vielleicht:

 ABCD
2Adresse1AA2
3Adresse3DD2
4Adresse1ACH1
5Adresse2A  
6Adresse1A  
7Adresse1A  
8Adresse3D  
9Adresse4CH  
10Adresse5D  

Formeln der Tabelle
ZelleFormel
D2{=SUMME((VERGLEICH(A$2:A$13&B$2:B$13;A$2:A$13&B$2:B$13;0)=ZEILE($1:$12)*(B$2:B$13=C2))*1)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Bitte um Hilfe bei Auswertung Adressdatei von: Josef Ehrensberger
Geschrieben am: 05.08.2012 11:53:29


Hallo Tom,

Tabelle1

 ABCDEFGH
1Adresse1A   A2 
2Adresse3D   D2 
3Adresse1A   CH1 
4Adresse2A      
5Adresse1A      
6Adresse1A      
7Adresse3D      
8Adresse4CH      
9Adresse5D      
10        
11        

Formeln der Tabelle
ZelleFormel
G1=SUMMENPRODUKT((VERGLEICH($A$1:$A$40000&"x"&$B$1:$B$40000;$A$1:$A$40000&"x"&$B$1:$B$40000;0)=ZEILE($1:$40000))*($A$1:$A$40000<>"")*($B$1:$B$40000<>"")*($B$1:$B$40000=F1))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4






« Gruß Sepp »



  

Betrifft: AW: Bitte um Hilfe bei Auswertung Adressdatei von: Tom Finnland
Geschrieben am: 05.08.2012 20:11:33

Hallo an meine freundlichen Helfer,

musste leider unerwartet weg und hab desshalb Eure Lösungen erst jetzt gesehen, werds heute leider nicht mehr schaffen das zu Testen sondern muß mir das für Morgen nach der Arbeit aufheben - aber vielleicht eh nicht schlecht weil dann kann ich gleich an der Original-Datei probieren.
Ich bedanke mich bei Euch allen und wünsche für heute einen schönen Abend-Rückmeldung wie/was geklappt hat (bzw. was ich richtig umsetzen konnte) folgt Morgen!!
Vielen Dank nochmal und
lg Thomas


  

Betrifft: AW: Bitte um Hilfe bei Auswertung Adressdatei von: Tom Finnland
Geschrieben am: 07.08.2012 03:22:10

Hallo,

spät (nach einigen Überstunden) aber doch dir versprochene Rückmeldung zu Euren Hilfestellungen:

@ Hajo: Dankeschön, funktioniert, und ich denke ich werde das auch ohne fremde Hilfe so anpassen können das ich in der Original-Datei damit arbeiten kann!!

@ Erich: Das scheint nicht zu funktionieren-also sofern ich Deine Formel richtig übertragen habe bekomme ich hier nicht das richtige Ergebniss

@Sepp: leider das selbe - ich habe Deine Formel sowohl mit als auch ohne geschweifte Klammern ausprobiert, aber auch hier bekomme ich (sofern ich nichts falsch gemacht hab) nicht das gewünschte Ergebniss.

Ich Danke euch allen für Eure Mithilfe, zur Kontrolle lege ich noch eine Datei bei

https://www.herber.de/bbs/user/81301.xls

in der ich Eure Lösungen (hoffentlich richtig) eingearbeitet hab. Sofern ich einen Fehler gemacht hab würde ich mich um einen entsprechenden Hinweis freuen- damits mir beim nächsten mal nicht nochmal passiert.
Ich bedanke mich nochmal und wünsche einen schönen Tag
lg Thomas


  

Betrifft: Länderangaben fehlen von: Erich G.
Geschrieben am: 07.08.2012 08:47:48

Hi Thomas,
wenn du bei Sepps und meiner Formel die Länderkürzel in die richtigen (hier farbig markierten) Zellen schreibst,
bekommst du auch dort die richtigen Ergebnisse:

Sepp

 FG
1D2
2CH1
3A2
4 0

Formeln der Tabelle
ZelleFormel
G1{=SUMMENPRODUKT((VERGLEICH($A$1:$A$40000&"x"&$B$1:$B$40000;$A$1:$A$40000&"x"&$B$1:$B$40000;0)=ZEILE($1:$40000))*($A$1:$A$40000<>"")*($B$1:$B$40000<>"")*($B$1:$B$40000=F1))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Erich

 CD
2D2
3CH1
4A2
5 1

Formeln der Tabelle
ZelleFormel
D2{=SUMME((VERGLEICH(A$2:A$13&B$2:B$13;A$2:A$13&B$2:B$13;0)=ZEILE($1:$12)*(B$2:B$13=C2))*1)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Um die 1 hinter der leeren Zelle bei meiner Fiormel wegzubekommen, kannst du noch ein WENN in die Formel einbauen.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Länderangaben fehlen von: Tom Finnland
Geschrieben am: 08.08.2012 03:31:18

Hallo Erich,

Danke für Deine Nachricht, jetzt klärt sich ja doch noch alles auf: Hab mir ja gedacht das Eure Formeln funktionieren müssen, allerdings hat sich da ein kleines Mißverständniss eingeschlichen: Bei Deinem uns Sepp's Vorschlag müsste ich die Länderkennzeichen eingeben wie Du schreibst-und die weiß ich eben VORHER nicht. Ich habs in meinem Eingangspost erwähnt hätte es aber vielleicht noch mehr hervorheben müssen und hab Euch vielleicht mit meiner stark vereinfachten Beispieltabelle auch noch in die Irre geführt, sorry. In der Tat stehen in der Originaltabelle Adresssen aller Kunden mit denen wir in einem bestimmten Zeitraum Kontakt hatten (Anfrage, Verkauf etc.). Ich kann unmöglich vorher wissen aus welchen Ländern die kommen-heute bekomme ich nur Adressen aus D und nächste Woche ist Chile, Burkina Faso und der Vatikan dabei:-)) Noch dazu bekomme ich die Adressen so wie sie erfasst wurden, und das ist auch sehr unterschiedlich: D, DE, BRD, GER z.B und wie erwähnt eben auch noch mit sehr vielen Duplikaten. Also ich komm derzeit wenig zum Testen, denke/hoffe aber das Hajos Lösung funktioniert und somit möchte ich Euch nicht mehr weiter mit diesem Problem belasten-Danke euch allen nochmal und freue mich wenn Ihr mir bei einem meiner nächsten Probleme vielleicht wieder helfen würdet.
lg Thomas


  

Betrifft: Länderangaben ergänzt von: Erich G.
Geschrieben am: 08.08.2012 08:10:20

Hi Thomas,
das hatte ich wirklich überlesen!

Hier werden in Sp. C auch die Länderkürzel ermittelt (C3 nach unten kopieren):

 ABCD
2Adresse1AA1
3Adresse3DD2
4Adresse1AXXX1
5Adresse2XXXCH1
6Adresse1A  
7Adresse1A  
8Adresse3D  
9Adresse4CH  
10Adresse5D  

Formeln der Tabelle
ZelleFormel
C2=B2
D2{=WENN(C2="";"";SUMME((VERGLEICH(A$2:A$13&B$2:B$13;A$2:A$13&B$2:B$13;0)=ZEILE($1:$12)*(B$2:B$13=C2))*1))}
C3{=WENN(SUMME(ZÄHLENWENN(B$2:B$99;C$2:C2))>=SUMME((B$2:B$99<>"")*1); ""; INDEX(B:B;1+VERGLEICH(1;(ZÄHLENWENN(C$2:C2;B$2:B$99)=0)*(B$2:B$99<>""); 0)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Länderangaben ergänzt von: Tom Finnland
Geschrieben am: 09.08.2012 02:49:16

Hallo Erich.
Danke für deine "Nachbesserung"-jetzt funktionierts wirklich einwandfrei!!
Habs grad noch in der Beispieltabelle ein paar Testadressen hinzugefügt-findet wirklich alles und zeigt die richtige Anzahl an!!:-)) Hättest Du abschließend noch einem Tip für mich wie ich das in der Praxis am besten anwende: Das anpassen der Formel an die Originaltabelle bekomm ich schon hin bzw. werd das am WE versuchen (wenns nicht gelingt schrei ich eh hier um Hilfe) aber ich überleg grad wie ich die Formel am besten in die Tabelle bekomme: Natürlich kann ich die Formeln in die jeweils aktuelle Originaltabelle manuel reinschreiben und runterziehen, aber wenn sich das automatisieren liesse wärs natürlich ein schon fast dekatender Komfortgewinn. Oder soll ich eher eine "Zieltabelle" vorbereiten in der die Formel schon drinnsteht-mit StrgA+StrgC+StrgV wirds dann aber nicht mehr funktionieren-weil dann sind die Formeln ja auch wieder überschrieben?? Wär aber nur wenn Du zufällig eine Idee zur Hand hast, sonst werd ich am WE über Google was suchen.
Danke Dir nochmal und
LG Thomas


Beiträge aus den Excel-Beispielen zum Thema "Bitte um Hilfe bei Auswertung Adressdatei"