Auswertung von Inventurlisten

[StellarMonkey]

Hallo Zusammen,

ich muss die Daten einer Inventur auswerten. Es handelt sich hierbei um IT Gerätschaften.

Ich habe zwei Excellisten vorliegen. Die erste Liste ist von der Anlagenbuchhaltung. Hier sind also die Daten der Geräte aufgeführt, die von der ANBU bei Kauf (oder Umbuchung) eingetragen wurden.
In der zweiten Liste sind die Daten der Geräte, die tatsächlich bei der körperlichen Inventur aufgenommen wurden.

In beiden Listen haben ich unter anderem folgende Spalten:

- Bezeichnung (z.B. Dell Optiplex 7070)
- Standort
- SN (Seriennummer des Geräts)
- Inventar (Inventarnummer des Geräts)

In der Liste der Anlagenbuchhaltung habe ich zusätzlich die Spalte "AnlagenNr.".

Ich habe jetzt eine dritte Excel-Datei angelegt (Beispieldatei im Anhang). Hier möchte ich die relevanten Spalten aus beiden Listen kopieren und einfügen.
(Ich weiß, dass ich auch Datei-übergreifend Formeln benutzen kann, aber ich muss sowieso einiges berichtigen - z.B. wenn die Kollegen die Produktnummer und nicht die Seriennummer der Geräte eingescannt haben und jede Menge andere Fehler. Daher füge ich die Daten lieber separat in eine andere Datei.)

In der Excel-Datei habe ich drei Tabellen.
Eine Tabelle mit den Daten aus der ANBU (tbl_ANBU), eine mit den Daten der Inventur (tbl_Inventur) und eine Tabelle in der die Auswertung stattfinden soll (tbl_Auswertung).

Als Kriterium für den Vergleich sollen die Seriennummern herhalten. Es soll also geschaut werden, welche Seriennummern sowohl in tbl_ANBU als auch in tbl_Inventur vorkommen. Wenn es ein Match gibt, sollen die Daten entsprechend in tbl_Auswertung geschrieben werden. 
Da auch mal Geräte den Standort wechseln oder sich Inventarnummern ändern (ja, dafür gibt es leider Gründe), müssen also alle Spalten aus tbl_ANBU und tbl_Inventur in tbl_Auswertung geschrieben werden. Die Seriennummer natürlich nur einmal, da diese ja in beiden Daten-Tabellen gleich sein soll.
Seriennummern, die nur in einer Daten-Tabelle auftauchen, sollen aber ebenfalls in tbl_Auswertung geschrieben werden. Dann natürlich nur mit den Daten aus der Tabelle in der die SN gefunden wurde. Die restlichen Spalten in tbl_Auswertung sollen entweder leer bleiben oder es soll ein eindeutiger Text - z.B. "nicht gefunden" - angezeigt werden. 

Welche Formeln würdet ihr hierfür nutzen? Ich dachte an VERGLEICH verschachtelt mit SVERWEIS(en), aber ich hab keinen Plan, wie ich das richtig umsetzen kann, da ich ja als Suchkriterium nur entweder die Seriennummern aus tbl_ANBU oder aus tbl_Inventur nehmen kann und die jeweils andere Tabelle dann ja nicht komplett geprüft wird.
Hat jemand eine Idee? Tipps? Ich kann die beiden Daten-Tabellen auch gerne zu einer Tabelle machen - so hatte ich eigentlich auch angefangen, aber da bin ich leider auch nicht weiter gekommen.

Vielen Dank schonmal für eventuelle Hilfen.

PS: Es handelt sich hier natürlich nicht nur um so ein paar Zeilen wie in der Beispieldatei. In den beiden Listen habe ich ca. 30.000 unterschiedliche Datensätze.

[Ivan 16]

Hallo

 Mit PQ könnte wenn richtig verstanden so aussehen.

[StellarMonkey]

Hallo Ivan,

danke für die Antwort. Leider passt das nicht so ganz. Ich hatte jeweils eine SN, die in nur ein Zeichen Unterschied hatte. In ANBU CHBTQX und in Inventur CHBTQZ auch mit unterschiedlichen Inventarnummern. Die Seriennummern stehen in der Tabelle für die Auswertung beide in der selben Zeile, was nicht sein darf, da ich sonst die falsche Anlagennummer für das Gerät zuordne, was in der Inventurliste aber nicht in der ANBU Liste steht.
Dann hatte ich noch jeweils eine SN die gar nicht (nicht mal ähnlich) in der jeweils anderen Liste drin stand. Die aus der ANBU ist in deiner Auswertungsliste. Die aus der Inventur nicht.
Da in beiden Daten-Tabellen jeweils 18 Einträge sind, aber zwei Seriennummern pro Tabelle sind, die nicht in der anderen auftauchen, müsste es in der Auswertung eigentlich 20 Zeilen geben. 
16 Zeilen für die Seriennummern, die in beiden Daten-Tabellen gleich sind und dann noch jeweils zwei pro Tabelle für die unterschiedlichen.

