エクセルで同じセルを引き出し、かつそこから他のデータも引き出す方法

エクセルの質問です。
A列にID、B列に日付(A列に入力されているIDの来店日など)、C列にID(A列に含まれているものや、含まれていないもの、重複しているものもある)、D列に日付(B列に含まれているものや、含まれていないもの)、E以降の列には様々なデータが入力されているとします。C, D列からA列およびB列が一致しているものを抽出し、かつE列以降のデータも引き出す方法はないでしょうか。

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

• tom04さん
  ありがとうございます。
  求めているものはまさにこれなのですが、なぜかsheet 2で30行分くらいしか出てきません…。
  ちなみにA,B列は約1000行、C,D列は50000行、E列以降のデータが多く、EM列くらいまであるのですが、関係あるでしょうか？

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/02/11 17:18

No.1 ベストアンサー 回答者： tom04 回答日時： 2020/02/11 01:28

こんばんは！

↓の画像のように元データはSheet1にあり、Sheet2に表示するとします。
今回も作業用の列を設けるのが簡単だと思います。

画像ではSheet1の作業列G2セルに
=IF(COUNTIFS(A:A,C2,B:B,D2),ROW(),"")

という数式を入れフィルハンドルでこれ以上データはない！というくらいまで下へコピーしておきます。

Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!C:C,SMALL(Sheet1!$G:$G,ROW(A1))),"")

という数式を入れ、列・行方向にフィル＆コピー！
これで画像のような感じになります。

※　B列は好みの表示形式で！

尚、作業列が目障りであれば遠く離れた列にするか
非表示にしてください。m(_ _)m

No.5 回答者： tom04 回答日時： 2020/02/12 09:16

No.1・4です。

関数・マクロ両方とも30行程度しか表示されない！というコトですが・・・
A＆B列データとC＆D列データで一致するものが実際にはその程度しかない！というコトしか考えられないのですが。
（見た目は同じように見えても何らかの違いがある。）

VBAの場合、C＆D列でA＆B列と一致するものが複数存在する場合は、エラーになるはずですし
関数の場合、複数存在しても作業列にすべての行番号が表示されるはずなので
表示されている行番号のデータはSheet2に表示されるはずです。

こちらで考えられる原因としてはこの程度しか思いつきません。m(_ _)m

この回答へのお礼

元のdateから見直してみました。ご指摘の通り、dateの並び替えの時点でずれていたようです。お陰様で助かりました。
心より感謝します。

お礼日時：2020/02/14 00:57

No.4 回答者： tom04 回答日時： 2020/02/11 21:33

No.1です。

＞ちなみにA,B列は約1000行、C,D列は50000行、E列以降のデータが多く、EM列くらいまで・・・

相当量のデータなのですね。
関数では荷が重いかも。

VBAでやってみました。
元データはSheet1にあり、1行目が項目行で最終列まで項目名が入っているとします。
そして、Sheet2にC列以降のデータを表示するようにしてみました。

標準モジュールにしてください。

Sub Sample1()
　Dim myDic1 As Object, myDic2 As Object
　Dim i As Long, j As Long
　Dim lastRow As Long, lastCol As Long
　Dim buf As String, myStr As String
　Dim wS As Worksheet
　Dim myKey, myItem, myR, myAry1, myAry2

　　Set myDic1 = CreateObject("Scripting.Dictionary")
　　Set myDic2 = CreateObject("Scripting.Dictionary")
　　Set wS = Worksheets("Sheet2")
　　　wS.Cells.ClearContents
　　　　With Worksheets("Sheet1")
　　　　　lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
　　　　　　wS.Range(Cells(1, "A"), Cells(1, lastCol - 2)).Value = Range(.Cells(1, "C"), .Cells(1, lastCol)).Value
　　　　　　lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　　　　myR = Range(.Cells(2, "A"), .Cells(lastRow, "B"))
　　　　　　　　For i = 1 To UBound(myR, 1)
　　　　　　　　　myStr = myR(i, 1) & "_" & myR(i, 2)
　　　　　　　　　　If Not myDic1.exists(myStr) Then
　　　　　　　　　　　myDic1.Add myStr, ""
　　　　　　　　　　End If
　　　　　　　　Next i
　　　　　　lastRow = .Cells(Rows.Count, "C").End(xlUp).Row
　　　　　　　myR = Range(.Cells(2, "C"), .Cells(lastRow, lastCol))
　　　　　　　　For i = 1 To UBound(myR, 1)
　　　　　　　　　myStr = myR(i, 1) & "_" & myR(i, 2)
　　　　　　　　　　If myDic1.exists(myStr) Then
　　　　　　　　　　　For j = 3 To lastCol - 2
　　　　　　　　　　　　buf = buf & myR(i, j) & "_"
　　　　　　　　　　　Next j
　　　　　　　　　　　　myDic2.Add myStr, Left(buf, Len(buf) - 1)
　　　　　　　　　　End If
　　　　　　　　　　　buf = ""
　　　　　　　　Next i
　　　　End With
　　　　　myKey = myDic2.keys
　　　　　myItem = myDic2.items
　　　　　　myR = Range(wS.Cells(2, "A"), wS.Cells(UBound(myKey) + 2, lastCol))
　　　　　　　For i = 0 To UBound(myKey)
　　　　　　　　myAry1 = Split(myKey(i), "_")
　　　　　　　　　myR(i + 1, 1) = myAry1(0)
　　　　　　　　　myR(i + 1, 2) = myAry1(1)
　　　　　　　　myAry2 = Split(myItem(i), "_")
　　　　　　　　　For j = 0 To UBound(myAry2)
　　　　　　　　　　myR(i + 1, j + 3) = myAry2(j)
　　　　　　　　　Next j
　　　　　　　Next i
　　　　　　Range(wS.Cells(2, "A"), wS.Cells(UBound(myKey) + 2, lastCol)) = myR
　　　　　Set myDic1 = Nothing
　　　　　Set myDic2 = Nothing
　　　　　wS.Activate
　　　　MsgBox "完了"
End Sub

こんな感じではどうでしょうか？m(_ _)m

この回答へのお礼

わざわざここまでありがとうございます。
ですが、A, B列1000件近くあるうちの30行くらいしか反映されないようです・・・。

お礼日時：2020/02/11 22:59

No.3 回答者： ママチャリ 回答日時： 2020/02/11 07:32

「フィルターオプション」が簡単かと。

「データ」タブの「並べ替えとフィルタ」から「詳細設定」をクリックしてみて下さい。
フィルタというと条件外の行を非表示にするイメージがありますが、抽出先のオプションで「指定した範囲」を指定することにより、任意の位置に取り出すことも可能です。

No.2 回答者： 銀鱗 回答日時： 2020/02/11 03:01

手作業で行う手順をそのまま再現するだけで良いですよ。

「ID」と「日付」を見て、一致するデータにマークを付けて、マークが付いている行のデータを拾い上げる。
…って事をしませんか。
一致するかは「COUNTIF」「COUNTIFS」関数を使って比較できます。
マークは、上から順に連番にすれば便利。マークを1から順に拾えば良いんです。

あとはXLOOMUP関数や、MATCH関数とINDEX関数の組み合わせ、などでデータ参照させるだけです。
（このやり方はインターネット上を検索すれば図解入りの解説付きで説明されているサイトを見つけられると思いますので、そちらを探して参照してください）

・・・
すでにこの手順の回答が返っていますが、この考え方を理解できていないのであれば、
回答を真似るのではなく、まずは自身で作ってみましょう。