２ファイルを使用し商品単位のサイズ展開を抽出したい

質問

お世話になります。

２つのcsvファイルがあります。
それらを使ってデータを作りだしたいと思っています。

CSVファイル１
Ａ列／　Ｂ列　／Ｃ列
空白／商品番号／空白

CSVファイル２
Ａ列／　Ｂ列　／ Ｃ列 ／ Ｆ列 ／Ｈ列
空白／商品番号／タイプ／サイズ／カラー

ファイル１と２はB列の商品番号で紐づいていますが
ファイル１が親データであるのに対し、
ファイル２はサイズ展開分行数があります。

やりたいことはファイル１の商品番号単位で、
紐づくファイル２のサイズ展開をファイル１のC列にセットしたいです。

データは以下のようにセットされています。

CSVファイル１
Ａ列／　Ｂ列　／Ｃ列
　　／０００１／　　
　　／０００２／　　
　　／０００３／　　
　　／０００４／　　
　　／０００５／　　

CSVファイル２
Ａ列／　Ｂ列　／Ｃ列 ／ Ｆ列 ／Ｈ列
　　／０００１／i　　／50cm　／　　
　　／０００１／s　　／　　　／　　
　　／０００１／s　　／　　　／　　
　　／０００２／i　　／60cm　／　　
　　／０００２／i　　／70cm　／　　
　　／０００２／s　　／　　　／　　
　　／０００２／s　　／　　　／　　
　　／０００３／i　　／80cm　／　　
　　／０００３／i　　／90cm　／　　
　　／０００３／i　　／100cm ／　　
　　／０００３／s　　／　　　／　　
　　／０００３／s　　／　　　／　　
　　／０００４／i　　／50cm　／　　
　　／０００４／i　　／60cm　／　　
　　／０００４／s　　／　　　／　　
　　／０００４／s　　／　　　／　　
　　／０００５／i　　／90cm　／ブルー
　　／０００５／i　　／90cm　／レッド
　　／０００５／i　　／100cm ／ブルー
　　／０００５／i　　／100cm ／レッド
　　／０００５／s　　／　　　／
　　／０００５／s　　／　　　／

このとき、以下のように抽出されてほしいです。
CSVファイル１
Ａ列／　Ｂ列　／Ｃ列
　　／０００１／△50cm　　
　　／０００２／△60cm△70cm　　
　　／０００３／△80cm△90cm△100cm
　　／０００４／△50cm△60cm
　　／０００５／△90cm△100cm

※どの商品番号もタイプがsのものが2行あるのですが、
これはサイズ展開に関係ないので無視したいです。

※タイプがiのものがサイズ展開を司っているので対象です。

※０００５のように、同じ商品番号にカラーが2種類ある場合は
その分行が増えるのでサイズ展開としては90cmと100cmしかないのですが、
それが重複して表現されてしまいます。
カラーが複数種類ある商品データは多いので可能な限り
重複は抽出の時点で除きたいです。

ファイル１の親データの時点で1000商品ほどあるため
手作業で確認していくのは厳しく、
お知恵拝借出来たらと考えております。

どうぞ宜しくお願いいたします。

回答No.2

tom04 · ベストアンサー

こんばんは！
外しているかもしれませんが・・・
VBAになってしまいます。

＞２つのcsvファイルがあります。

という部分は一つのBookに2つのSheetがある！という前提での一例です。

↓の画像のように左側がSheet1で右側がSheet2だとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long
Dim c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1") '←Sheet1の部分は実際のSheet名に！
Set wS2 = Worksheets("Sheet2") '←こちらのSheet名も！

