Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Zellvergleich

Frage Microsoft Microsoft Office

Mitglied: viewpoint

viewpoint (Level 1) - Jetzt verbinden

18.03.2009, aktualisiert 12:17 Uhr, 5017 Aufrufe, 19 Kommentare

Zellvergleich mit mehreren

Hallo!

Ich habe ein Excel-Sheet mit 35650 Zeilen und 26 Spalten.
In Spalte A steht ein Mitarbeiter-Name, in Spalte S steht der Abteilungsname (ein normales Textfeld), in Spalte V ist eine Kostenstelle hinterlegt (die anderen Spalten sind unrelevant)

Ich will nun wissen, wie oft innerhalb einer Abteilung eine abweichende Kostenstelle (und welche) eingetragen ist

Ein Beispiel: In Spalte S kommt 10x der Abteilungsname "Programmierung" vor. und innerhalb der Abteilung gibt es 5 verschiedene Kostenstellen -> mit Auflistung:
Programmierung
Kst 001
Kst 002
Kst 003
Kst 004
Kst 005


Wie kann ich das am besten umsetzen?

Danke für Tipps!

Lg. aus Wien
Mitglied: SarekHL
18.03.2009 um 12:26 Uhr
Ich denke, am einfachsten löst man dieses Problem mit einer Pivot-Tabelle.

Hier findest Du eine gute Video-Anleitung zum Thema Pivot-Tabellen.
Bitte warten ..
Mitglied: viewpoint
18.03.2009 um 13:17 Uhr
Das dachte ich auch, jedoch ist es nicht so einfach, 10.000 Werte in einer Pivot Tabelle anzeigen zu lassen, davon abgesehen würde ich diese Ergebnisse gerne weiterverwenden - eben wie in oben angeführter Tabelle.
Bitte warten ..
Mitglied: 76109
19.03.2009 um 15:38 Uhr
Hallo,

wieviele Abteilungen und Kostenstellen gibt es.?

Wo sollen die Ergebnisse hin?


Gruß Dieter
Bitte warten ..
Mitglied: viewpoint
20.03.2009 um 13:40 Uhr
unternehmensweit schätze ich 8000 abteilungen - etwa 5000 kostenstellen - geschätzt!
Am besten wäre die "Tabellisierung" in einem neuen Blatt - zwecks der Übersichtlichkeit
Bitte warten ..
Mitglied: 76109
20.03.2009 um 15:21 Uhr
Hallo viewpoint,

ein Haus voller Spezialisten und keiner kann ein Excel-Macro schreiben?

Sieht der Tabellenaufbau genauso aus, wie im 1. Beitrag, oder stehen in jeder Zeile Abteilung und Kostenstelle und sind zwischen den Abteilungen Leerzeilen und und und?

Bitte Beispiel mit genauem Tabellenaufbau, am besten mit 2 Abteilungen und Aussehen der Ergebnistabelle an Hand des Beispiels.

Und existiert eventuell für die verschiedenen Abteilung eine Spalte mit eindeutiger Abteilungs-Nummer?


Gruß Dieter


PS. Die Tabellennamen wären auch wichtig.
Bitte warten ..
Mitglied: viewpoint
21.03.2009 um 14:06 Uhr
Hallo!

Die Daten kommen aus dem SAP. Sobald ich am Montag wieder im Büro bin, werde ich 5-6 Zeilen hier rein kopieren.
Grundsätzlich sind zwischen den Abteilungen keine Leerzeichen oder Leerzeilen, es ist sortiert nach einer eindeutigen Nummer die der Mitarbeiter hat - jeder Mitarbeiter füllt eine Zeile.
Es kann sein, dass ein Mitarbeiter in der 10 Zeile steht, und sein Kollege der selben Abteilung mit der selben Kostenstelle 1000 Zeilen drunter.
Ich will quasi filtern welche Abteilungen es gibt, und welche Kostenstellen diese Abteilung führt (pro Abteilung können es mehrere Kostenstellen sein).
So wie es in meinem ersten Beitrag ist, so sollte es in etwa aussehen. Ich denke so ist es übersichtlich und damit kann man etwas anfangen.

Lg., bis Montag ;)
Bitte warten ..
Mitglied: 76109
21.03.2009 um 14:36 Uhr
Hallo viewpoint,

danke für die Vorab-Info.

Die Mitarbeiter-Nummer ist für diesen Zweck ungeeignet.

Noch ein schönes WE

Gruß Dieter
Bitte warten ..
Mitglied: viewpoint
23.03.2009 um 09:36 Uhr
Moin!

Sheet sieht folgender Maßen aus:

