複数の行の値を 1 つの行に結合する方法

Question 1

あなたが提供した説明にはあまり自信がありませんが、とにかくここに書いてみます。

重要なポイント - 以下では、メンバー # MVP システム内で、エントリー日が常に登録日よりも上に表示されることを前提としています。

安全のため、コピーで作業し、各行にインデックス番号を追加します (たとえば、を挿入しColumnA、にと1入力しA1、数式を Row2250 までコピーします。その上に [コピーして貼り付け (特殊)]/[値] を選択します。=A1+1A2ColumnA

を選択しD2、ホーム > スタイル - 条件付き書式、新しいルール、数式を使用して書式設定するセルを決定、この数式が当てはまる値を書式設定: を挿入=AND(NOT(ISBLANK(D2)),OR(D1=D2,D2=D3))、書式、塗りつぶし、黄色を選択、OK、OK。条件付き書式 - ルールの管理で、適用先にを入力し=$D$2:$D$2250、適用します。OK。

スプレッドシート全体を選択し (見出しの A の左側、1 の上にある三角形をクリック)、[データ] > [並べ替えとフィルター - フィルター] の順に選択し、[ColumnD色によるフィルター] で黄色を選択します。

Row1 を青色で番号が付けられた最後の行までコピーし、A1別のシート (Sheet2 など) に貼り付けます。

Sheet2 で、を削除しF1、セルを上にシフトして、OK をクリックします。また、N1:T1も実行します。(ここでは、追加の目視が必要になる場合があります。)

ColumnASheet2 に新規を追加します。をに1入力しA1、を選択し2て、選択範囲の右下隅をつかみ、マウスの左ボタンを押したままを押したまま、必要なだけ下にドラッグし、を押したままにします。 A2A1:A2Ctrl

Sheet2 を選択し、データ > 並べ替えとフィルター - 並べ替えで、データにヘッダーがある、並べ替え基準ColumnA(最初の1s)、値で並べ替え、最小から最大に並べ替え、OK をチェックします。

2Sheet2 のが含まれる最低行番号ColumnAと、最も占有されている最高行番号をメモします。を削除しますColumnA。

最初のシートに戻り、黄色でハイライト表示されている行をすべて削除します。

ColumnASheet2 で、小さい方の行番号と、それより大きい番号の他のすべての行を選択し、コピーして最初のシートの下部に貼り付けます。

うまくいけば、必要なことがほとんど達成されます。達成されない場合でも、それは「正しい方向への一歩」です。確認すると、最後に占有された行は、上記の 2 つの数値の差である 2250+1 を引いた値になるはずです。

MEMBER FIRST NAME を確認するには、MEMBER # とその参照テーブルを作成し、それに基づいてコピーしたシートの MEMBER FIRST NAME を比較することをお勧めします。Curt か Curtis かは、おそらく判断の問題です。

Answer

あなたが提供した説明にはあまり自信がありませんが、とにかくここに書いてみます。

重要なポイント - 以下では、メンバー # MVP システム内で、エントリー日が常に登録日よりも上に表示されることを前提としています。

安全のため、コピーで作業し、各行にインデックス番号を追加します (たとえば、を挿入しColumnA、にと1入力しA1、数式を Row2250 までコピーします。その上に [コピーして貼り付け (特殊)]/[値] を選択します。=A1+1A2ColumnA

を選択しD2、ホーム > スタイル - 条件付き書式、新しいルール、数式を使用して書式設定するセルを決定、この数式が当てはまる値を書式設定: を挿入=AND(NOT(ISBLANK(D2)),OR(D1=D2,D2=D3))、書式、塗りつぶし、黄色を選択、OK、OK。条件付き書式 - ルールの管理で、適用先にを入力し=$D$2:$D$2250、適用します。OK。

スプレッドシート全体を選択し (見出しの A の左側、1 の上にある三角形をクリック)、[データ] > [並べ替えとフィルター - フィルター] の順に選択し、[ColumnD色によるフィルター] で黄色を選択します。

Row1 を青色で番号が付けられた最後の行までコピーし、A1別のシート (Sheet2 など) に貼り付けます。

Sheet2 で、を削除しF1、セルを上にシフトして、OK をクリックします。また、N1:T1も実行します。(ここでは、追加の目視が必要になる場合があります。)

ColumnASheet2 に新規を追加します。をに1入力しA1、を選択し2て、選択範囲の右下隅をつかみ、マウスの左ボタンを押したままを押したまま、必要なだけ下にドラッグし、を押したままにします。 A2A1:A2Ctrl

Sheet2 を選択し、データ > 並べ替えとフィルター - 並べ替えで、データにヘッダーがある、並べ替え基準ColumnA(最初の1s)、値で並べ替え、最小から最大に並べ替え、OK をチェックします。

2Sheet2 のが含まれる最低行番号ColumnAと、最も占有されている最高行番号をメモします。を削除しますColumnA。

最初のシートに戻り、黄色でハイライト表示されている行をすべて削除します。

ColumnASheet2 で、小さい方の行番号と、それより大きい番号の他のすべての行を選択し、コピーして最初のシートの下部に貼り付けます。

うまくいけば、必要なことがほとんど達成されます。達成されない場合でも、それは「正しい方向への一歩」です。確認すると、最後に占有された行は、上記の 2 つの数値の差である 2250+1 を引いた値になるはずです。

MEMBER FIRST NAME を確認するには、MEMBER # とその参照テーブルを作成し、それに基づいてコピーしたシートの MEMBER FIRST NAME を比較することをお勧めします。Curt か Curtis かは、おそらく判断の問題です。

Question 2

これは別の可能なアプローチです。それは 3 つの条件に依存します。

重複フィールドと非重複フィールドを区別するために、一意の識別子が必要です。この場合、フィールド MEMBER# がその役割を果たします。他のインスタンスでは、識別子は複数のフィールドの値の組み合わせとして構築されることがあります。この ID は、単一のフィールドの値、または複数のフィールドの値の複合である可能性があります。
MEMBER# の重複は 2 つまで、つまり 3 つ以上の「重複」レコードは不可。
行は MEMBER# 識別子に基づいてソートされます。

アイデアは、既存のテーブルの右側に、数式を使用して 2 つの重複行間で共有されている部分的なデータを 1 つの行に統合し、1 つの行を埋めて 1 つの行を空白のままにする変換されたテーブルを作成することです。

これが完了したら、結果テーブルにフィルターを適用して空白行を除外し、入力された行を別の場所にコピーすることができます。

以下に示すように、列 A にフラグフィールド「DUP」を追加しました。列 C の MEMBER# が前の行の MEMBER# と等しい場合は 1 になり、そうでない場合は 0 になります。サンプルデータで重複する MEMBER# を持つ 2 セットの行は黄色で強調表示されています。

「DUP」フラグフィールドが追加されたデータセット

数式の結果テーブルは次のようになります。予想どおり、2 つのレコード間で共有されていた補足情報は、レコードの 1 つに集められ、もう 1 つのレコードは二重ダッシュ ("--") で埋められています。(サンプルデータ内の重複行の 2 セットは、テーブル内で濃い青で強調表示されています。)

テーブルの最初の 2 行を見ると、MEMBER# 550061 の重複バージョンが含まれていますが、MEMBER_FIRST_NAME 列の行 4 の 2 番目の「Gene」が「--」に置き換えられています。行 3 の以前は空白だった ENROLLMENT_DATE は、行 4 から上に移動して 3/2/2013 が入力されています。2 番目の DRAWING_ENTRIES フィールド (元のテーブルでは列 M、新しいテーブルでは列 AS) の N/A 値は空白に置き換えられています。

重複行にフラグが付けられ、空になっている変換されたテーブル

残っているのは、フィルターを適用し、DUP 列を基準列として使用し、DUP が 0 である行のみを選択して、結果を新しい場所にコピーすることだけです。

フィルタリングされたデータセット

重複を統合するために使用される数式は、構造が基本的に同じであるため、1 つを詳しく調べることは理にかなっています。以下は、表のセル AH3 の MEMBER_FIRST_NAME 列の最初の数式です (結果表の最初の行の数式の完全なセットをこの投稿の最後に含めています)。

=IF($A3=1,                               If this is row 2 of a DUP set,
  "--",                                    Set value of the result cell to "--"
                                         Otherwise it's a row 1 (maybe a dup, maybe not)
  IF($A4=0,                                Is the following row its dup?
    IF(IFERROR(B3="",FALSE),"",B3),          No, set result to the value on this row 
    IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)), Yes, but is this row's value blank or error?
      IF(IFERROR(B4="",FALSE),"",B4),          Yes, use the value from the following row
      IF(IFERROR(B3="",FALSE),"",B3))))        No, use the value from this row

