Excel の行をテーブルに結合する

Question

Excel で次のような表を作成することは可能ですか?

そして、次のようにします。

したがって、表に同じ住所を持つ人が 2 人以上いる場合は、名前全体を 1 つのセルに書き、共通の住所を別のセルに書き込んで行を結合します。

Answer 1

VBA でこれを行うための疑似コード: (実際のコードは自分で作成できます)。これは回答内にのみ記述されているため、空欄を埋める必要がありますが、各アドレスを循環して、そのアドレスに一致する列を持つ名前を収集するという大まかな考え方は理解できると思います。

dim address_on as string 'current address
dim names as string 'concatenate list of names
dim in_list_already as boolean

    For address_row = 1 to range().end(xldown).row
    'loop through addresses
        address_on = Range("Column" & address_row).value
        names = ""

        'First check if address_on is already in destination list?
        in_list_already = false
        for check_row = 1 to range("Destination").end(xldown).row
            If range("Destination Col" & check_row).value = address_on then
                in_list_already = true
                Exit for
            End if
        next

        if in_list_already = false then
            'Find all names that have this address
            for name_row = 1 to range().end(xldown).row
                If range("Address Column" & name_row).value = address_on then
                    names = names  & Range("Name Column" & name_row).value & ","
                End if
            next

            'remove last comma
            names = names.remove(Len(names)-1,1)

            'add to list
            Range("Column to insert to 1" & next_slot).value = names
            Range("Column to insert to 2" & next_slot).value = address_on 

        End if 

next

ご覧のとおり、names = names & Range("Name Column" & name_row).value & ","一致するものがあればリストに連結するだけです。

上記の方法は次のとおりです。

1. データを循環させる
2. 出力に項目がすでに存在しますか? 存在しない場合は表示しません (重複がないようにするため)。
3. あなたが現在いる住所を持つすべての名前を収集します
4. 出力結果

Answer 2

質問のようにレコードが集約されている場合、数式を使用してこれを行うのは簡単です (一致するアドレスが一緒にない場合は、より複雑になります)。

列を非表示解除してメソッドを表示します。

名前用とフィルタリング用の 2 つのヘルパー列を作成しました。質問の希望する順序に一致させるために、名前ヘルパー列を左側に挿入しました。A2 の数式:

=IF(C2=C1,A1& ", " &B2,B2)

私の場合、列 C は住所列です。これは、現在の行の住所が前の行の住所と一致するかどうかを確認します。一致しない場合は、新しい住所であることを意味し、関連付けられている名前が挿入されます。前の住所と同じ住所の場合は、前の行の結果にコンマと行の名前が連結されます (したがって、一致する住所はいくつでも存在できます)。

ヘルパー列 D は、行のアドレスがそのアドレスの最後であるかどうか (つまり、次の行のアドレスが異なるかどうか) をチェックします。D2 の数式:

=C2<>C3

数式を列に入力したら、D1 をクリックし、メニューから [オートフィルター] を選択します。D1 のプルダウンで、FALSE の選択を解除します。これにより、各アドレスの最後の行以外のすべての行が非表示になります。

永続的な「クリーンな」リストが必要な場合は、必要なフィルターされた列をコピーして、新しい場所に貼り付けます。以下の列 F と G のように、表示されている列のみがコピーされます。

フィルターがオンの状態でも貼り付けることはできますが、行が非表示になっている範囲に貼り付けると、フィルターをオフにするまで結果の一部が非表示になります。

Answer 3

問題を解決する UDF (ユーザー定義関数) を 1 つ提案したいと思います。

使い方：

• ソース データは Range 内にあると想定していますA2:B8。

• この配列（CSE）式を に入力しE2、 で終了します。Ctrl+Shift+Enter＆記入してください。

  {=INDEX($B$2:$B$8, MATCH(SMALL(IF(COUNTIF($E$1:E1, $B$2:$B$8)=0, COUNTIF($B$2:$B$8, "<"&$B$2:$B$8), " "), 1), COUNTIF($B$2:$B$8, "<"&$B$2:$B$8), 0))}
  

• コピーそしてペースト下記のコードは モジュール。

  Function ExtractinOneCell(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
  
  Dim I As Long
  
  Dim xRet As String
  For I = 1 To LookupRange.Columns(2).Cells.Count
  
      If LookupRange.Cells(I, 2) = LookupValue Then
          If xRet = "" Then
              xRet = LookupRange.Cells(I, ColumnNumber) & Char
          Else
              xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
          End If
      End If
  
  Next
  
  ExtractinOneCell = Left(xRet, Len(xRet) - 2)
  
  End Function
  

• この数式を入力してD2、下まで記入してください。

  =ExtractinOneCell(E2,$A$2:$B$8,1,", ")