MAIL GIVENNAME SURNAME ACCOUNTNAME COMPANY ORGSHORT COSTCENTER NUMBER
pepi.maier@mail.com Pepi Maier MAIPE Gesellschaft 1 Prog 123456 100987
sepp.Huber@mail.com Sepp Huber HUBSE Gesellschaft 1 Prog 234567 100876
karl.gates@mail.com Karl Gates GATKA Gesellschaft 1 Prog 123456 100765
fred.seidl@mail.com Fred Seidl SEIFR Gesellschaft 1 Buch 345678 100654

Die erste Zeile ist die Überschrift. Daten sind natürlich Dummy-Daten (aus Datenschutzgründen...). Vorher und nachher sind noch einige Spalten wie "Anrede", "Gebäude", "Stockwerk", etc.
Ergebnis sollte nun so aussehen:
Gesellschaft 1
Prog
123456
234567
Buch
345678

Das einzige das wirklich einzigartig ist und den Mitarbeiter erkennbar macht, ist das Feld "NUMBER". Oder eben eine Kombination aus mehreren Attributen/Angaben.
Bitte warten ..
Mitglied: 76109
23.03.2009 um 16:45 Uhr
Hallo viewpoint,

die Abteilung reicht als Orientierung aus. Mitarbeiter können wechseln. Dank der Finanzkrise ist ja wohl nix mehr sicher.

Habe ich das richtig verstanden, dass auch die Anzahl eingetragen werden soll. Also wie oft eine Kostenstelle verwendet wurde.

Und kann ich davon ausgehen, dass in Spalte S1 und V1 nichts drin steht?

Kann es auch so aussehen incl. Anzahl:

Gesellschaft 1
Prog 123456 5 (mal)
789012 3 (mal)
Buch 471108 7 (mal)
081547 12 (mal)

Aktuell habe ich es so:

Gesellschaft 1
Prog Kst1 Kst2 Kst3 Kst4 Kst5
Prog 5 3 7 1 9

Die obere Tabelle ist wesentlich komplizierter. D.h. ich müsste die untere nochmal in die obere umstricken.


Gruß Dieter

PS. Die Tabellblattnamen brauch ich auch noch?
Bitte warten ..
Mitglied: viewpoint
24.03.2009 um 10:34 Uhr
Hallo!

eigentlich reicht Vorschlag 2 völlig aus.
das Sheet heißt "extUser". Ab Spalte AA steht nichts mehr drin - also überall inklusive Z.

Sieht ja ziemlich heftig aus - sollte man verkaufen ;)

Lg. Andreas
Bitte warten ..
Mitglied: 76109
24.03.2009 um 17:52 Uhr
Hallo Andreas,

wie was, ich soll euch den Code verkaufen?

Die Frage bezüglich Spalte S1 und V1 war ein Missverständnis meinerseits.

Ein paar Fragen hab ich noch:

1. Wie heißt das Tabellenblatt mit den Quell-Daten?

2. Soll in Zeile 1 eine Überschrift und wenn ja, welche (Gesellschaft1...)?
  • Zeile 1 wird bei der Aktualisierung einfachheitshalber gelöscht.

3. Sollen mehr als derzeit 5 Kostenstellen pro Abteilungen vorgesehen werden?

4. Wie sollen die Werte aktualisiert werden ( dauert 1-2 Sekunden)?
  • Automatisch bei Veränderung der Quell-Daten oder manuell über Taste STRG + ?

5. Bestehen Kentnisse über VB-Editor und/oder ist dieser installiert?


Gruß Dieter
Bitte warten ..
Mitglied: viewpoint
25.03.2009 um 08:08 Uhr
Hallo!

- Das Tabellenblatt heißt "extUser"
- Das Unternehmen besteht aus 5 Unternehmen. Es ist möglich, dass in dieser Liste 2 Unternehmen die gleiche Kostenstelle haben, aber nicht zusammen gehören
- Ich glaube dass es Abteilungen gibt, die bis zu 30 Kostenstellen haben - das will ich eben rausfinden
- Aktualisierung reicht eigentlich mit Klick auf einen Button - also per "Makro ausführen" bzw. Tastenkombi
- Kenntnisse über VB-Editor im Office hab ich. Allerdings kann ich so komplexe Schritte nicht programmieren - aber bestehenden Code lesen und verstehen.

Lg. Andreas
Bitte warten ..
Mitglied: 76109
25.03.2009 um 11:11 Uhr
Hallo Andreas,

Also nochmal zum mitschreiben:

Es existiert eine Tabelle mit dem Namen extUser, dass ist soweit klar.
Aber ich habe zwei Tabellen einmal die, wo die ursprünglichen Daten
(SheetName?) stehen und einmal die, wo die Ergebnisse (SheetName?)
der Zählungen stehen?

Den Code ändere ich in soweit um, dass pro Abteilung maximal 255
Kostenstellen möglich sind.

