共通データのある複数のセルを１つにまとめる

質問

宜しくお願いします。

Excelで住所録を編集したいのですが、

画像の様に、Ｂ列の文字(市町村区)が一致する場合、
以降の住所を同じセルでまとめたいのですが、
関数でできることなのでしょうか？

画像の上が元データになります。
下画像のＡＢ列は、入力済みとお考えください。
Ｃ列に関数を入れ、該当データを引っ張ってきたいです。

まとめられたデータに区切り等は不要です。

お分かりの方はお力添えください。
宜しくお願いいたします。

回答No.3

KURUMITO · ベストアンサー

元のデータがシート1に有るとし、Ａ，Ｂ，Ｃ列にお示しのデータが1行目は項目名で2行目から下方にあるとします。
ＤおよびＥ列は作業列として、お求めの表はシート2に表示させることにします。
シート1のＤ2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",IF(B1=B2,D1&C2,IF(B1<>B2,C2,"")))

Ｅ2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=COUNTIF(B:B,B2),MAX(E$1:E1)+1,""))

なお、1万行にも及ぶデータの場合にはＤ2およびＥ2セルをコピーしてから、名前ブックスでD2:E10000と入力して確定すればそのセル範囲が指定されますので右クリックして貼り付けを操作すればよいでしょう。

シート2では1行目を項目名などにしてＡ2セルには次の式を入力しＣ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$E:$E),"",INDEX(Sheet1!$A:$D,MATCH(ROW(A1),Sheet1!$E:$E,0),IF(COLUMN(A1)<=2,COLUMN(A1),IF(COLUMN(A1)=3,4,10))))

これでシート2には予めＡおよびＢ列にデータを入れておく必要はありません。自動的に表示されます。

お礼

望む通りの結果を得られました。
ありがとうございます。

昨日４時間以上かけて行った12000行のデータ処理が、
教えていただいた関数では、10程度で終了しました。

残り10万近いデータをどうしようか悩んでいましたので
本当に助かりました。

大感謝です！ありがとうございました♪

回答No.4

tom04

No.1です！
前回のコードは同一Sheetでの操作のコードでした。

お礼欄を見ると、
（1）Sheet1にC列までのデータがあり、Sheet2にB列までのデータしか入っていない
（2）希望の操作はSheet2で行いたい！
というコトですよね？

Sheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
前回同様↓のコードをコピー＆ペーストしてマクロを実行してみてください。
両Sheetともデータは1行目からあるとします。

Sub test() 'この行から
Dim i, k As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) Then
Rows(i).Delete
End If
Next i
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If ws.Cells(k, 1) = Cells(i, 1) And ws.Cells(k, 2) = Cells(i, 2) Then
Cells(i, 3) = Cells(i, 3) & ws.Cells(k, 3)
End If
Next k
Next i
Columns.AutoFit
Application.ScreenUpdating = True
End Sub 'この行まで

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

お礼

ありがとうございます。
そして、お手数おかけしました。
お蔭さまで、望む結果を得られました。

然しながら、5000行を超える処理を一度に行うと相当時間がかかってしまいます。
また、データを追加した際の継続処理は、関数の方がマッチしていました。

1000件程度の処理なら、教えていただいたマクロの方が手間が少ないので、
部分的な処理で利用させていただきます。

私のために時間を割いていただき、ありがとうございました。

回答No.2

keithin

丁寧に手を動かしていけば別に何もムズカシイことをせずとも、猿でも結果を得られます。

添付図：
ご相談のような作り方をしないで、１行目にはふつーにタイトル行を記入します。

Ｄ２セルには
=C2&IF(A2&B2=A3&B3,D3,"")
と記入して、リストの下端までコピー貼り付けます

Ｅ２セルには
=IF(A2&B2<>A1&B1,D2,"")
と記入してリストの下端までコピー貼り付けます

必要ならオートフィルタを設定し、Ｅ列を「空白ではない」で絞り込んで全体をコピー、シート２にでも貼り付けます。

お礼

早速のご回答ありがとうございました。

また、私の説明不足をお詫びします。
元データが数万行でしたので、
手作業ではちょっと…、でご質問させていただきました。

お答えいただいた数式でもいけそうです。
ありがとうございます。

回答No.1

tom04

こんばんは！
関数で！というのがご希望のようですが、仮にできたとしてもかなりの数式になると思います。
（簡単にできる方法があればごめんなさい）

そこでVBAになってしまいますが・・・一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）
データは画像通り、1行目からあるとします。

Sub test() 'この行から
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) Then
Cells(i - 1, 3) = Cells(i - 1, 3) & Cells(i, 3)
Range(Cells(i, 1), Cells(i, 3)).Delete (xlUp)
End If
Next i
Columns.AutoFit
End Sub 'この行まで

※　関数ではないのでデータ変更があるたびにマクロを実行する必要があります。

参考にならなかったらごめんなさいね。m(_ _)m

お礼

丁寧なご回答をありがとうございました。
私のためにお手数かけていただき感謝いたします。

教えていただいたマクロを実行してみました。
AB列の重複行は削除されますが、Ｃ列に値を取得できませんでした。

<Sheet1>

　　A列　　　　B列　　　　C列
1 東京都　　渋谷区　　上原
2 東京都　　渋谷区　　鶯谷町
3 東京都　　渋谷区　　宇田川町
4 東京都　　板橋区　　相生町
5 東京都　　板橋区　　赤塚
6 東京都　　板橋区　　小豆沢

<Sheet2>

　　A列　　　　B列　　　　C列
1 東京都　　渋谷区
2 東京都　　渋谷区
3 東京都　　渋谷区
4 東京都　　板橋区
5 東京都　　板橋区
6 東京都　　板橋区

マクロ実行結果(Sheet2)

　　A列　　　　B列　　　　C列
1 東京都　　渋谷区
2 東京都　　板橋区

ご迷惑でなければ、もう少し面倒みていただけないでしょうか
宜しくお願いいたします。