Usuń zduplikowane wpisy, zachowując tylko najnowsze

Mam kilka arkuszy kalkulacyjnych z informacjami, które muszę skonsolidować, każdy z inną (a czasem nakładającą się) częścią końcowego zestawu danych, którą mam nadzieję stworzyć. Zazwyczaj łączenie arkuszy kalkulacyjnych z podobnymi danymi jest łatwe do zrobienia poprzez zrzucenie wszystkich danych do jednego arkusza i użycie Usuń duplikaty. Chcę jednak upewnić się, że pozostawione elementy zamówienia reprezentują najnowszą wersję duplikatów.

Będę importować następujące dane z każdego arkusza kalkulacyjnego:

• Unikalne pole identyfikatora (UID), które identyfikuje przedmiot elementu zamówienia i będzie używane do wykrywania duplikatów między arkuszami wejściowymi.
• Pole statusu (STATUS), które będzie zawierało informacje o elemencie wywoływanym w polu unikalnego identyfikatora.
• Pole daty (DATA), które będzie zawierało datę pierwotnego zapisania importowanych danych.

Potrzebuję, aby mój wynik zawierał tylko jeden element zamówienia dla każdego identyfikatora UID i zawierał dane z pola STATUS, które odpowiada najnowszej DATY dla tego identyfikatora UID z arkuszy wejściowych.

Jak najłatwiej to zrobić w programie Excel?

Nie wiem, czy jest to gwarantowane, ale wydaje mi się, że to działa (w bardzo małych testach w programie Excel 2007): weź połączony arkusz danych i posortuj go w odwrotnej kolejności według DATE, więc najnowsze wiersze są powyżej starszych. Następnie usuń duplikaty .

Ta witryna potwierdza to zachowanie: „Gdy program Excel skanuje tabelę, usuwa każdy kolejny rekord, który ma ten sam identyfikator produktu co poprzedni rekord, nawet jeśli reszta danych jest inna”.

Oto kilkustopniowe rozwiązanie, zakładając, że możesz wykonać niektóre z nich ręcznie i nie potrzebujesz jednego całkowicie zautomatyzowanego rozwiązania: (a jeśli tak, to jestem pewien, że możesz wziąć to stąd ...)

1. Excel nie jest bazą danych.
2. Zrzuć wszystkie dane w jednym arkuszu. (Dla przykładu zakładam, że masz UID w kolumnie A, DATE w kolumnie B i STATUS w C).
3. W drugim arkuszu wykonaj polecenie Usuń duplikaty tylko w kolumnie UID . (np. skopiuj tylko filtrowane unikaty lub skopiuj całą kolumnę, a następnie wykonaj standardowe usuwanie duplikatów).

4. W kolumnie DATA dodaj następującą formułę Array *:

   {= MAX (JEŻELI (Arkusz danych! A: A = A1, Arkusz danych! B: B))}

   Zasadniczo wybiera najnowszą datę dla każdego identyfikatora UID. (Dotyczy to oczywiście pierwszego rzędu, pamiętaj o wypełnieniu wszystkich pozostałych wierszy A1, A2, ...)

5. W kolumnie STATUS dodaj następującą formułę macierzy:

   {= INDEKS (JEŻELI (DataSheet! A: A = A1, IF (DataSheet! B: B = B1, DataSheet! C: C)), MATCH (TRUE, IF (DataSheet! A: A = A1, IF (DataSheet! B: B = B1, PRAWDA)), 0))}

(Ponownie zwróć uwagę na pierwszy wiersz, wypełnij resztę).

Ten jest bardziej złożony, podzielmy go:

JEŻELI (Arkusz danych! A: A = A1, JEŻELI (Arkusz danych! B: B = B1, Arkusz danych! C: C))

Ta formuła tablicowa po prostu wykonuje odpowiednik klauzuli SQL WHERE z dwoma warunkami: dla wszystkich wierszy, które pasują zarówno do UID (kolumna A), jak i DATE (kolumna B), zwróć wartość wiersza w kolumnie C (STATUS).

MECZ (PRAWDA, JEŻELI (Arkusz danych! A: A = A1, JEŻELI (Arkusz danych! B: B = B1, PRAWDA)), 0)

Pierwsza formuła powinna być wystarczająco dobra, ale ponieważ nie mamy sposobu na wyciągnięcie tylko wartości innej niż NULL (lub innej niż FAŁSZ), a Excel nie ma formuły WSPÓŁPRACA, musimy skorzystać z odrobiny pośrednictwo.
Formuła MATCH przeszukuje tablicę zwróconą przez JEŻELI (te same warunki, co powyżej, ale po prostu zwraca PRAWDA, jeśli jest zgodna), w celu znalezienia pierwszej wartości PRAWDA. 3 parametr, 0, wymaga dokładnego dopasowania.
Ta formuła po prostu zwraca indeks pierwszego i jedynego wiersza, który jest zgodny z poprzednimi warunkami (dopasowanie UID i DATE (która była maksymalną datą, która pasuje do UID)).

{= INDEKS (JEŻELI ( patrz wyżej ), MATCH ( patrz powyżej ))}

Teraz jest wystarczająco proste, aby pobrać indeks pasującego wiersza z MATCH i wyciągnąć odpowiednią wartość STATUS z tablicy IF. Zwraca to pojedynczą wartość, nowy STATUS, który ma gwarancję (jeśli wykonałeś wszystkie te kroki poprawnie) od najnowszej daty dla każdego identyfikatora UID.