Noch ein Tip: Ab und an ist es hilfreich Makros aufzuzeichnen und sich
den Code im VB-Editor anzusehen. Dabei wird zwar viel unnötiges an
Code geschrieben. Aber um zu sehen wie was funktioniert ist es doch
hilfreich.

Gruß Dieter

PS. Was ist mit der Überschrift in Zeile 1 (Tabelle Ergebnisse)?
Bitte warten ..
Mitglied: viewpoint
25.03.2009 um 15:36 Uhr
Hallo!

Die Datei heißt extUser.xls
Das Tabellenblatt ebenfalls extUser
Dort wo die Ergebnisse hineinkommen sollen, würde ich "Berechnung" nennen

Eine echte Überschrift brauche ich eigentlich nicht, es würde reichen wenn ganz oben das Unternehmen steht

Meine VB Erfahrung habe ich eh aus Makros die ich mir ansehe und zum Größten Teil hier aus diesem Forum ;)

Lg.
Bitte warten ..
Mitglied: 76109
25.03.2009 um 16:28 Uhr
Hallo Andreas,

das sehe ich jetzt erst . Du hast mich ganz schön reingelegt.

Der Hinweis, das mehrere Gesellschaften in einem Tabellenblatt vertreten sind,
hast Du bisher vergessen zu erwähnen. Jetzt kann ich grad nochmal von vorne
anfangen.

Zitat von viewpoint:
Hallo!

- Das Tabellenblatt heißt "extUser"
- Das Unternehmen besteht aus 5 Unternehmen. Es ist möglich,
dass in dieser Liste 2 Unternehmen die gleiche Kostenstelle haben,
aber nicht zusammen gehören

Stimmt das weningstens so?

Zitat von viewpoint:
Moin!

Sheet sieht folgender Maßen aus:

MAIL GIVENNAME SURNAME ACCOUNTNAME COMPANY ORGSHORT COSTCENTER NUMBER
@mail.com Pepi Maier MAIPE Gesellschaft 1 Prog 123456 100987
@mail.com Sepp Huber HUBSE Gesellschaft 1 Prog 234567 100876
@mail.com Karl Gates GATKA Gesellschaft 1 Prog 123456 100765
@mail.com Fred Seidl SEIFR Gesellschaft 1 Buch 345678 100654



Gruß Dieter
Bitte warten ..
Mitglied: 76109
25.03.2009 um 20:13 Uhr
Hallo Andreas,

bei dem VBA-Code wird davon ausgegangen, das die extSheet-Tabelle genauso aussieht,
wie in der vorangegangenen Antwort "didi1954 schreibt am 25.03.2009 um 16:28:38 Uhr"

Es wird angenommen, dass sich folgende Einträge in folgenden Spalten befinden:

1. Spalte R = Gesellschaft
2. Spalte S = Abteilung
3. Spalte V = Kostenstelle

Ansonsten den Kopierbefehl im Code anpassen.


Es wird angenommen, dass folgende Tabellenblätter existieren:

1. Quell-Daten = "extUser"
2. Ziel-Daten = "Berechnung"

Ansonsten die Tabellennamen in den Set-Anweisungen ändern


Das Makro ist minimal getestet. Einen richtiger Test muss mit der Original-Tabelle
durchgeführt werden. Dazu schlage ich vor:

1. Das Makro in Modul 1 in einer Kopie der Excell-Datei zu kopieren
2. Das Makro mit einer Tastenkombination zu verknüpfen oder über Debugger starten
3. Die Quell-Daten in Sheet "extUser" ans Ende kopieren und wie folgt zu sortieren:
  • Zellen löschen: Zeile 1 und alle Spalten ausser R,S,V
  • Alle Zellen markieren und Dialog sortieren öffnen:
  • Eingabe 1 sortieren nach A
  • Eingabe 2 sortieren nach B
  • Eingabe 3 sortieren nach C

Nun müsste nach Gesellschaft mit den einzelnen Abteilungen und ihren Kostenstellen
sortiert sein. Jetzt kannst Du von Hand auszählen und die Werte Vergleichen.


01.
Option Explicit 
02.
Option Compare Text 
03.
 
04.
 
05.
Sub CreateCostTable() 
06.
    Dim Scr As Worksheet, Des As Worksheet, Kst, Anz, Org As String, Cpy As String 
07.
    Dim Clr(0 To 254), EndLine As Long, Line As Long, r As Long, i As Integer, n As Integer 
08.
     
09.
    On Error GoTo Ende 
10.
 
11.
    Set Scr = Sheets("extUser"):  Set Des = Sheets("Berechnung") 
12.
     
13.
    With Des 
14.
       .Cells.Clear 