Hast du irgendwelche Formeln benutzt? Ich kann keine in der Datei finden.

Grüße

[Ralf A]

Hi,

hab mir beide Dateien nicht angesehen, aber....

Du schreibst eingangs:

Als Kriterium für den Vergleich sollen die Seriennummern herhalten. Es soll also geschaut werden, welche Seriennummern sowohl in tbl_ANBU als auch in tbl_Inventur vorkommen. Wenn es ein Match gibt, sollen die Daten entsprechend in tbl_Auswertung geschrieben werden. 

In der Zieltabelle können somit niemals mehr Datensätze als in der Quelltabelle mit den wenigsten Übereinstimmungen sein, denn die Abweichungen können ja in der Schnittmenge nicht auftauchen . Deshalb ist entweder die Aussage auf Ivans Lösung, dass aus 18 Datensätzen 20 werden sollen oder die Aufgabenstellung falsch. Möglicherweise ist aber auch die Grundstruktur der Quelltabellen ungünstig, um Dich auf eine solche Annahme kommen zu lassen.

Formeln wirst Di in Ivans Lösung nicht finden können. Er schrieb ja, dass es eine PQ Lösung ist. Wie er das gemacht hat, kannst Du nachvollziehen, indem Du im Menü Daten auf Abragen und Verbindungen klickst und auf eine der angezeigten Abfragen doppelklickst. Dort im Menü Start dann auf Erweiterter Editor klicken. Das wird Dir ohne Hintergrundwissen allerdings auch nicht weiterhelfen. Es lohnt sich aber dennoch, sich mit PQ zu beschäftigen.

[StellarMonkey]

Hallo Ralf,

zwei Zeilen untendrunter schreibe ich aber:

Seriennummern, die nur in einer Daten-Tabelle auftauchen, sollen aber ebenfalls in tbl_Auswertung geschrieben werden. Dann natürlich nur mit den Daten aus der Tabelle in der die SN gefunden wurde. Die restlichen Spalten in tbl_Auswertung sollen entweder leer bleiben oder es soll ein eindeutiger Text - z.B. "nicht gefunden" - angezeigt werden.

Um es noch einmal genauer zu verdeutlichen:

Ich habe 16 Seriennummern, die in beiden Tabellen vorkommen.
Also schon einmal 16 Zeilen in der Auswertung.
Dann habe ich zwei Seriennummern in tbl_ANBU, die aber nicht in tbl_Inventur stehen. Das sind zwei weitere Zeilen, in denen aber nur die Spalten "AnlagenNr.", "Bezeichnung ANBU", "Standort ANBU", "SN", und "Inventar ANBU" ausgefüllt werden. Da sind wir dann bei 18 Zeilen.
Dann habe ich noch zwei Seriennummern, die in tbl_Inventur vorkommen, aber nicht in tbl_ANBU. Auch diese sollen in die Auswertung aber nur mit den Spalten "Bezeichnung Inventur", "Standort Inventur", "SN" und "Inventar Inventur". Da sind wir dann bei 20 Zeilen.

PQ war mir bisher kein Begriff. Ich habe in den Spalten P und Q geschaut, ob ich irgendwelche Formeln sehen.   Mein Fehler. 
Aber danke für die Aufklärung. Hab mir jetzt mal die Abfrageeinstellungen im PQ-Editor anzeigen lassen. Ich werd mich da mal genauer informieren und schauen, ob mir das mit meiner Aufgabenstellung hilft.

[Ralf A]

...ah ja... soweit hab ich dann nicht mehr gelesen...

Wäre ja mal eine schöne Übung für Dich. Auch wenn man das in einer Abfrage zuammenfassen könnte, der einfachere Weg ist aber, Du erstellst für die benötigten Zusatzinformationen jeweils eine eigene Abfrage, die Du am Ende alle (also inkl. Ivans) zusammenführst. Hilfreich dürfte u.v.a. dieser Link sein

[StellarMonkey]

Danke Ralf,