6 Excel nie jest bazą danych.

* FOOTNOTE: jeśli nie znasz formuł tablicowych (choć myślę, że tak jest), spójrz na to : w zasadzie podajesz oryginalną formułę, która powinna dawać tablicę wartości (bez zawijasów {}), a następnie naciśnij CTRL+ SHIFT+ ENTER. Excel dodaje dla ciebie squiggly {} i oblicza wszystkie wartości jako tablicę.

* FOOTNOTE # 2: Poważnie, EXCEL NIE JEST BAZY DANYCH. ;-)

@AviD jest poprawne, ponieważ Excel nie jest bazą danych, ale możesz zaimportować dane do innego arkusza kalkulacyjnego za pośrednictwem źródła danych Microsoft Query. Jest to trochę brzydkie, ale da ci dostęp do instrukcji SQL, która powinna umożliwić ci to, czego chcesz.

1. W nowym arkuszu kalkulacyjnym przejdź na kartę Dane i w grupie Uzyskaj dane zewnętrzne wybierz Z innych źródeł ... i Z zapytania Microsoft.

2. Wybierz pliki Excel i wybierz zapisane dane
3. Jeśli pojawi się błąd informujący, że nie można znaleźć żadnych widocznych tabel, po prostu kliknij OK, a następnie w oknie dialogowym Opcje wybierz Tabele systemowe z listy programów. To powinno dać ci dostęp do arkuszy w arkuszu

4. Dodaj kolumny UID, Status i Data do zapytania

5. Dalej ... Dalej ... Dalej i wybierz Wyświetl dane lub edytuj zapytanie w Microsoft Query i wybierz Zakończ

6. Teraz dostajesz arkusz roboczy, który wygląda trochę jak wczesna wersja Access.

7. Kliknij przycisk SQL, aby uzyskać dostęp do samego zapytania, które moim zdaniem należy zmienić na coś takiego jak poniżej (używając GROUP BY i MAX, aby uzyskać najnowszą datę):

   WYBIERZ Sheet1$.UID, Sheet1$.Status, Max ( Sheet1$.Latest) FROM C:\Users\rgibson\Desktop\Book8.xlsx. Sheet1$ Sheet1$ GROUP BY Sheet1$.UID, Sheet1$.Status

   8. Możesz zamknąć zapytanie i wybrać miejsce, w którym chcesz zaimportować dane:

Jeśli chcesz rozważyć skorzystanie z narzędzia innej firmy, poleciłbym BeyondCompare . Jest łatwy w użyciu, ma bardzo hojną wersję próbną przed zakupem, dość niską cenę i jest dobry zarówno do porównywania, jak i łączenia wielu różnych typów plików i katalogów, w tym Excela. Możesz skopiować pojedyncze linie z jednego pliku do drugiego.

(Jestem użytkownikiem BC i nie mam nic wspólnego z firmą).

Jeśli wszystko inne zawiedzie, połącz wszystkie dane w jeden arkusz sortuj według daty (upewnij się, że sformatowano w ten sam sposób), a następnie skopiuj najnowszą datę do nowego arkusza kalkulacyjnego, a następnie następną najbardziej aktualną datę itd. Przejdź na kartę Dane usuń duplikaty. Ponieważ program Excel zachowuje pierwszy rekord wprowadzony do arkusza kalkulacyjnego i usuwa kolejny występujący duplikat, powinno to działać.

Obróć arkusz danych. Wprowadź unikatowy identyfikator w wierszu i pole daty w wartości i ustaw wartość na maks. Spowoduje to wygenerowanie dwu kolumnowej tabeli z UID i najnowszą datą powiązaną z tym UID. Sformatuj kolumnę wartości w tabeli przestawnej, aby była dokładnie równa wartości pola daty na karcie danych.

Następnie skopiowałem dane przestawne do nowej karty - nazwałem to „data resolver” - i utworzyłem nazwany zakres dla dwóch kolumn A i B i nazwałem zakres nazwany „date_selector”, który możesz nazwać jak chcesz. Właściwie ustawiłem zakres na $ A: $ B, więc byłoby gotowe, gdybym wrócił i dodał później dodatkowe wiersze.

Potem wróciłem do tabeli danych i dodałem dwie kolumny. Wyszukiwanie AV, które wprowadziło identyfikator UID i zwróciło maksymalną datę z zakresu „date_selector”, a następnie prostą instrukcję if, aby zachować wiersze, w których data z zakresu „date_selector” pasowała do daty w rekordzie.

Mój unikalny identyfikator znajduje się w kolumnie M Data jest w kolumnie H Wartość daty WYSZUKAJ.PIONOWO znajduje się w kolumnie A

WYSZUKAJ.PIONOWO = WYSZUKAJ.PIONOWO (M2, data_resolver, 2, FALSE)

JEŚLI OŚWIADCZENIE = JEŻELI (A2 = H2, „ZACHOWAJ”, „USUŃ”)

W przyszłości wszystko, co muszę zrobić, to odświeżyć oś przestawną, wkleić wyniki przestawne do karty date_resolver, wkleić wiersze z formułą i usunąć wiersze usuwania.

(myśli głęboko w pudełku)