2列のエクセルの表を変更したい

画像の左の表を右の表のように変更したいです。
office365でtranspose関数を使う方法を教えていただき、出来るようになりましたが、
office365の環境がないところでも使いたいので、再度質問します。
できるだけ簡単な方法で、あまり工程も多くない方がいいです。
簡単なVBAレベルまででお願いします。

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

• 

  ありがとうございます！出来ました！
  AGGREGATE関数、知りませんでした。。。
  あとで頑張って読み解きます。
  
  そうなんです。
  D列も自動作成できればもっと有難いです！
  大変図々しいですが、VBAも教えてもらえますか？

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/07/01 14:33

No.3 ベストアンサー 回答者： fujillin 回答日時： 2022/07/01 16:32

No1です。

＞大変図々しいですが、VBAも教えてもらえますか？
方法はいろいろあると思いますが、以下は一例です。

※ D2以降に記入するだけの処理にしてありますので、事前にクリア等
　が必要なら追加してください。
※ また、担当名は出現順となりますので、並べ替え等が必要な場合は
　追加してください。

Sub Q13022513()
Dim d, v, tmp, t, r As Range
Dim i As Long, j As Long, sep As String

sep = Chr(27)
Set r = Range(Cells(2, 1), Cells(Rows.Count, 2).End(xlUp))
If r(1).Row < 2 Then Exit Sub
v = r.Value
Set d = CreateObject("Scripting.Dictionary")

For i = LBound(v) To UBound(v)
tmp = Split(v(i, 2), ",")
For j = LBound(tmp) To UBound(tmp)
t = Trim(tmp(j))
If t <> "" Then
If d.Exists(t) Then d(t) = d(t) & sep & v(i, 1) Else d.add t, v(i, 1)
End If
Next j
Next i

Set r = Cells(2, 4)
v = d.keys
For i = LBound(v) To UBound(v)
t = Split(v(i) & sep & d(v(i)), sep)
r.Resize(, UBound(t) + 1).Value = t
Set r = r.Offset(1)
Next i
End Sub

この回答へのお礼

ありがとうございます。
出来ました。
頑張って読み解きます。

お礼日時：2022/07/04 13:15

No.6 回答者： Belltreeriver 回答日時： 2022/07/04 15:52

失礼しました。

EXCEL2019で検証し、送信時のPCが2013を使用し式をコピペしたので、
_xlfn.TEXTJOINとなっていました。
TEXTJOINが正です。配列数式なのでCtr+Shift+Enterで式確定をしてください。
D2=LEFT(TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\",FIND("\",TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\")-1)1),""))=0))&"\")-1)

この回答へのお礼

ありがとうございます、感謝です。

お礼日時：2022/07/07 18:24

No.5 回答者： Belltreeriver 回答日時： 2022/07/03 18:55

№４です。

先の回答はABC、あいう等の１文字しか対応していなかったので、実務に合わせた数式を頭を捻って考えました。EXCEL2019以降に対応します。大変長いです。
TRANSPOSE(ROW($1:$5))の数式部分は担当欄がMaxで５名までを意味します。
D2=LEFT(_xlfn.TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\",FIND("\",_xlfn.TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\")-1)

配列確定（Ctr+Shift+Enter）し、下へフィルコピー

この回答へのお礼

ありがとうございます。
セルD2が「#NAME?」になってしまいました。

お礼日時：2022/07/04 13:20

No.4 回答者： Belltreeriver 回答日時： 2022/07/02 22:44

D列を自動作成する数式になります。

（ローテクで長いです）

D1=CHAR(MIN(INDEX(CODE(MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1)),0)))

D2=IF(ISNA(INDEX(MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1),MATCH(0,COUNTIF(D$2:D2,"="&MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1)),0))),"",INDEX(MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1),MATCH(0,COUNTIF(D$2:D2,"="&MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1)),0)))
配列確定（Ctr+Shift+Enter）し、下へフィルコピー

この回答へのお礼

ありがとうございます。
セルD1は表示できたのですが、セルD2は空白になってしまいます。。。

お礼日時：2022/07/04 13:16

No.2 回答者： d-q-t-p 回答日時： 2022/07/01 16:22

PowerQueryでやればいいと思いますけど。

担当列をカンマ指定で列の分割
分割した列を選択して列のピボット解除
値列で行のグループ化
グループ化したテーブルにインデックス列を追加
グループ列から品名・インデックス列を展開
インデックス列を指定して列のピボット（値列「品名」で集計はしない）

インデックス列の追加以外は機能を選択するだけでできます。
インデックス列は行のグループ化の時に「each _」を
「each Table.AddIndexColumn(_, "インデックス", 1, 1)」に
変更すればいいです。

この回答へのお礼

「PowerQuery」
すぐに使える環境がなかったので、まだ確認できていませんが、
是非時間を作ってPowerQueryを身に着けたいと思いました。
ありがとうございます。

お礼日時：2022/07/04 13:12

No.1 回答者： fujillin 回答日時： 2022/07/01 12:09

こんにちは

ご提示のレイアウトで、E2セルに
=IFERROR(IF($D2="","",INDEX($A:$A,AGGREGATE(15,6,ROW(A$2:A$6)/NOT(ISERROR(FIND($D2,$B$2:$B$5))),COLUMN(A1)))),"")
の式を入力し、右方、下方にフィルコピー。

では、いかがでしょうか？

※ D列も自動作成したいような場合は、関数で行うよりも、VBAの方が簡単だと思います。