違うシートにあるリストの文字を複数のセルごと別シートに抽出する方法

お世話になります。
Excel2013を使用していますが、違うシートにあるリストの文字を抽出する方法について
質問させてください。

Sheet 1　A列２行目～AA列40000行目までデータ
Sheet 2　B列2行目から抽出したい項目が10～30個前後（その都度変わります）
Sheet 1のI列～L列の間にSheet 2のB列にある言葉を含むセルを抽出したいのですが
半角、全角、大文字、小文字が混在していて　next i で対応していると
数と量が多くてフリーズや修正に対応しきれず困っています。
なんとか配列などで対応することはできないでしょうか。
（なんとなく配列だと早いイメージがあるのですが、こだわりはありません）

ちなみに抽出したいのはSheet 1のA列、B列、D列と、I列～L列のSheet 2のB列と同じセルの値です。
例えばSheet 1のJ列とSheet 2のB列にある文字が同じなら抽出するならA列、B列、D列、J列
Sheet 1のL列とSheet 2のB列にある文字が同じならA列、B列、D列、L列

どうかアドバイスよろしくお願いします。

質問者からの補足コメント

• お世話になります。
  説明不足ですみません。
  やりたいことはsheet3（別シート）に転記をすることです。
  質問文にも書きましたが、sheet3に転記をすること自体はできています。
  ただ、量が多いので時間がかかるのと、項目が増えた際に都度都度抽出するkeyを追加していくのが大変なので、他の方法がないのかアドバイスをいただきたかったのです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/08/17 16:15

• 転記(コピー)の仕方について捕捉です。
  Sheet3のA1～D1には項目があり、二行目から下に追加していきたいです。
  
  例えばSheet 1のJ列とSheet 2のB列にある文字が同じならA列、B列、D列、J列をSheet3のA列～D列にコピー
  
  sheet 1のL列とSheet 2のB列にある文字が同じならA列、B列、D列、L列をSheet3のA列～D列にコピー
  
  今はsheet2の文字を毎回抽出キーとして記載しています。
  If Sh1.cells(i,j) = "nuts" then
  Sh3.cells(n,1) = Sh1.cells(i,1)
  Sh3.cells(n,2) = Sh1.cells(i,2)
  Sh3.cells(n,3) = Sh1.cells(i,4)
  Sh3.cells(n,4) = Sh1.cells(i,j)
  End if

    補足日時：2019/08/17 17:40

• 見ていただきありがとうございます。
  まるきり同じ言葉2列以上あることはありませんが、違う項目が同じ行に含まれていることはあります。そのときはsheet3の行を変えて転記しています。
  
  例　同じ行にnutsとフルーツが含まれている
  A B C nuts
  A B C ピスタチオ&フルーツ

  No.4の回答に寄せられた補足コメントです。 補足日時：2019/08/18 10:56

No.5 回答者： めぐみん_ 回答日時： 2019/08/19 08:19

No.3です。

なんか回答がつくほどに少しずつ仕様を開示していっている感じがして、回答者的にはどこまでなのか？でお先真っ暗な感じです。
質問文の内容をまず完成させた後にあとはそちらでやるか、質問を一度閉じて条件（要望？）を明確に全て書き出した再質問を立て直した方が宜しいのでは？
と思いますよ。

画像貼付もあるとわかりやすいかもですよ。

この回答へのお礼

色々説明不足ですみません。
出直してきます。

お礼日時：2019/08/19 13:55

No.4 回答者： ママチャリ 回答日時： 2019/08/18 08:42

I列～L列のうち、2列以上が一致してしまった場合は、最初に一致した列だけをコピーすれば良いのでしょうか？

それとも、そんなデータは存在しないので考慮不要なのでしょうか？

この回答へのお礼

ありがとうございました。

お礼日時：2019/08/19 13:56

No.3 回答者： めぐみん_ 回答日時： 2019/08/18 08:31

シート１のＩ～Ｌ列についてはCOUNTIF関数で有無を調べられるのでは？

シート３のＤ列についてはシート１のＩ～Ｌ列のどこの値であろうと、検索値はシート２のＢ列の値なのでしょ？
なのでシート１のＩ～Ｌ列のどこで見つかったとか調べる必要無いように思いますけど。
（背景色や書式でコピペしなきゃいけないなら別ですが、値だけを代入するならと言う事で）

この回答へのお礼

見ていただきありがとうございます。
自分が確認するだけなら良いのですが
リストにして出して欲しいと言われてて...。

実際には入力されている位置(項目名)もsheet3に記載していますが、配列だとこれは無理かなと思い省きました。
Sh3.cells(n,5) = Sh1.cells(1,j)

お礼日時：2019/08/18 11:03

No.2 回答者： googoo900 回答日時： 2019/08/17 23:32

データが多いので、照合に時間がかかっているのだと思います。

ヒットする数が多い場合は、補足コメントのようにIF文で照合していくのが無難かと思います。
ヒットする数が少ない場合は、FIND文で照合していくのが早いと思います。

質問文の場合、検索対象セルはI2～L40000なので約160000個あります。
例えば、ヒットするのが1個しかない場合は1個ずつIFで調べるよりもFINDで調べるほうが早いです。
一方、ヒットするのが100000個ある場合はIFで調べるほうが早いです。


配列というか、メモリーにデータをとりこんで処理するほうが早いと思いますが、自分の得意ジャンルではないので・・・。

この回答へのお礼

アドバイスありがとうございます。
やはり難しいのですね...。

お礼日時：2019/08/18 10:57

No.1 回答者： zircon3 回答日時： 2019/08/17 16:02

「抽出」というのが実際何をしたいかですが。

。。
「移動」なのか「コピー」なのか。
ある範囲内の全セルの内容を「移動」あるいは「コピー」したいのか、ある範囲内にあるセルのうち、ある条件を満たす値を格納したセルの内容を「移動」あるいは「コピー」したいのか。

その辺の「仕様」をご自身でしっかり言葉（文章）にまとめられることです。
その上で、それを自行する具体的手順を箇条書きにまとめられることです。

あと、おやりになりたい事を同一シート内で行うのであればその処理をご自身で実現することは出来ていますか？
出来ておられるのであれば上記の仕様文書の作成は不要で、その処理内容の抽出先のセル指定のところにシート名指定を加えればＯＫ・・・ということになります。

まずはその辺から。(_ _)
参考まで。