エクセル 特定の文字列の消去について

質問

ある資料Aから、
ある資料Bの文字列を全部消去したいと思っています。

つまり資料Aの文字列から、
Bの文字を抜き出すわけですが
Bの資料にはAから削除したい文字が複数フィールドあります。

データ量が多いため、一列にすることが現実的ではないため、
Substituteのようなコラム処理を複数コラムをもとにできるのかどうか、
知識のある方いらっしゃいましたらアドバイスよろしくお願いします。

添付画像のように、
Aにはコラムが一つだけ

Bにはコラムが3つあり、
Bのすべてのコラムの文字列をAから消去したいです。

Bの1行目の文字列が
Aの違う行に出てきても消去の対象としたいです。

よろしくお願いします。

回答No.5

ushi2015

こんにちは
マクロ案です。
資料A、資料Bはシート名として、
資料AはセルB3以下最終行までに調査データ、
資料BはセルA3からセルC最終行までに該当データとして、
該当データがあった場合は行削除しています。
Sub test()
　　Dim shA As Worksheet
　　Dim shB As Worksheet
　　Dim e As Long
　　Dim i As Long
　　Dim r As Range
　　Dim t As Range
　　Dim x
　　ReDim v(0)

　　Set shA = Worksheets("資料A")
　　'調査データ範囲B3:B*
　　e = shA.Range("B" & Rows.Count).End(xlUp).Row

　　Set shB = Worksheets("資料B")
　　'該当データ範囲A3:C*
　　Set t = shB.Range("A3").CurrentRegion
　　For Each r In t
　　　　If r.Row > 2 And r.Value <> "" And r.Column < 4 Then
　　　　　　v(UBound(v)) = r.Value
　　　　　　ReDim Preserve v(UBound(v) + 1)
　　　　End If
　　Next
　　With shA
　　　　For i = e To 3 Step -1
　　　　　　For Each x In v
　　　　　　　　If Not IsEmpty(x) Then
　　　　　　　　　　If InStr(1, .Cells(i, 2), x) > 0 Then
　　　　　　　　　　　　.Cells(i, 2).EntireRow.Delete
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　Next
　　　　Next
　　End With
End Sub

回答No.4

bunjii

回答No.1の追加です。
文字列Aの値は" "で区切られた文字列が最大4つありますので作業用に4列使うことで資料Aの各行について中間処理データを抽出すればSUBSTITUTE関数で処理可能のようです。
添付画像では中間データをJ列からM列へ抽出してH列へ処理結果を導きました。
J3=IFERROR(INDEX($A:$F,INT(MOD(LARGE(INDEX((FIND($D$3:$F$11,$B3&$D$3:$F$11)<LEN($B3))*(FIND($D$3:$F$11,$B3&$D$3:$F$11)*100+ROW($D$3:$F$11)+COLUMN($D$3:$F$11)/10),0,0),SUMPRODUCT((FIND($D$3:$F$11,$B3&$D$3:$F$11)<LEN($B3))*1)-COLUMNS($J3:J3)+1),100)),MOD(LARGE(INDEX((FIND($D$3:$F$11,$B3&$D$3:$F$11)<LEN($B3))*(FIND($D$3:$F$11,$B3&$D$3:$F$11)*100+ROW($D$3:$F$11)+COLUMN($D$3:$F$11)/10),0,0),SUMPRODUCT((FIND($D$3:$F$11,$B3&$D$3:$F$11)<LEN($B3))*1)-COLUMNS($J3:J3)+1),1)*10),"")
J3セルをコピーしJ3:M11へ貼り付けました。
H3=TRIM(SUBSTITUTE(B3,TRIM(J3&" "&K3&" "&L3&" "&M3),""))
H3セルを下へH11セルまでオートフィルでコピーしました。

回答No.3

Prome_Lin

マクロでもよろしいのでしょうか？

前提条件として、実際のデータはすべて３行目からです。

