エクセルで単語データを作成したい

質問

単語学習を効率よく進めたくて次のような単語リストを作りたいと考えています。

A列には番号、 B列には英文のデータがあります。
C列には英文の単語をデータとして生成したいのです。
一度出たデータは省いて新規のものだけ。
この例では、I、 you、mistake(s)は２度目に除外されます。
大文字や、複数形、ed, ing形はややこしくなりますので、手作業で省いてもよいかと思います。

D列には元の英文（A列）の番号

このようなことは可能でしょうか。
自分なりに作っていると、代名詞や前置詞などが多数出てきて困ります。
要は目新しい単語を英文から抽出したいということです。
良い方法があれば教えてください。よろしくお願いします。

A 列　　　B 列
1 　　　　I won't talk to you unless you admit your mistake.
2 　　　　I wonder why you are fighting over such small mistakes.

C列　　　D 列
I 　　　　 1
won't 　　　　 1
talk 　　　　 1
to 　　　　　 1
you 　　　　　 1
unless 　　　　 1
admit 　　　　 1
your 　　　　　 1
mistake 　　　　 1
wonder 　　　　 2
why 　　　　　 2
are 　　　　　　 2
fighting 　　　　 2
over 　　　　　　 2
such 　　　　　　 2
a 　　　　　　　 2
small 　　　　　　 2

回答No.5

mitarashi

#2の方の言及されているdictionary（連想配列）使用版です。
一旦配列に受けてから処理し、シートに転写していますので、少々分かり難いかもしれません。
Sheet1のA1から、A列の各セルに英文が入っているとします。途中に空白行がある事は想定しておりません。
Sub test()
Dim srcArray As Variant, buf As Variant, dstArray() As Variant
Dim i As Long, j As Long, myKeysCount As Long
Dim myDic As Object, myKey As String, keys As Variant
Dim srcSheet As Worksheet, dstSheet As Worksheet

Set srcSheet = ActiveWorkbook.Sheets("Sheet1") '文の入っているシート
Set dstSheet = ActiveWorkbook.Sheets("Sheet2") '出力先のシート
'セルの値を配列に入れる
With srcSheet
srcArray = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value
End With
Set myDic = CreateObject("Scripting.Dictionary")
'連想配列に入れる／既存アイテムの場合加算
For i = 1 To UBound(srcArray, 1)
buf = Split(srcArray(i, 1), " ")
For j = 0 To UBound(buf)
myKey = buf(j)
'文末の.を除去
If Right(myKey, 1) = "." Then myKey = Left(myKey, Len(myKey) - 1)
If Not myDic.exists(myKey) Then
myDic.Add myKey, 1
Else
myDic.Item(myKey) = myDic.Item(myKey) + 1
End If
Next j
Next i
keys = myDic.keys
myKeysCount = UBound(keys)
'出力データを一旦入れる配列のサイズを決めて、連想配列のデータを代入
ReDim dstArray(1 To myKeysCount + 1, 1 To 2)
For i = 0 To myKeysCount
dstArray(i + 1, 1) = keys(i)
dstArray(i + 1, 2) = myDic.Item(keys(i))
Next i
'出力先シートのA1セルから配列を丸ごと貼り付け
With dstSheet
.Cells.Clear
.Range("A1").Resize(myKeysCount + 1, 2) = dstArray
End With
Set myDic = Nothing
End Sub

お礼

ちょっと、私の最初の注文がまずかったのでみなさんにご迷惑をおかけしました。
みなさんのおかげで、悪戦苦闘の末、ほぼ思い通りの処理ができました。
英文の中から、指定の単語を抽出し、それを前述の単語リストでチェック、既出のものはその文番号を書き出す。
みなさんからの熱心な回答のおかげで、この３日間私も解決にくらいつくことができました。
これで自分なりの英文、単語のストックが可能になると思います。
一層、英語力の強化に努めたいと思います。
ユニークな提言をいただきましたので、また時間を見つけてトライしたいと思います。
ありがとうございました。