Условное сцепление содержимого ячеек по строкам

Question

У меня есть лист с тысячами строк, которые содержат уникальный идентификатор A и один 3 разных кода ошибки M Каждый отдельный идентификатор может иметь 1, 2 или 3 ошибки. Они всегда будут в одном и том же порядке.

Я набрал вручную и на N что желаемый результат. Я написал такую формулу в столбцах O, P, Q с текстом ошибки:

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

Я пробовал что-то подобное в R но это не работает по понятным причинам. Есть ли способ сделать это так, чтобы R выглядел как N?

=CONCATENATE(O42,"/", P42,"/",Q42)

Если не возможно сделать с формулой Excel, есть ли способ VBA?

Answer 1

ОК, немного почесал голову, но я понял:

Столбцы O , P & Q как у вас есть, под названием Brand , Product и OEM . Превратите все это в таблицу с помощью Ctrl - T (не обязательно, но удобно, и мой столбец R полагается на него, но вы можете использовать ссылки на столбцы, если хотите)

Колонка R:

=IF(LEN([Brand])>0,[Brand],IF(LEN([Product])>0,[Product],IF(LEN([OEM])>0,[OEM],"")))

Столбец S:

=IF(A2=A4,F2&"/"&F3&"/"&F4,IF(A2=A1,"",IF(A2=A3,F2&"/"&F3,F2)))

К сожалению, кажется, что единственный способ использовать ссылки на таблицы в другой строке - это использовать Offset , поэтому для простоты я вернулся к ссылкам на ячейки. Это как бы сводит на нет крутой / удобный фактор превращения всего этого в стол в первую очередь, но, как бы там ни было ...

А также... Вот картина того, на что это похоже:

Answer 2

Я бы начал с оптимизации ваших формул O , P и Q  У вас сейчас есть

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

IFERROR - отличная функция для отображения очищенной версии вычисленного значения, которое может быть кодом ошибки; Я использую его и часто рекомендую в ответах на Super User.  Как вы, наверное, знаете,

• IFERROR(calculated_value, default_value)

коротка для

• IF(ISERROR(calculated_value), default_value, calculated_value)

Но использование IFERROR для создания очищенной версии некоторого значения, а затем проверка этого значения для выполнения каких- либо условий является излишне неловким способом использования IFERROR . Приведенная выше формула может быть упрощена до

=IF(ISERROR(SEARCH("Brand is not valid", M42)), "", "Brand")

И, как я уверен , что вы знаете, SEARCH("Brand is not valid", M42) проверяет , содержит ли M42 Brand is not valid  Но поскольку столбец M может содержать только три строки ошибок, его можно сократить до

=IF(ISERROR(SEARCH("Brand", M42)), "", "Brand")

или упрощено до

=IF(M42 = "Brand is not valid", "Brand", "")

---

Хорошо, теперь я сделаю формулы O , P и Q немного более сложными:

• O42 → =IF($A42=$A41, O41, "") & IF(ISERROR(SEARCH("Brand", $M42)), "", "Brand")
• P42 → =IF($A42=$A41, P41, "") & IF(ISERROR(SEARCH("Product", $M42)), "", "Product")
• Q42 → =IF($A42=$A41, Q41, "") & IF(ISERROR(SEARCH("OEM", $M42)), "", "OEM")

Формула для O42 гласит:

Если это вторая или третья строка для этого идентификатора (столбец A), посмотрите на ячейку над этой (т. Е. На ячейку столбца O для предыдущей строки), чтобы увидеть, установили ли мы уже, что эта вещь имеет недопустимый бренд ,  Кроме того, посмотрите на Столбец M для этой строки, чтобы видеть, является ли это, Brand is not valid .  Затем объедините результаты.

Поскольку уникальный идентификатор никогда не будет указан дважды с одной и той же ошибкой (верно?), Эти два промежуточных результата никогда не будут непустыми, поэтому в сущности это делает «ИЛИ»:

Показать значение Brand если эта строка, ИЛИ одна из предыдущих строк для этого идентификатора, содержит ошибку недопустимой марки.

Это дает эффект или перетаскивая значения O , P и Q вниз до последней строки для каждого идентификатора:

Обратите внимание, что в строках 41, 44, 47 и 49 показаны краткие формы всех ошибок, которые относятся к их соответствующим идентификаторам в столбцах O , P и Q

Я определил столбец R же, как вы.  См. Раздел Создание списка содержимого ячеек, разделенных запятыми, за исключением пробелов , чтобы узнать способы устранения нежелательных слешей.

Если желаемое сцепление только в строках 41, 44, 47 и 49 достаточно, значит, все готово.  В противном случае определите N42 как

=IF($A22=$A23, N23, R22)

или же

=IF($A22<>$A23, R22, N23)

Это почти тот же трюк, который я использовал в столбцах O , P и Q , но в противоположном направлении:

Если это последняя строка для этого идентификатора (то есть, если это строка 41, 44, 47 или 49), используйте конкатенацию значений из этой строки (которая является полной коллекцией кодов ошибок для этого идентификатора).  В противном случае посмотрите на ячейку ниже этой (т. Е. На ячейку Столбца N для следующей строки), у которой будет правильный ответ.

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