次に、「Ａ列」に「No.」、「Ｂ列」に「文字列Ａ」、
「Ｄ列」に「文字列Ｂ」、「Ｅ列」に「文字列Ｃ」、「Ｆ列」に「文字列Ｄ」が入っているものとしています。

Option Explicit
Sub Test()
Dim i, j, k, l As Integer
Dim p, s As String
For i = 3 To Range("D2").End(xlDown).Row
For j = 4 To 6
For k = 3 To Range("A2").End(xlDown).Row
p = Cells(i, j).Value
s = Cells(k, 2).Value
Cells(k, 2).Value = Replace(s, p & " ", "")
Cells(k, 2).Value = Replace(Cells(k, 2).Value, p, "")
Next k
Next j
Next i
End Sub

簡単なマクロの説明です。

For i = 3 To Range("D2").End(xlDown).Row

は、「文字列Ｂ」の最後に文字が入っている行が何行目かを取得して３行目から、その最後の行まで繰り返し。

For j = 4 To 6

「Ｄ列」から「Ｆ列」までを繰り返し。

For k = 3 To Range("A2").End(xlDown).Row

「Ａ列」の最後の文字が入っている行が何行目かを取得し、３行目から繰り返し。

p = Cells(i, j).Value

文字列「Ｂ～Ｄ」を取得。

s = Cells(k, 2).Value

文字列「Ａ」を取得。

Cells(k, 2).Value = Replace(s, p & " ", "")

「文字列Ａ」のセルの中に、元の「文字列Ａ」から「文字列Ｂ～Ｄ＋半角スペース」を「""」に変換（結果的に削除）。

Cells(k, 2).Value = Replace(Cells(k, 2).Value, p, "")

もし、最後の文字列の場合は、スペースは存在しないので、もう一度、スペースの存在しない「文字列Ｂ～Ｄ」を削除です。

普通は、存在した場合のみ「Replace()」を実行すべきかも知れません。

存在しなければ、結果的に何もしないので、「If InStr(s, p) > 0 Then」は入れていませんが、プログラムの考え方としては、この「If」文はいれた方がいいのかも知れません。

回答No.2

kkkkkm

マクロでよろしければ
資料Aのシート名が資料A
資料Bのシート名が資料B
AのコラムはB2から始まっている
BのコラムはA列からC列までで2行目から始まっていてC列の最終行が資料Bの最終行である
という前提になっていて、Aのコラムを直接書き換えます。

Sub Example()
Dim c As Range, d As Range

With Sheets("資料B")
For Each c In .Range(.Cells(2, "A"), .Cells(Rows.Count, "C").End(xlUp))
With Sheets("資料A")
For Each d In .Range(.Cells(2, "B"), .Cells(Rows.Count, "B").End(xlUp))
If InStr(d.Value, c.Value) > 0 Then
d.Value = Replace(d.Value, c.Value, "")
End If
Next
End With
Next
End With
End Sub

回答No.1

bunjii

>Substituteのようなコラム処理を複数コラムをもとにできるのかどうか、知識のある方いらっしゃいましたらアドバイスよろしくお願いします。
Excelに組み込まれたSUBSTITUTE関数は1回につき1つの文字列が処理対象になります。
提示の画像で
資料AのNo.1は"123 ABC GHI NOM"であり、資料Bの文字列BのNo.1と文字列CのNo.1が含まれていますので、2つの文字列を連結して1回のSUBSTITUTE関数で削除することはできます。
次のような数式を使います。
=SUBSTITUTE([文字列ANo.1],[文字列BNo.1]&" "&[文字列CNo.1]&" ","")
各文字列はセルの番地で指定できますが、削除したい文字列の順番が一様では内容ですから自動的に割り付けることは無理でしょう。
提示の画像では削除対象が3列×9行なのですべてを入れ子に組み込めば可能かも知れません。
マクロ（VBA）を組めば自動で処理可能になりますが私は不得手なのでコードの見本を提示できません。
他の回答者のアドバイスを待ってください。