コードに関する追加のコメント:IFERROR(<cell address>="",FALSE)一部の行の N/A エラー値を適切に除外するには、やや回りくどい表現が必要です。

結果テーブルの最初の行のコード

DUP         =IF(D3=D2,1,0)
FNAME       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(B3="",FALSE),"",B3),IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)),IF(IFERROR(B4="",FALSE),"",B4),IF(IFERROR(B3="",FALSE),"",B3))))
LNAME       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(C3="",FALSE),"",C3),IF(OR(IFERROR(C3="",FALSE),ISERROR(C3)),IF(IFERROR(C4="",FALSE),"",C4),IF(IFERROR(C3="",FALSE),"",C3))))
MEMBER#     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(D3="",FALSE),"",D3),IF(OR(IFERROR(D3="",FALSE),ISERROR(D3)),IF(IFERROR(D4="",FALSE),"",D4),IF(IFERROR(D3="",FALSE),"",D3))))
ENTRY DT    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(E3="",FALSE),"",E3),IF(OR(IFERROR(E3="",FALSE),ISERROR(E3)),IF(IFERROR(E4="",FALSE),"",E4),IF(IFERROR(E3="",FALSE),"",E3))))
ENROL_DT    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(F3="",FALSE),"",F3),IF(OR(IFERROR(F3="",FALSE),ISERROR(F3)),IF(IFERROR(F4="",FALSE),"",F4),IF(IFERROR(F3="",FALSE),"",F3))))
MVP_PTS     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(G3="",FALSE),"",G3),IF(OR(IFERROR(G3="",FALSE),ISERROR(G3)),IF(IFERROR(G4="",FALSE),"",G4),IF(IFERROR(G3="",FALSE),"",G3))))
ENTRIES     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(H3="",FALSE),"",H3),IF(OR(IFERROR(H3="",FALSE),ISERROR(H3)),IF(IFERROR(H4="",FALSE),"",H4),IF(IFERROR(H3="",FALSE),"",H3))))
FORM        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(I3="",FALSE),"",I3),IF(OR(IFERROR(I3="",FALSE),ISERROR(I3)),IF(IFERROR(I4="",FALSE),"",I4),IF(IFERROR(I3="",FALSE),"",I3))))
PTS_CORRECT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(J3="",FALSE),"",J3),IF(OR(IFERROR(J3="",FALSE),ISERROR(J3)),IF(IFERROR(J4="",FALSE),"",J4),IF(IFERROR(J3="",FALSE),"",J3))))
PTS_MISSED  =IF($A3=1,"--",IF($A4=0,IF(IFERROR(K3="",FALSE),"",K3),IF(OR(IFERROR(K3="",FALSE),ISERROR(K3)),IF(IFERROR(K4="",FALSE),"",K4),IF(IFERROR(K3="",FALSE),"",K3))))
FINAL_PTS   =IF($A3=1,"--",IF($A4=0,IF(IFERROR(L3="",FALSE),"",L3),IF(OR(IFERROR(L3="",FALSE),ISERROR(L3)),IF(IFERROR(L4="",FALSE),"",L4),IF(IFERROR(L3="",FALSE),"",L3))))
DR_ENTRIES  =IF($A3=1,"--",IF($A4=0,IF(IFERROR(M3="",FALSE),"",M3),IF(OR(IFERROR(M3="",FALSE),ISERROR(M3)),IF(IFERROR(M4="",FALSE),"",M4),IF(IFERROR(M3="",FALSE),"",M3))))
SP_TALON    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(N3="",FALSE),"",N3),IF(OR(IFERROR(N3="",FALSE),ISERROR(N3)),IF(IFERROR(N4="",FALSE),"",N4),IF(IFERROR(N3="",FALSE),"",N3))))
WP_TALON    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(O3="",FALSE),"",O3),IF(OR(IFERROR(O3="",FALSE),ISERROR(O3)),IF(IFERROR(O4="",FALSE),"",O4),IF(IFERROR(O3="",FALSE),"",O3))))
BD          =IF($A3=1,"--",IF($A4=0,IF(IFERROR(P3="",FALSE),"",P3),IF(OR(IFERROR(P3="",FALSE),ISERROR(P3)),IF(IFERROR(P4="",FALSE),"",P4),IF(IFERROR(P3="",FALSE),"",P3))))
DEPT        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(Q3="",FALSE),"",Q3),IF(OR(IFERROR(Q3="",FALSE),ISERROR(Q3)),IF(IFERROR(Q4="",FALSE),"",Q4),IF(IFERROR(Q3="",FALSE),"",Q3))))
EMPL        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(R3="",FALSE),"",R3),IF(OR(IFERROR(R3="",FALSE),ISERROR(R3)),IF(IFERROR(R4="",FALSE),"",R4),IF(IFERROR(R3="",FALSE),"",R3))))
NOTES       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(S3="",FALSE),"",S3),IF(OR(IFERROR(S3="",FALSE),ISERROR(S3)),IF(IFERROR(S4="",FALSE),"",S4),IF(IFERROR(S3="",FALSE),"",S3))))
DLR         =IF($A3=1,"--",IF($A4=0,IF(IFERROR(T3="",FALSE),"",T3),IF(OR(IFERROR(T3="",FALSE),ISERROR(T3)),IF(IFERROR(T4="",FALSE),"",T4),IF(IFERROR(T3="",FALSE),"",T3))))

