マクロや関数で来店管理表を作成したい。

質問

最終来店日別・顧客番号順の管理表を作成したいと考えております。
　　表１・初日データ
行番号　　　　　　　　 列番号　Ｇ　　　　　　 列番号　Ｈ
　　　　　　　　　　　　 顧客番号　　　　　　 最終来店日
　　6　　　　　　　　　 100　　　　 　　　　　　2011-11-09
　　7 　　　　　　　　　 30 　　　　　　　　　 2011-11-09
　　8 　　　　　　　　　 500　　　　　　　　　　 2011-11-09
　　9 　　　　　　　　　 200　　　　　　　　　　 2011-11-09
10
～ 　
1000 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　表２・２日目データ
行番号　　　　　　　　 列番号　Ｇ　　　　　　 列番号　Ｈ
　　　　　　　　　　　　 顧客番号　　　　　　 最終来店日
　　6　　　　　　　　　 150 　　　　 　　　　　　2011-11-10
　　7 　　　　　　　　　 500 　　　　　　　　　 2011-11-10
　　8 　　　　　　　　　 10 　　　　　　　　　　 2011-11-10
　　9 　　　　　　　　　　　　　　　　
～　
1000 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　表３・来店日管理表
行番号 列番号　Ｂ 　列番号　Ｅ 　　　　列番号 F
　　　　　　 顧客番号 　最終来店日 　　　来店回数
　　6　　　 10　　　　　 2011-11-10　 　　1
　　7　　　 150 　　　　　　　　　2011-11-10 　　1
　　8 　　　 500 　　　　　　　　　2011-11-10 　　2
　　9 　　　 30 　　　2011-11-09 　　1
　　10　　 100 　　　　 2011-11-09　　　　　　1
　　11 　　 200 　　　　2011-11-09 　　　　　1
　　12 　　
　　～　
1000

●〔表１〕・〔表２〕のデータから、〔表３〕のような〔来店日管理表〕の作成方法を教えてください。
　１・初日
１・初日に〔表１〕のデータを〔表３〕に表示し、〔列番号　Ｆ〕に来店回数を〔１〕と表示する
２・〔最終来店日〕別・〔顧客番号順〕に表示する
３・〔表１〕のデータを消去する

　２・２日目
１・〔表３〕のデータと〔表２〕のデータを統合して、初来店者は〔列番号　Ｆ〕に来店回数を　　　　　　　　　　　〔１〕と表示する
　　　　　　　　　複数回来店者は、〔列番号　Ｆ〕に来店回数を〔前回回数＋１〕して表示する
２・〔最終来店日〕別・〔顧客番号順〕に表示する
３・〔表１〕のデータを消去する

宜しくお願い致します。

回答No.1

tom04 · ベストアンサー

こんばんは！

2日目の
＞３・〔表１〕のデータを消去する
は
＞３・〔表２〕のデータを消去する
だと解釈しての一例です。

表1はSheet1・表2はSheet2にあり、Sheet3にまとめるようにしてみました。
「初日」と「二日目」の二つのマクロになりますので、「初日」のマクロから実行してみてください。

Sub 初日()
Dim i, j, k As Long
Dim ws1, ws3 As Worksheet
Set ws1 = Worksheets(1)
Set ws3 = Worksheets(3)
j = ws1.Cells(Rows.Count, 7).End(xlUp).Row
k = ws3.Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
If k > 5 Then
Range(ws3.Cells(6, 2), ws3.Cells(k, 6)).ClearContents
End If
ws3.Columns(5).NumberFormatLocal = "yyyy-m-d"
For i = 6 To j
If WorksheetFunction.CountIf(ws3.Columns(2), ws1.Cells(i, 7)) = 0 Then
With ws3.Cells(Rows.Count, 2).End(xlUp).Offset(1)
.Value = ws1.Cells(i, 7)
.Offset(, 3) = ws1.Cells(i, 8)
.Offset(, 4) = 1
End With
End If
Next i
k = ws3.Cells(Rows.Count, 2).End(xlUp).Row
Range(ws3.Cells(6, 2), ws3.Cells(k, 6)).Sort key1:=ws3.Cells(5, 5), _
order1:=xlDescending, _
key2:=ws3.Cells(5, 2), order2:=xlAscending
i = ws1.Cells(Rows.Count, 7).End(xlUp).Row
Range(ws1.Cells(6, 7), ws1.Cells(i, 8)).ClearContents
Application.ScreenUpdating = True
End Sub