Application.ScreenUpdating = False
wS1.Cells.ClearContents
wS2.Range("B:B").Copy Worksheets("Sheet3").Cells(1, 1)
With Worksheets("Sheet3").Range("A:A")
.AdvancedFilter Action:=xlFilterInPlace, unique:=True
.Copy wS1.Cells(1, 2)
.Delete
End With
For i = 2 To wS1.Cells(Rows.Count, 2).End(xlUp).Row
For k = 2 To wS2.Cells(Rows.Count, 2).End(xlUp).Row
If wS2.Cells(k, 2) = wS1.Cells(i, 2) Then
Set c = wS1.Rows(i).Find(what:=wS2.Cells(k, 6), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
wS1.Cells(i, Columns.Count).End(xlToLeft).Offset(, 1) = wS2.Cells(k, 6)
End If
End If
Next k
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

尚、Sheet1のB列データは入力不要でSheet2の出現順に表示するようにしています。
この作業のためSheet3を作業用Sheetとして使っていますので、Sheet3はまっさらな状態にしておいてください。

ご希望の方法でなかったらごめんなさいね。m(_ _)m

お礼

やはりこういう目的を達成するためには
VLOOKUPなどの関数では厳しくて、
VBAの世界になるんですね。

VBとSQLはわかるのですがVBAを組んだことが無いのですが
お作法を学んで、自分でも
「こういうことならVBAで出来そうだ」というあたりが
つけられるようにはなりたいと感じました。

とにかく今回は並行して作業している中で、
他の作業を完了してOKWAVEを見たら既に回答を頂いていて
それを貼り付けて実行したら目的達成という
本当に助かる流れとなりました。

どうもありがとうございました！！

回答No.5

K Kazz（@JazzCorp）

＃４
間違いの訂正：Sub CSV2()の終盤
正）
If (InStr(.Cells(nn, "F"), .Cells(nn - 1, "F")) > 0) Then
.Cells(nn - 1, "F") = .Cells(nn, "F")
Else
.Cells(nn - 1, "F") = .Cells(nn - 1, "F") & " " & .Cells(nn, "F")
End If
誤）
If (InStr(.Cells(nn, "F"), .Cells(nn - 1, "F")) > 0) Then
Else
.Cells(nn - 1, "F") = .Cells(nn - 1, "F") & " " & .Cells(nn, "F")
End If

お礼

訂正に関してもご丁寧にご指示くださり、
大変感謝しております。

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

回答No.4

K Kazz（@JazzCorp）

’コレは準備ツール
’２つのCSVをEXCELシートに変換する。
’環境に合わせてConstの部分を適当に変更する。
’シート名はCSVのファイル名が継承される。
Option Explicit
Const xFileName = "CSV.csv,CSV2.csv"
Const xHeads = 1
Sub EasyCopyCSV()
Dim xPath As Variant
Dim CSV_filename As Variant
Dim CSV_SheetName As Variant
Dim FileCount As Long
Dim kk As Long
ChDir ThisWorkbook.Path
xPath = ThisWorkbook.Path & "\"
CSV_filename = Split(xFileName, ",")
FileCount = UBound(CSV_filename)
For kk = 0 To FileCount
Workbooks.Open (xPath & CSV_filename(kk))
CSV_SheetName = Worksheets(1).Name
Sheets(CSV_SheetName).Move Before:=ThisWorkbook.Sheets(1)
Next
End Sub

’情報が全て入っている方のシートを直接更新する。（上のツールで作られた一番目のシート）
’商品番号が同じレコードを圧縮し、サイズを出て来た順に1本化（ユニークに）
’番号DBへの更新は適用外。
Sub CSV2()
Dim xSheet As Worksheet
Dim xLast As Long
Dim kk As Long
Dim nn As Long
Set xSheet = Worksheets(1)
With xSheet
xLast = .Cells(Rows.Count, "B").End(xlUp).Row
For nn = xLast To xHeads + 1 Step -1
If (.Cells(nn, "C") = "s") Then
Rows(nn).Delete
End If
Next
xLast = .Cells(Rows.Count, "B").End(xlUp).Row
For nn = xLast To xHeads + 2 Step -1
'If (.Cells(nn, "C") = "i") Then
If (.Cells(nn - 1, "B") = .Cells(nn, "B")) Then
If (InStr(.Cells(nn, "F"), .Cells(nn - 1, "F")) > 0) Then
Else
.Cells(nn - 1, "F") = .Cells(nn - 1, "F") & " " & .Cells(nn, "F")
End If
Rows(nn).Delete
Else
End If
'End If
Next
End With
End Sub

お礼

ファイルを１つのエクセル内に別々のシートで
貼り直さなくても、元のままの二つのファイルで
作業ができるんですね！すごいです。

準備ツール等をどこにどのように用意すればよいか
よくわからなかったため、
スムーズに作業に入れなかったので
そのやり方をネットで調べる手間を回避するため
今回はNo.2のご回答者様のやり方を選ばせて頂きました。
今後はご教示頂いた手法も
試してみたいと思っています。

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

回答No.3

tom04

No.2です！
たびたびごめんなさい。
最後の
＞ファイル１の親データの時点で1000商品ほどあるため・・・
の部分を見逃していました。
ファイル1のデータを変更してはいけないのですよね？

↓のコードに変更してください。

Sub Sample2() 'この行から
Dim i As Long, k As Long
Dim c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1") '←Sheet1の部分は実際のSheet名に！
Set wS2 = Worksheets("Sheet2") '←こちらのSheet名も！

Application.ScreenUpdating = False
For i = 2 To wS1.Cells(Rows.Count, 2).End(xlUp).Row
For k = 2 To wS2.Cells(Rows.Count, 2).End(xlUp).Row
If wS2.Cells(k, 2) = wS1.Cells(i, 2) Then
Set c = wS1.Rows(i).Find(what:=wS2.Cells(k, 6), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
wS1.Cells(i, Columns.Count).End(xlToLeft).Offset(, 1) = wS2.Cells(k, 6)
End If
End If
Next k
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

何度も失礼しました。m(_ _)m

お礼

ありがとうございました！
こちらでやってみまして、大成功でした！

回答No.1

bin-chan

ACCESSをお持ちですか？
なければEXCELのピボットテーブルでも可。

１）CSVファイル２をインポート（すべての列をテキスト型で読む）
２）クエリのクロス集計ウィザードを実行（またはピボットテーブル）
　　「行見出し」にＢ列、「列見出し」にＦ列を指定。件数を表示。
３）できあがったら、EXCELにコピペ。（ACCESSの場合のみ）
４）件数が＞０のところを列名（サイズ）に置換。

行見出しがが求めたい結果のＢ列、
「検出された列をすべて連結したもの」のが求めたい結果のＣ列です。
（多少の編集が必要ですが）

最後にＣＳＶ形式で保存するなり、エディタ経由で貼るなりしてください。

お礼

アクセスは持っていますが、
クロス集計ウィザードをやったことがないため
そのやり方をネットで調べる手間を回避するため
今回はNo.2のご回答者様のやり方を選ばせて頂きました。

また機会を見てご教示頂いたアクセスの方法も
試してみたいと思っています。

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