Answer

これは別の可能なアプローチです。それは 3 つの条件に依存します。

重複フィールドと非重複フィールドを区別するために、一意の識別子が必要です。この場合、フィールド MEMBER# がその役割を果たします。他のインスタンスでは、識別子は複数のフィールドの値の組み合わせとして構築されることがあります。この ID は、単一のフィールドの値、または複数のフィールドの値の複合である可能性があります。
MEMBER# の重複は 2 つまで、つまり 3 つ以上の「重複」レコードは不可。
行は MEMBER# 識別子に基づいてソートされます。

アイデアは、既存のテーブルの右側に、数式を使用して 2 つの重複行間で共有されている部分的なデータを 1 つの行に統合し、1 つの行を埋めて 1 つの行を空白のままにする変換されたテーブルを作成することです。

これが完了したら、結果テーブルにフィルターを適用して空白行を除外し、入力された行を別の場所にコピーすることができます。

以下に示すように、列 A にフラグフィールド「DUP」を追加しました。列 C の MEMBER# が前の行の MEMBER# と等しい場合は 1 になり、そうでない場合は 0 になります。サンプルデータで重複する MEMBER# を持つ 2 セットの行は黄色で強調表示されています。

「DUP」フラグフィールドが追加されたデータセット

数式の結果テーブルは次のようになります。予想どおり、2 つのレコード間で共有されていた補足情報は、レコードの 1 つに集められ、もう 1 つのレコードは二重ダッシュ ("--") で埋められています。(サンプルデータ内の重複行の 2 セットは、テーブル内で濃い青で強調表示されています。)

