Znajdź przedmioty w jednej kolumnie, których nie ma w innej kolumnie

90

Mam dwie kolumny w Excelu i chcę znaleźć (najlepiej wyróżnić) elementy, które są w kolumnie B, ale nie w kolumnie A.

Jaki jest najszybszy sposób to zrobić?

microsoft-excel microsoft-excel-2007 conditional-formatting

— Krzyż
źródło

91

Wybierz listę w kolumnie A
Kliknij prawym przyciskiem myszy i wybierz Nazwij zakres ...
Wpisz „ColumnToSearch”
Kliknij komórkę C1
Wprowadź tę formułę: =MATCH(B1,ColumnToSearch,0)
Przeciągnij formułę w dół dla wszystkich pozycji w B

Jeśli formuła nie znajdzie dopasowania, zostanie oznaczona jako # N / A, w przeciwnym razie będzie to liczba.

Jeśli chcesz, aby było to PRAWDA dla dopasowania i FAŁSZ dla braku dopasowania, użyj tej formuły:

=ISNUMBER(MATCH(B1,ColumnToSearch,0))

Jeśli chcesz zwrócić nieuzasadnioną wartość i zwrócić pusty ciąg dla znalezionych wartości

=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"",B1)

— devuxer
źródło

6

Następnie ... Wprowadź tę formułę do formatowania warunkowego na obu listach i użyj jej, aby podświetlić (lub cokolwiek) niepasujące komórki

— Kije

4

„Nazwij zakres” nie pojawia się dla mnie? Testowane na programach Excel 2010 i 2016.

— KERR

Jeśli, podobnie jak @KERR i ja, nie masz „Nazwij zakres ...”, po prostu użyj A:Alub swój zakres zamiast ColumnToSearchw formułach.

— Michael

Jeśli, podobnie jak ja, na początku nie zdajesz sobie sprawy, gdzie wpisać „ColumnToSearch” (krok 3), wpisz go jako nazwę tworzonego zakresu nazwanego.

— DaveL17

„Nazwij zakres” pojawia się jako Define Name...(Excel 2019)

— oliver-clare

29

Oto szybka i brudna metoda.

Podświetl kolumnę B i otwórz formatowanie warunkowe .

Wybierz Użyj formuły, aby określić, które komórki mają zostać wyróżnione .

Wprowadź następującą formułę, a następnie ustaw preferowany format.

=countif(A:A,B1)=0

— Ellesa
źródło

Użyłem „= ISODD (LICZ.JEŻELI (A: A, B1) = 0)”, aby wydrukować wartość prawda dla „1” lub fałsz dla „0”

— Ramraj,

15

Wybierz dwie kolumny. Przejdź do Formatowanie warunkowe i wybierz Wyróżnij reguły komórek. Wybierz Zduplikowane wartości. Po przejściu do następnego kroku możesz zmienić go na unikalne wartości. Właśnie to zrobiłem i zadziałało dla mnie.

— DOB
źródło

jeśli masz wartość w kolumnie B powtórzoną, to ich nie podświetli

— magodiez

1

Z pewnością jest to najprostsza i najtrafniejsza odpowiedź? Nie wymaga formuł.

— KERR

W tym czasie nic się nie dzieje.

— Overmind

12

Zajęło mi to wieczność, aby to rozgryźć, ale jest to bardzo proste. Zakładając, że dane zaczynają się od A2 i B2 (dla nagłówków), wprowadź tę formułę w C2:

=MATCH(B2,$A$2:$A$287,0)

Następnie kliknij i przeciągnij w dół.

Komórka z #N/Aoznacza, że wartość bezpośrednio obok niej w kolumnie B nie pojawia się nigdzie w całej kolumnie A.

Pamiętaj, że musisz zmienić 287 $ A, aby dopasować całą tablicę wyszukiwania w kolumnie A. Na przykład, jeśli dane w kolumnie A zmienią się na 1000 pozycji, powinno to być 1000 $ A.

— brenton
źródło

3

=MATCH(B2,$A:$A,0)pracował dla mnie.

— nilgun

4

Zobacz moją formułę tablicową odpowiedź na listę A nie znalezioną w B tutaj:

= IFERROR (INDEKS (2 $ A $: 1999 $ A $, MECZ (0, IFERROR (MATCH (2 $ A $: 1999 A $, 2 $ B $: 399,0 $ B $)), LICZNIK ($ C $ 1: $ C1, $ 2 USD: 1999 USD)), 0)), „”)

Porównywanie dwóch kolumn nazw i zwracanie brakujących nazw

— Jeeped
źródło

To rozwiązanie jest dobre, ale zależy od sortowania kolumn źródłowych alfabetycznie, w przeciwnym razie pobierze duplikaty.

— Steve Taylor

3

Moim wymaganiem było nie wyróżniać, ale pokazywać wszystkie wartości oprócz tych, które są duplikatami między 2 kolumnami. Korzystałem z rozwiązania @ brenton i dalej poprawiałem, aby pokazać wartości, dzięki czemu mogę bezpośrednio korzystać z danych:

=IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, "")

Skopiuj to w pierwszej komórce trzeciej kolumny i zastosuj formułę przez kolumnę, aby wyświetlić wszystkie elementy z kolumny B, których nie ma w kolumnie A.

— Gautam Jain
źródło

Wystąpił błąd w powyższej formule = JEŻELI (ISNA (PODAJ.POZYCJĘ (B2, $ 2 $: 2642,0 $)), A2, "") powinien być = JEŻELI (ISNA (PODAJ (B2, $ A $ 2: $) 2642,0 $)), B2, „”)

1

Dziękuję tym, którzy udostępnili swoje odpowiedzi. Dzięki twoim rozwiązaniom udało mi się dotrzeć do własnych.

W mojej wersji tego pytania miałem do porównania dwie kolumny - pełną klasę ukończenia studiów (Kol. A) i podzbiór tej klasy ukończenia Szkoły (Kol. B). Chciałem móc wyróżnić w pełnej klasie kończącej studia tych studentów, którzy byli członkami tego podzbioru.

Umieszczam następującą formułę w trzeciej kolumnie:

=if(A2=LOOKUP(A2,$B$2:$B$91),1100,0)

To zakodowało większość moich uczniów, chociaż przyniosło pewne błędy w pierwszych kilku wierszach danych.

— Drew McAllister
źródło

-3

w C1piśmie =if(A1=B1 , 0, 1). Następnie w Conditional formattingwybierz Data barslub Color scales. To najłatwiejszy sposób.

— A.Ramin Walyar
źródło