Sub 二日目()
Dim j, k As Long
Dim ws2, ws3 As Worksheet
Set ws2 = Worksheets(2)
Set ws3 = Worksheets(3)
Application.ScreenUpdating = False
For j = 6 To ws2.Cells(Rows.Count, 7).End(xlUp).Row
If WorksheetFunction.CountIf(ws3.Columns(2), ws2.Cells(j, 7)) Then
k = WorksheetFunction.Match(ws2.Cells(j, 7), ws3.Columns(2), False)
With ws3.Cells(k, 5)
.Value = ws2.Cells(j, 8)
.Offset(, 1) = ws3.Cells(k, 6) + 1
End With
Else
With ws3.Cells(Rows.Count, 2).End(xlUp).Offset(1)
.Value = ws2.Cells(j, 7)
.Offset(, 3) = ws2.Cells(j, 8)
.Offset(, 4) = 1
End With
End If
Next j
k = ws3.Cells(Rows.Count, 2).End(xlUp).Row
Range(ws3.Cells(5, 2), ws3.Cells(k, 6)).Sort key1:=ws3.Cells(5, 5), _
order1:=xlDescending, _
key2:=ws3.Cells(5, 2), order2:=xlAscending
j = ws2.Cells(Rows.Count, 7).End(xlUp).Row
Range(ws2.Cells(6, 7), ws2.Cells(j, 8)).ClearContents
Application.ScreenUpdating = True
End Sub

※　別Sheetでマクロを試してみてください。
※　的外れならごめんなさいね。m(_ _)m

お礼

tom04様

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

2日目の
＞３・〔表１〕のデータを消去する
は
＞３・〔表２〕のデータを消去する
だと解釈しての一例です。

●申し訳ございません、当方の間違いでした。

最初に三枚のシートで作成しました。

次に一枚のシートで作成しました。

いずれも、考えていましたように作成することが出来ました。

此れもご指導のおかげと感謝いたします。

一枚のシートでの作成をしておりましたので、御礼が遅くなりお詫び申し上げます。

今後ともご指導のほど宜しくお願い致します。

回答No.2

imogasi

こんなの課題を丸投げで、この質問コーナーをシステム作成の下請けと考えていないか。
一番大切なことは、＞表１・初日データ＞表２・２日目データ　　が別シートどまりか、別ブックにあるのか書いてない。
量的に出来るなら、集約データ表を１つのシートに作ってはどうか。
ーー
同一ブック内の別シートにあるなら
Sub test01()
Dim sh
For Each sh In Worksheets
d1 = sh.Range("A65536").End(xlUp).Row
d2 = Worksheets("sheet4").Range("A65536").End(xlUp).Row
If sh.Name <> "Sheet4" Then 'Sheet4　は集約シート
sh.Range("A2:C" & d1).Copy Worksheets("Sheet4").Range("A" & d2 + 1)
End If
Next
End Sub
３シートの例でテストしたが、いくつあってもコードは変わらないで良い。Ｓｈｅｅｔ４のシート名は「集約」とでもすれば良い。
各シート第１行目は項目見出し行とする。
ーー
そそれが済めば、集約シートで、顧客番号＋最終来店日でソートとして、望みの表にするコードを考えたら。ぐっと易しくなる。
別ブックの場合は１つのフォルダの中にブックを集め（手操作）どうしたら集約できるか考えること。
Ｇｏｏｇｌｅででも「ＶＢＡ　フォルダ　エクセルファイル　集約」で照会すれば沢山記事が出る。わたしも何回も回答している
ＦＡＱだ。