繰り返しコピペ(1部内容変更して)

質問

Excel2013使用です。
添付した画像のようなデータと、実際には別シートに名簿というテーブルデータがあります。
やりたいことは、担当列に全員と入力されていた場合該当する行を名簿の人数分コピーしてデータの下に貼り付けし、かつ担当列の値を名簿の人名に変えたいというものです。
担当列に「、」で区切られたデータは下記サイトを参考に分割、コピペ出来たのですが上記がうまい処理が思いつかず行き詰まってしまいました。
お知恵をお貸しください。よろしくお願い致します。

回答No.2

SI299792

下記サイトというのが解りませんが、
特に関数でという指示が無いのでVBA で作りました。
Sheet1、元データ
Sheet2、結果
Option Explicit
'
Sub Macro1()
　　Dim S As Worksheet
　　Dim ROut As Long
　　Dim RInp As Long
　　Dim ROuC As Long
　　Dim RInC As Long
　　Dim What As Variant
'
　　Set S = Sheets("Sheet1")
　　RInC = S.Cells(Rows.Count, "F").End(xlUp).Row - 1
　　Sheets("Sheet2").Select
　　ROut = 2
　　Range("A2:D" & Rows.Count).ClearContents
'
　　For RInp = 2 To S.Cells(Rows.Count, "C").End(xlUp).Row
　　　　What = S.Cells(RInp, "C")
　　　　ROuC = 1
'
　　　　If What = "全員" Then
　　　　　　Cells(ROut, "C").Resize(RInC) = S.[F2].Resize(RInC).Value
　　　　　　ROuC = RInC
　　　　Else
　　　　　　What = Split(What, "、")
　　　　　　ROuC = UBound(What) + 1
　　　　　　Cells(ROut, "C").Resize(ROuC) = WorksheetFunction.Transpose(What)
　　　　End If
　　　　Cells(ROut, "A").Resize(ROuC, 2) = S.Cells(RInp, "A").Resize(, 2).Value
　　　　Cells(ROut, "D").Resize(ROuC) = S.Cells(RInp, "D")
　　　　ROut = ROut + ROuC
　　Next RInp
End Sub

回答No.1

lalique0304

担当列に「、」で区切られたデータがあれば出来るとのことなので；

１．営業所＋所属の文字列をキーとし、その部署全員の名前を「、」で区切った全員名簿を作成し、キーでソート（昇順）します。
　　大阪営業2　　 神田、東野、長谷川
　　東京営業　　　鈴木、佐々木

２．次にデータを全員の行が連続して並ぶように担当でソートします。
３．最初の全員のセルにVLOOKUP関数で全員名簿を参照する計算式を入れます。
　　VLOOKUP(営業所のセル＆所属のセル、全員名簿、２、FALSE）
４．正しく参照できたら計算式を最後の全員のセルまでコピーします。
５．最後に計算式が入ったセル全体をコピーし、そのまま値の貼り付けをします。

これで全員のセルが「、」で区切られた名前に置き換わります。