Ячейки условного форматирования с разными значениями для строк с одинаковым значением в определенном столбце

Question

Мне нужно условно отформатировать ячейки с разными значениями между одинаковыми столбцами, если их значение для определенного столбца одинаково.

сценарий

Моя электронная таблица представляет собой базу данных контактов, где каждая строка представляет собой запись контакта, а каждый столбец содержит тип данных (фамилия, улица 1, улица 2, город, штат и т.д.) Для каждой записи контакта.

Электронная таблица представляет собой объединение контактной информации, хранящейся в разных офисах нашей компании. Мы объединили всю контактную информацию в одном источнике, чтобы мы могли согласовать любые расхождения и сохранить все наши контакты в одном месте в будущем.

Некоторые контакты имеют только одну запись - например, когда только один из наших офисов имел данные для этого контакта, поэтому он не может не согласиться с данными, которые мы имеем из любого другого офиса; другие контакты имеют несколько записей (т. е. может быть только одна строка для Фреда Джонсона, но четыре строки для Джейн Смит). Каждый контакт должен иметь одинаковую точную контактную информацию, прежде чем мы сможем загрузить ее в облачную базу данных, иначе она создаст дубликаты.

Мы потратили много времени, пытаясь согласовать данные между записями для одного и того же человека, и теперь нам нужно выяснить, где существуют оставшиеся расхождения.

Я объединил поля LastName и FirstName, чтобы строки с одинаковым значением в этом поле можно было сравнивать друг с другом - цель состоит в том, чтобы выделить ячейки с разными данными в одном столбце по сравнению с другими записями с одинаковым значением в столбец LastFirst (если есть).

Как я могу это сделать?

До сих пор я знал, что функция Find Special может выделять ячейки при сравнении с назначенной строкой или столбцом, но мне нужно сделать сравнение со строками, которые имеют одинаковое значение для определенного столбца.

У меня есть доступ к Excel 2010 и 2013 для выполнения этой функции.

Спасибо!

Answer 1

Могут быть более точные решения, но вам не нужно объединять имена и фамилии, чтобы создать столбец "FullName".

Предварительный просмотр нашего вывода

(белые ячейки справа показывают, какой вывод получит наша формула для управления условным форматированием):

Вот наша формула

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
   -COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Не забудьте отрегулировать его в соответствии с количеством строк в вашем наборе данных и положением столбцов имени и фамилии, если их нет в A & B.

Я также предположил, что ваши первые данные без имен начинаются в ячейке C2, то есть это самый верхний левый бит данных, который вы хотите проверить на несоответствия. Отредактируйте "С" в этой формуле, если вам нужно.

Применение условного форматирования

Скопируйте приведенную выше формулу (внеся все необходимые изменения), затем нажмите на крайнюю левую ячейку, которую вы хотите проверить на несоответствия (здесь я буду ссылаться на "C2"). Теперь нажмите кнопку « Conditional Formatting на ленте и выберите « New Rule .

Выберите « Use a formula to determine which cells to format , затем вставьте формулу в текстовое поле (убедитесь, что в ней нет пробелов - если вы скопировали сверху, вам нужно будет вырезать несколько). Теперь вы можете нажать Format чтобы установить, как вы хотите выделить несоответствия. Я пошел за (бросать!) красная заливка. OK пока не вернетесь к электронной таблице.

Если у вас нет несоответствия в ячейке C2, может показаться, что ничего не произошло, но это потому, что нам все еще нужно применить правило ко всему набору данных. Оставляя C2 по-прежнему выбранным, нажмите « Manage Rules в меню ленты Conditional Formatting .

Теперь вы можете выбрать весь диапазон, который вы хотите проверить на несоответствия. В поле « Applies to нажмите и перетащите, чтобы выбрать все, что вы хотите сравнить (или, если у вас много строк, для скорости просто введите ссылку на ячейку =$C$2:$Z$999)

Нажмите ОК и все!

Как это устроено

Эта формула использует COUNTIFS() для подсчета количества строк для этого человека:

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)

Затем мы выясняем, сколько строк имеют имя этого человека И значение в столбце, который вы проверяете. Если все строки идентичны, то это число должно точно соответствовать первым COUNTIFS() .

COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Если мы вычтем последние из первых, и все строки совпадут, то формула выдает 0 и не выполняет никакого условного форматирования. Однако, если что-то отличается, выходной сигнал будет равен 1 или выше, вызывая условное форматирование.

Заметка

Мне пришлось обернуть окончательные критерии COUNTIFS() в операторе IF() для обработки пробелов - COUNTIFS не очень любит пробелы (кажется неуверенным, считать их 0 или "". Weird).

---

Скачать файл

Этот пример документа также доступен для скачивания.