エクセル教えてください 重複データの削除方法

質問

エクセルは良く使っていますが、レベルは初級の上です。

同一セル内に複数の重複データが入っていますが、重複しないものを抽出し
新しいセルに入れたいと思っています。

重複データの区切りは、セミコロン";"　パイプ区切り"|",空白、改行が混在しています。
重複しないものを、新しいセルに入れたいのですが、どのようにしたら良いでしょうか？
新しいセルでのデータ区切りは、コンマ（又はパイプ区切り）にしたいと思っています。

例で説明します。

A列（番号） B列（元のデータ）　　　　　　　　　　 　 C列（重複を除いたデータ）
　1　　　　　 タイ ベトナム 中国|タイ|タイ　　　　　　　　　タイ,ベトナム,中国　　
　2　　　　　 インド ベトナム|インド タイ |インド 中国　　インド,ベトナム,タイ,中国

※B列のデータを重複を除いて、C列に入れたいと思っています

※２行のデータを例にしましたが、実際には2万くらいデータがあります

※マクロは全くわからないので、多少の手作業があっても関数レベルで
やれると助かります。（いまから、マクロを勉強してもよいでしょうか？？）

良い方法をアドバイスいただければ助かります。

回答No.3

noname#204879 · ベストアンサー

Sheet2 において、
１．セル A1 に次式を入力して、此れを下方にズズーッとドラッグ＆ペースト
　　=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!B1,CHAR(10)," "),"|"," "),";"," ")
２．列Ａ全体を選択して、[コピー]→[値の貼り付け]を実行
３．列Ａ全体を選択したままで、[データ]→[区切り位置]を実行
４．カンマやタブなどの…に区切られたデータ”に目玉を入れて、[次へ]
５．“スペース”および“連続した区切り文字…扱う”にチェックを入れて、[完了]

Sheet3 において、
６．セル A1 に次式を入力して、此れを下方および右方にズズーッとドラッグ＆ペースト
　　=IF(COUNTIF(Sheet2!$A1:A1,Sheet2!A1)=1,Sheet2!A1,0)
　　何処まで右に？
　　全セルのデータが 0 になる列までネ（此処ではそれを仮に列Ｅとしておく）
７．列Ａ～Ｅ全体を選択したままで、[編集]→[ジャンプ]→[セル選択]を実行
８．“数式”に目玉を入れ、かつ、その配下の“数値”だけにチェックを残して[OK]
９．数値 0 の全セルが選択状態になるので、そのままの状態で[編集]→[削除]を実行
10．“左方向にシフト”に目玉を入れて「エイヤッ！」と Enterキーを叩き付け

お礼

ありがとうございます。これなら操作の流れがわかりました。

操作手順１０以下が途中で切れているように思えますが、
後は何とか、目的は果たせそうです。
特に６に相当する操作が思いつかなかったので、助かりました。

回答No.4

mitarashi

#3さんと同様にデータ／区切り位置で元データを複数列に分解します。
このデータがB列からG列にあるとします。H列は空にしておきます。
２行目の例では、I列に
=IF(H2="",C2,IF(ISERROR(FIND(C2,H2)),H2&","&C2,H2))
という式を入れて、右方、下方にずずっと、十分な数だけコピーします。多すぎても問題ありませんが、不足はNGです。
最終列にお望みのデータが得られると思います。ご参考まで。

なお、#1のkeithinさんと同様の方法で、但しユーザー定義式で無くてループを回して一発でやってみました。一旦配列に取り込む高速化の技は取り入れていますが、2万行を当方の環境(Core i5 3.2GHz,xl2010)で、600ミリ秒前後で処理できました。
要するにVBAを用いると、1秒かからずに処理できますよと、誘惑しています。

※replace関数を複数回用いる方法と、Midで切り出して、","に変換してMidで書き戻す方法を試してみましたが、replace関数を用いる方が高速でした。（これが検証してみたかっただけです．．．）

お礼

ありがとうございます。追加の工夫の部分、トライしてみます。

スキルがあると、VBAでは１秒弱でできるというのは、確かに誘惑です。
VBAを作るところに∞秒かかるので、先に進めず誘惑を味わえないのが残念です。

今回はありがとうございました。また、教えてください。

回答No.2

tom04

こんばんは！

＞※マクロは全くわからないので、多少の手作業があっても関数レベルで
とありますが、関数で行うとしても結構厄介だと思います。

というコトでお望みでないVBAになってしまいますが、一例です。
データは2行目以降にあるとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, str As String, buf As String, myArray, tmp
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
tmp = Cells(i, "B")
For k = 1 To Len(tmp)
str = StrConv(Mid(tmp, k, 1), vbNarrow)
If str = " " Or str = "|" Then
tmp = WorksheetFunction.Replace(tmp, k, 1, "*")
End If
Next k
myArray = Split(tmp, "*")
For k = 0 To UBound(myArray)
If InStr(buf, myArray(k)) = 0 Then
buf = buf & myArray(k) & ","
End If
Next k
Cells(i, "C") = Left(buf, Len(buf) - 1)
buf = ""
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数で簡単にできる方法があればごめんなさいね。m(_ _)m

お礼

せっかく教えていただいたのです、VBAがわからず実行できません。
今回は期限があるので実行できませんが、早く理解できるように
なりたいと思いました。
実は初投稿で、こんなに教えていただけたので感激しています。
次回はよろしくお願いいたします

回答No.1

keithin

次の通りにするだけで出来ます。

手順：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

function myf(target) as string
　dim buf as string
　dim a as variant, ax as variant
　dim mydic as object
　set mydic = createobject("Scripting.Dictionary")
　on error resume next

’区切り文字を統一する
　buf = replace(target, ";", ",")
　buf = replace(buf, "|", ",")
　buf = replace(buf, "　", " ")
　buf = replace(buf, " ", ",")
　a = split(buf, ",")

’重複を除いたデータを採取する
　for each ax in a
　　if ax <> "" then mydic(ax) = 1
　next

’結果を作成する
　myf = join(mydic.keys, ",")
end function

ファイルメニューから終了してエクセルに戻る
好きな場所、たとえばＢ列に元データを準備する
てきとーなセル、たとえばＣ１に
=myf(B1)
と関数を記入する。
以下コピー。

お礼

VBAの基本的なところも説明いただいたのですが、残念ながら、
この説明でもまだVBAが理解できず、例題でも実行できませんでした。
VBAも必須だな、と思いました。これから勉強します。