15.
        Application.ScreenUpdating = False 
16.
         
17.
        Scr.Range("R:S,V:V").Copy Destination:=.Range("A1"): .Range("A1:C1").Clear 
18.
       
19.
       .Columns("A:C").Sort Key1:=.Range("A1"), Key2:=.Range("B1"), _ 
20.
                            Key3:=.Range("C1"), OrderCustom:=1 
21.
     
22.
        EndLine = .Cells(.Rows.Count, 2).End(xlUp).Row 
23.
         
24.
        For r = 1 To EndLine 
25.
            If Not .Cells(r, 1) Like Cpy Then Cpy = .Cells(r, 1):  Org = "" 
26.
            If Not .Cells(r, 2) Like Org Then 
27.
                Org = .Cells(r, 2):  Line = r:  i = 0:  n = 0 
28.
                Kst = Clr:  Anz = Clr:  Kst(0) = .Cells(r, 3):  Anz(0) = 1 
29.
            Else 
30.
                If Not .Cells(r, 3) Like Kst(i) Then 
31.
                    i = i + 1:  Kst(i) = .Cells(r, 3):  n = n + 1:  Anz(n) = 1 
32.
                Else 
33.
                    Anz(n) = Anz(n) + 1 
34.
                End If 
35.
            End If 
36.
            If Not .Cells(r + 1, 2) Like Org Then 
37.
                .Range(.Cells(Line, 3), .Cells(Line, 256)) = Kst 
38.
                .Range(.Cells(Line, 3), .Cells(Line, 256)).NumberFormat = "@" 
39.
                .Range(.Cells(Line + 1, 3), .Cells(Line + 1, 256)) = Anz 
40.
                .Range(.Cells(Line + 2, 1), .Cells(r, 3)).Clear 
41.
            End If 
42.
        Next 
43.
       .Cells.Sort Key1:=.Range("A1") 
44.
    End With 
45.
Ende: 
46.
    If Err Then MsgBox "Die Kostenstellenaufzählung ist fehlgeschlagen.", vbExclamation, "Fehler" 
47.
    Application.ScreenUpdating = True 
48.
 
49.
End Sub

Ergebnis:

Company 1 Prog Kst1 Kst2 Kst3 --- Kst254
Company 1 Prog 5 3 7 --- 9
Company 1 Buch Kst1 Kst2 Kst3 --- Kst254
Company 1 Buch 6 9 1 --- 3
Company 2 Prog Kst1 Kst2 Kst3 --- Kst254
Company 2 Prog 3 1 2 --- 5
Company 2 Buch Kst1 Kst2 Kst3 --- Kst254
Company 2 Buch 7 4 9 --- 3


Ich hoffe Du bist nicht zu sehr darüber schockiert, dass der Code - entgegen Deiner
Erwartungen - nicht so heftig ausgefallen ist, als angenommen.


Gruß Dieter
Bitte warten ..
Mitglied: 76109
26.03.2009 um 16:04 Uhr
Hallo ,

habe am Code-Ende einen Fehler korrigiert: Muss natürlich heissen "Application.ScreenUpdating = True"

Gruß Dieter
Bitte warten ..
Mitglied: viewpoint
26.03.2009 um 21:46 Uhr
Funktioniert perfekt :D ist genau das wonach ich suchte!

Vielen Dank!
Bitte warten ..
Mitglied: 76109
26.03.2009 um 23:15 Uhr
Hallo Andreas,

Toll, wenn es funktioniert.

Ich denke das mit dem ScreenUpdating hast Du geändert?

Ganz am Anfang vom Code habe ich noch die "Option Compare Text" geschrieben.

Das bedeutet, dass beim Textvergleich mit dem Like-Operator nicht zwischen Groß/Kleinschreibung unterschieden wird.

Um gegebenenfalls sicher zugehen, dass sich keine Leerzeichen in den Textfeldern eingeschlichen haben, könnte man
noch an einigen Stellen eine Trim-Anweisung einfügen?


Gruß Dieter
Bitte warten ..
Neuester Wissensbeitrag
Humor (lol)

Linkliste für Adventskalender

(3)

Information von nikoatit zum Thema Humor (lol) ...

Heiß diskutierte Inhalte
Exchange Server
gelöst Exchange 2010 Berechtigungen wiederherstellen (20)

Frage von semperf1delis zum Thema Exchange Server ...

Windows Server
DHCP Server switchen (20)

Frage von M.Marz zum Thema Windows Server ...

Hardware
gelöst Negative Erfahrungen LAN-Karten (19)

Frage von MegaGiga zum Thema Hardware ...

Exchange Server
DNS Einstellung - zwei feste IPs für Mailserver (15)

Frage von ivan0s zum Thema Exchange Server ...