ich bin grade schon dabei die Abfragen zu erstellen. Habe auch schon im Prinzip das richtige Ergebnis in einer Merge Tabelle. Diese hat allerdings noch 18 Spalten. Neun Spalten aus dem inner join der beiden Daten-Tabelle (tbl_ANBU und tbl_Inventur), fünf aus dem outer join von tbl_ANBU und vier aus dem outer join von tbl_Inventur. Ich habe aber die 20 Zeilen, die ich brauche und es sind auch nur die Spalten der jeweiligen Zeile ausgefüllt, die ausgefüllt sein sollen. Jetzt bin ich gerade dabei mit bedingten Logikfunktionen durch PQ-Formeln die ganzen Daten in meine Auswertungstabelle mit nur acht Spalten zu bekommen. 
Ich poste dann natürlich meine fertige Beispiel-Datei, falls jemand eine Lösung für eine ähnliche Aufgabenstellung sucht.  

Sieht aber auf jeden Fall schon einmal vielversprechend aus. 

[Ralf A]

...die Spalten, die Du brauchst (oder die, die Du nicht brauchst, also die, die weniger sind) markieren, rechter Mausklick auf einen Spaltennamen davon, (Andere) Spalten entfernen aus dem Kontextmenü wählen...

Das kannst Du auch vor dem Joinen schon machen, also nur die gewünschten Spalten behalten. Beim expandieren der Ergebnistabelle kannst Du aber auch festlegen, ob Du alle oder nur bestimmte Spalten willst. Aber bei 18 Zeilen spielt das erstmal keine Rolle...

[Ivan 16]

Hallo

Eine andere Antwort, das das Ergebnis nicht passt war mir schon klar, aber schuld daran bist du selbst.
Wenn man als Wunschlösung nur leere Zellen lässt dann ist das halt so.
Wenn man dann noch liest das die Orginaldatei noch mehr Spalten hat, dann muss ich leider sagen ppp.
Was du dir an Wunschlösung weiter so vorgestellt hast, da bin ich leider nocht durch gestiegen.

[StellarMonkey]

@Ivan: Ich verstehe nicht so ganz, was du mit deiner letzten Antwort meinst. Aber es klingt so als würdest du meine erste Antwort von gestern als Angriff auf deine Antwort sehen. Falls das so ist: Ich habe es nicht als Angriff gemeint. Ich wusste noch gar nicht was PQ ist und konnte mit deiner Antwort nicht so viel anfangen. Aber letztendlich hat es mich ja jetzt doch zum Ziel gebracht. Also auch nochmal ein Danke an dich.

Ich habe jetzt mal meine (vorerst) finale Version der Beispieldatei angehängt.
Da kann man sicherlich noch ein bisschen was optimieren und in den Abfragetabellen Spalten entfernen, aber es erfüllt seinen Zweck.

Es werden erst einmal die beiden Daten-Tabellen tbl_ANBU und tbl_Inventur abgefragt und dann zusammen in Merge1 übergeben. So habe ich dann alle Seriennummern die in einer der beiden Daten-Tabellen vorkommen - also auch entsprechend so viele Zeilen, wie es unterschiedliche Seriennummern gibt. 

In tbl_Auswertung frage ich dann per WENN-Funktion die unterschiedlichen Spalten für Seriennummern aus Merge1 ab. Bei den restlichen Spalten in tbl_Auswertung benutze ich dann die INDEX-Funktion um die Seriennummer aus Merge1 wieder in tbl_ANBU und / oder tbl_Inventur zu suchen und die entsprechenden Werte einzutragen. Die INDEX-Funktion habe ich mit WENNFEHLER verschachtelt um den Text "nicht gefunden" auszugeben, wenn eine Zelle nicht ausgefüllt werden kann. 

Und mit den Regeln der bedingten Formatieren markiere ich mir noch die Zellen, die in den korrespondierenden Zellen unterschiedliche Werte aufweisen (also z.B. die beiden in der gleichen Zeile befindlichen Zellen in Standort ANBU und Standort Inventur).

Die Werte in den beiden Daten-Tabellen lassen sich auch ändern und nach Abrufen der aktualisierten Daten werden diese auch in die PQ Abfrage-Tabellen geschrieben. Funktioniert also soweit alles.

Beim Schreiben bemerke ich gerade, dass ich eigentlich nur die unterschiedlichen "SN" Spalten in Merge1 gebraucht hätte... Aber ich lasse es erstmal so - vielleicht brauche ich ja später doch noch andere Spalten.
Wahrscheinlich hätte ich das Ganze auch viel einfacher haben können auch ohne PQ, aber so hab ich wenigstens was neues dazu gelernt.

Grüße und nochmal Danke für die Hilfe.