テーブルの最初の 2 行を見ると、MEMBER# 550061 の重複バージョンが含まれていますが、MEMBER_FIRST_NAME 列の行 4 の 2 番目の「Gene」が「--」に置き換えられています。行 3 の以前は空白だった ENROLLMENT_DATE は、行 4 から上に移動して 3/2/2013 が入力されています。2 番目の DRAWING_ENTRIES フィールド (元のテーブルでは列 M、新しいテーブルでは列 AS) の N/A 値は空白に置き換えられています。

重複行にフラグが付けられ、空になっている変換されたテーブル

残っているのは、フィルターを適用し、DUP 列を基準列として使用し、DUP が 0 である行のみを選択して、結果を新しい場所にコピーすることだけです。

フィルタリングされたデータセット

重複を統合するために使用される数式は、構造が基本的に同じであるため、1 つを詳しく調べることは理にかなっています。以下は、表のセル AH3 の MEMBER_FIRST_NAME 列の最初の数式です (結果表の最初の行の数式の完全なセットをこの投稿の最後に含めています)。

=IF($A3=1,                               If this is row 2 of a DUP set,
  "--",                                    Set value of the result cell to "--"
                                         Otherwise it's a row 1 (maybe a dup, maybe not)
  IF($A4=0,                                Is the following row its dup?
    IF(IFERROR(B3="",FALSE),"",B3),          No, set result to the value on this row 
    IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)), Yes, but is this row's value blank or error?
      IF(IFERROR(B4="",FALSE),"",B4),          Yes, use the value from the following row
      IF(IFERROR(B3="",FALSE),"",B3))))        No, use the value from this row

コードに関する追加のコメント:IFERROR(<cell address>="",FALSE)一部の行の N/A エラー値を適切に除外するには、やや回りくどい表現が必要です。

結果テーブルの最初の行のコード

DUP         =IF(D3=D2,1,0)
FNAME       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(B3="",FALSE),"",B3),IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)),IF(IFERROR(B4="",FALSE),"",B4),IF(IFERROR(B3="",FALSE),"",B3))))
LNAME       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(C3="",FALSE),"",C3),IF(OR(IFERROR(C3="",FALSE),ISERROR(C3)),IF(IFERROR(C4="",FALSE),"",C4),IF(IFERROR(C3="",FALSE),"",C3))))
MEMBER#     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(D3="",FALSE),"",D3),IF(OR(IFERROR(D3="",FALSE),ISERROR(D3)),IF(IFERROR(D4="",FALSE),"",D4),IF(IFERROR(D3="",FALSE),"",D3))))
ENTRY DT    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(E3="",FALSE),"",E3),IF(OR(IFERROR(E3="",FALSE),ISERROR(E3)),IF(IFERROR(E4="",FALSE),"",E4),IF(IFERROR(E3="",FALSE),"",E3))))
ENROL_DT    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(F3="",FALSE),"",F3),IF(OR(IFERROR(F3="",FALSE),ISERROR(F3)),IF(IFERROR(F4="",FALSE),"",F4),IF(IFERROR(F3="",FALSE),"",F3))))
MVP_PTS     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(G3="",FALSE),"",G3),IF(OR(IFERROR(G3="",FALSE),ISERROR(G3)),IF(IFERROR(G4="",FALSE),"",G4),IF(IFERROR(G3="",FALSE),"",G3))))
ENTRIES     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(H3="",FALSE),"",H3),IF(OR(IFERROR(H3="",FALSE),ISERROR(H3)),IF(IFERROR(H4="",FALSE),"",H4),IF(IFERROR(H3="",FALSE),"",H3))))
FORM        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(I3="",FALSE),"",I3),IF(OR(IFERROR(I3="",FALSE),ISERROR(I3)),IF(IFERROR(I4="",FALSE),"",I4),IF(IFERROR(I3="",FALSE),"",I3))))
PTS_CORRECT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(J3="",FALSE),"",J3),IF(OR(IFERROR(J3="",FALSE),ISERROR(J3)),IF(IFERROR(J4="",FALSE),"",J4),IF(IFERROR(J3="",FALSE),"",J3))))
PTS_MISSED  =IF($A3=1,"--",IF($A4=0,IF(IFERROR(K3="",FALSE),"",K3),IF(OR(IFERROR(K3="",FALSE),ISERROR(K3)),IF(IFERROR(K4="",FALSE),"",K4),IF(IFERROR(K3="",FALSE),"",K3))))
FINAL_PTS   =IF($A3=1,"--",IF($A4=0,IF(IFERROR(L3="",FALSE),"",L3),IF(OR(IFERROR(L3="",FALSE),ISERROR(L3)),IF(IFERROR(L4="",FALSE),"",L4),IF(IFERROR(L3="",FALSE),"",L3))))
DR_ENTRIES  =IF($A3=1,"--",IF($A4=0,IF(IFERROR(M3="",FALSE),"",M3),IF(OR(IFERROR(M3="",FALSE),ISERROR(M3)),IF(IFERROR(M4="",FALSE),"",M4),IF(IFERROR(M3="",FALSE),"",M3))))
SP_TALON    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(N3="",FALSE),"",N3),IF(OR(IFERROR(N3="",FALSE),ISERROR(N3)),IF(IFERROR(N4="",FALSE),"",N4),IF(IFERROR(N3="",FALSE),"",N3))))
WP_TALON    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(O3="",FALSE),"",O3),IF(OR(IFERROR(O3="",FALSE),ISERROR(O3)),IF(IFERROR(O4="",FALSE),"",O4),IF(IFERROR(O3="",FALSE),"",O3))))
BD          =IF($A3=1,"--",IF($A4=0,IF(IFERROR(P3="",FALSE),"",P3),IF(OR(IFERROR(P3="",FALSE),ISERROR(P3)),IF(IFERROR(P4="",FALSE),"",P4),IF(IFERROR(P3="",FALSE),"",P3))))
DEPT        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(Q3="",FALSE),"",Q3),IF(OR(IFERROR(Q3="",FALSE),ISERROR(Q3)),IF(IFERROR(Q4="",FALSE),"",Q4),IF(IFERROR(Q3="",FALSE),"",Q3))))
EMPL        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(R3="",FALSE),"",R3),IF(OR(IFERROR(R3="",FALSE),ISERROR(R3)),IF(IFERROR(R4="",FALSE),"",R4),IF(IFERROR(R3="",FALSE),"",R3))))
NOTES       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(S3="",FALSE),"",S3),IF(OR(IFERROR(S3="",FALSE),ISERROR(S3)),IF(IFERROR(S4="",FALSE),"",S4),IF(IFERROR(S3="",FALSE),"",S3))))
DLR         =IF($A3=1,"--",IF($A4=0,IF(IFERROR(T3="",FALSE),"",T3),IF(OR(IFERROR(T3="",FALSE),ISERROR(T3)),IF(IFERROR(T4="",FALSE),"",T4),IF(IFERROR(T3="",FALSE),"",T3))))

複数の行の値を 1 つの行に結合する方法

答え1

答え2

結果テーブルの最初の行のコード

関連情報