ヒント:他の言語はGoogle翻訳されています。 訪問することができます English このリンクのバージョン。
ログイン
x
or
x
x
会員登録
x

or

別の列の一意の値に基づいてセルをある列に転記する方法

次の結果を得るために、ある列のセルを別の列の一意の値に基づいて水平行に転置したい場合、2つの列を含む一連のデータがあるとします。 Excelでこの問題を解決する良いアイデアはありますか?

doc一意の値を転置する1

数式で一意の値に基づいて1つの列にセルを転置する

VBAコードで一意の値に基づいてセルを1つの列に移す

Kutools for Excelで一意の値に基づいてセルを1列に変換する



次の配列式を使用すると、一意の値を抽出し、対応するデータを水平行に変換できます。次のようにしてください。

1。 この配列の式を入力します: = INDEX($ A $ 2:$ A $ 16、MATCH(0、COUNTIF($ D $ 1:$ D1、$ A $ 2:$ A $ 16)、0)) 例えば空白のセルD2に入れ、 Shift + Ctrl + Enter キーを合わせて正しい結果を得るには、スクリーンショットを参照してください:

doc一意の値を転置する2

上記の式において、 A2:A16 からユニークな値を列挙する列です。 D1 この数式セルの上のセルです。

2。 次に、塗りつぶしハンドルをセルにドラッグしてすべての一意の値を抽出します。スクリーンショットを参照してください。

doc一意の値を転置する3

3。 そして、この数式をセルE2に入力してください: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0)、覚えておいてください Shift + Ctrl + Enter 結果を得るためのキーは、スクリーンショットを参照してください:

doc一意の値を転置する4

上記の式において、 B2:B16 転置する列データです。 A2:A16 に基づいて値を転置したい列です。 D2 ステップ1で抽出した一意の値が含まれています。

4. 0が表示されるまで、転置されたデータをリストするセルの右側に塗りつぶしハンドルをドラッグします。スクリーンショットを参照してください:

doc一意の値を転置する5

5。 次に、塗りつぶしハンドルをセルの範囲までドラッグして、次に示すスクリーンショットのように転置データを取得します。

doc一意の値を転置する6


あなたが理解できるように数式が複雑な場合があります。ここでは、次のVBAコードを実行して必要な結果を得ることができます。

1。 押さえつける Alt + F11 キーを押して アプリケーション用Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードを モジュール 窓。

VBAコード:別の列の一意の値に基づいて、ある列のセルを転置します。

Sub transposeunique()
'updateby Extendoffice 20151207
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. 次に、 F5 キーを押してこのコードを実行し、使用するデータ範囲を選択するよう促すプロンプトボックスが表示されます。スクリーンショットを参照してください:

doc一意の値を転置する7

4。 そして、 OK ボタンを押すと、別のプロンプトボックスが表示され、セルを選択して結果を表示するよう促します(スクリーンショットを参照)。

doc一意の値を転置する8

6に設定します。 OK をクリックします。 OK ボタンをクリックし、列Bのデータは列Aの一意の値に基づいて転置されました。スクリーンショットを参照してください:

doc一意の値を転置する9


あなたが持っている場合 Kutools for Excel高度な行の結合 そして、 セルを分割する 数式やコードなしですぐにこの作業を完了することができます。

Kutools for Excel : 300便利なExcelアドイン以上で、60日以内に自由に試してみてください.

Excel用Kutoolsをインストールしたら、次のようにしてください:

1。 使用するデータ範囲を選択します。 (元のデータを保持したい場合は、最初にデータをコピーして別の場所に貼り付けてください)。

2。 次に、をクリックします クツール > コンテンツ > 高度な行の結合、スクリーンショットを見る:

doc一意の値を転置する10

3。 Cで列に基づく行のombine ダイアログボックスで、以下の操作を行ってください。

(1。)に基づいてデータを転置する列名をクリックし、 主キー;

(2。)転置したい別の列をクリックし、 組み合わせる カンマ、セミコロンなどの結合されたデータを区切る1つのセパレータを選択します。

doc一意の値を転置する11

4。 次に、をクリックします Ok ボタンをクリックすると、列Bのデータが列Aに基づいて1つのセルにまとめられます。スクリーンショットを参照してください:

doc一意の値を転置する12

5。 結合したセルを選択し、をクリックします。 クツール > テキスト > セルを分割する、スクリーンショットを見る:

doc一意の値を転置する13

6。 の中に セルを分割する ダイアログボックスで 列に分割タイプ オプションを選択し、組み合わせたデータを区切る区切り文字を選択します。スクリーンショットを参照してください:

doc一意の値を転置する14 14

7。 次に、をクリックします Ok ボタンをクリックし、分割された結果をポップアウトしたダイアログボックスに入れるセルを選択します。スクリーンショットを参照してください:

doc一意の値を転置する15

8に設定します。 OK をクリックします。 OKあなたが必要とする結果を得ることができます。 スクリーンショットを見る:

doc一意の値を転置する16

Excel用のKutoolsを今すぐダウンロードして無料でダウンロードできます!


Kutools for Excel200便利なExcelアドイン以上で、60日に制限なく試してみることができます。 今すぐダウンロードして無料トライアル!


Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート そして、以前に使用したもの; セルを暗号化する パスワード付き メーリングリストの作成 そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストや数式を簡単に編集する) レイアウトを読む (多数のセルを簡単に読んで編集できます)。 フィルター範囲に貼り付ける...
  • セル/行/列を結合 データを失うことなく; セルコンテンツの分割。 重複する行/列を結合する...重複セルの防止。 範囲の比較...
  • 重複または一意を選択します空白行を選択 (すべてのセルは空です)。 スーパー検索とファジー検索 多くのワークブックで。 ランダム選択
  • 完全コピー 式の参照を変更せずに複数のセル。 参照を自動作成 複数のシートに 箇条書きを挿入、チェックボックスなど
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷 セルのコンテンツとコメント間の変換...
  • スーパーフィルター (保存して他のシートにフィルタ方式を適用する)。 高度な並べ替え 月/週/日、頻度などによる。 特殊フィルター 太字、斜体で...
  • ワークブックとワークシートを組み合わせる; キー列に基づいて表をマージします。 データを複数のシートに分割する; xls、xlsx、およびPDFのバッチ変換...
  • 300を超える強力な機能。 Office / Excel 2007-2019および365をサポートします。 すべての言語をサポートします。 企業または組織に簡単に展開できます。 フル機能の30日間の無料トライアル。
KTEタブ201905

OfficeタブはOfficeにタブ付きインターフェイスを提供し、作業をより簡単にします

  • Word、Excel、PowerPointでタブ付き編集と読み取りを有効にする、出版社、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を50%向上させ、毎日数百回のマウスクリックを削減します。
オフィシタブ底
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Eduardo Sequeira · 3 months ago
    I have a data set which has multiple IDs in column A, and has connected data in column B. I used the above formula and altered it a bit so that I am transposing the cells in the column B into a row based on the unique ID tied to it in column A. The formula used to identify the unique IDs is: =INDEX($A$2:$A$13409, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$13409), 0)). The formula used to do the transposing is: =IFERROR(INDEX($B$2:$B$13409, MATCH(0, IF($A$2:$A$13409<>$D2, 1, 0)+COUNTIF($D2:D2,$B$2:$B$13409), 0)), "N/A"). Both given in the article, only slightly altered.

    The issue is my data set in column B has duplicates, sometimes appearing one after another, and I need all of the values in the column to be presented in the rows.

    The image attached is what I would like the table to show (this is a small sample size, the true dataset has over 13,000 entries). What is happening now is when a repeat value is encountered, it will not count it.
    i.e. Row 9 for ID 11980 now only shows 0 -31.79 -0.19 -0.74 N/A N/A .... when what I need it to show instead is 0 0 -31.79 -0.19 -0.74 0 0 N/A N/A ....

    Is there a way to work around this issue and resolve it?

    Thank you in advance!
  • To post as a guest, your comment is unpublished.
    arielle · 4 months ago
    I have a data set in Columns A (Unique ID) - E. Each row has data based on the ID#, there are multiple rows for each ID# but I want one row per ID# with all of the other data in columns (it would be 5 columns long minimum and 25 maximum depending on how many each unique ID has). I found a code but it only works for two columns. I had to concatenate the four columns (not including ID) then delimit after running the macro (lot of work). For 15,000 rows of data this is extra time consuming. Is there an endless column macro that would work? Thanks in advance everyone for your help!
    ID CODE ST CODE# DATE
  • To post as a guest, your comment is unpublished.
    martha Bright · 4 months ago
    The macro did not work. It just copied the contents in cell A1.
  • To post as a guest, your comment is unpublished.
    Vinod · 4 months ago
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) worked for me to transpose the unique values of A column into a new column BUT...is there a way to get the all the values in B column to be transposed as given below:

    Product Order Date Product Order Order Order Order Order Order Order
    KTE 100 3/3/2019 KTE 100 100 100 200 100 150 100
    KTO 150 3/3/2019 KTO 150 100 200 100 150 200
    KTE 100 3/4/2019 BOT 150 100 200 150 100 200
    KTO 100 3/4/2019 COD 200 150 100 150
    KTO 200 3/5/2019
    KTE 100 3/5/2019
    BOT 150 3/5/2019
    BOT 100 3/6/2019
    KTO 100 3/6/2019
    KTE 200 3/6/2019
    BOT 200 3/7/2019
    COD 200 3/7/2019
    KTE 100 3/7/2019
    KTO 150 3/7/2019
    BOT 150 3/8/2019
    KTE 150 3/8/2019
    COD 150 3/8/2019
    BOT 100 3/9/2019
    BOT 200 3/10/2019
    COD 100 3/10/2019
    KTO 200 3/10/2019
    COD 150 3/11/2019
    KTE 100 3/11/2019
  • To post as a guest, your comment is unpublished.
    Sean Vizcarrondo · 7 months ago
    So I am working for a company. We have columns for info such as Last name, first name, rank, section, phone number, address. Is there a way I can use a similar formula to transpose the entire row of info to a column by names?
  • To post as a guest, your comment is unpublished.
    kumar · 8 months ago
    Hi can we add each row and give the output in one column, with the above functionality.
  • To post as a guest, your comment is unpublished.
    Raj · 8 months ago
    Need to get the same out put but for predefined columns to be selected would be ($A,$B) and need the output column Position on $D$1.
    If any one have idea's that would be a great help!!!!
  • To post as a guest, your comment is unpublished.
    Kate · 11 months ago
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) worked for me to transpose the unique values of a column into a new column BUT...is there a way to ad in a sort function so that the new column created is transposed in ascending order?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Prial · 1 years ago
    Same as Dave, I need to do the exactly opposite of this. Table 2 to transpose to Table 1. Input Table 2, Output Table 1.
  • To post as a guest, your comment is unpublished.
    Dave Babler · 1 years ago
    I need to do exactly the opposite of this. I have many many columns associated with a row id and I want to paste them into two columns
    for example I have
    rowid, value, value1, value2, value3, value4, value..225
    100, Dolphin, 255, 9--, sarah, jameson, ....
    179, Router, flood, jason, 89, nose



    I want it to look like this
    100, Dolphin
    100, 255
    100, 9--
    100, sarah
    100, jaemeson
    179, Router
    179, flood
    179, jason
    179, 89
    179, nose
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Dave,
      To solve your problem, please use the below VBA code: (Note: When you select the data range that you want to transpose, please exclude the header row.)

      Sub TransposeUnique_2()
      Dim xLRow, xLCount As Long
      Dim xRg As Range
      Dim xOutRg As Range
      Dim xObjRRg As Range
      Dim xTxt As String
      Dim xCount As Long
      Dim xVRg As Range
      On Error Resume Next
      xTxt = ActiveWindow.RangeSelection.Address
      Set xRg = Application.InputBox("please select data range:", "Kutools for Excel", xTxt, , , , , 8)
      Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
      If xRg Is Nothing Then Exit Sub
      If (xRg.Rows.count < 2) Or _
      (xRg.Areas.count > 1) Then
      MsgBox "Invalid selection", , "Kutools for Excel"
      Exit Sub
      End If
      Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
      If xOutRg Is Nothing Then Exit Sub
      Application.ScreenUpdating = False
      xLCount = xRg.Columns.count
      For xLRow = 1 To xRg.Rows.count
      Set xObjRRg = Range(xRg.Cells(xLRow, 2), xRg.Cells(xLRow, xLCount)).SpecialCells(xlCellTypeConstants)
      xObjRRg.Copy
      xOutRg.Offset(, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
      Application.CutCopyMode = False
      Range(Cells(xOutRg.Row, xOutRg.Column), Cells(xOutRg.Row + xObjRRg.count - 1, xOutRg.Column)).Value = xRg.Cells(xLRow, 1).Value
      Set xOutRg = xOutRg.Offset(RowOffset:=xObjRRg.count)
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Anna · 11 months ago
        Thank you, it works perfectly, you saved me 2 days! :)
  • To post as a guest, your comment is unpublished.
    Gino · 1 years ago
    With the formula below:

    =IFERROR(INDEX($B$2:$B$45, MATCH(0, COUNTIF($D2:D2,$B$2:$B$45)+IF($A$2:$A$10<>$D2, 1, 0), 0)), 0)

    How can I transpose the data using approximate matches? Say, I want to extract all the values from Column B that match the first 9 characters / digits from Column A? Column B has 11 characters while A only 9. thank you!
  • To post as a guest, your comment is unpublished.
    Guest · 1 years ago
    i want to transpose duplicate values too (all values - unique + duplicate) and not just unique values. Can you give the formula for that too.
  • To post as a guest, your comment is unpublished.
    Aidan · 1 years ago
    Is there a way of doing this in reverse? I.e. data in rows of varying length and so sorting it into two columns? See attached.
  • To post as a guest, your comment is unpublished.
    Didin Mathew · 1 years ago
    How to do the transpose if B column doesn't have unique values but still need those values
    KTE 100
    KTE 100
    Assuming that they are two different transaction
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi,Didin,

      Can you give your problem more clearly or detailed?
      You can insert an example screenshot for your problem.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Sanjeev Chidambaram · 1 years ago
    I just want to do the opposite. Like i have the end result already, and i want to achieve the first step.
    • To post as a guest, your comment is unpublished.
      Chris · 1 years ago
      I am looking for the same thing
      • To post as a guest, your comment is unpublished.
        Juan Carlos · 1 years ago
        Did you find any solution for the opposite scenario? Thanks!
        • To post as a guest, your comment is unpublished.
          Prial · 1 years ago
          I want to do the opposite as well. Any solution you got gents?
          • To post as a guest, your comment is unpublished.
            skyyang · 1 years ago
            Hello, guys,
            To get the opposite result based on the example of this article, you can apply the following VBA code: (Note:When selecting the data range that you want to transpose, please exclude the header row)

            Sub TransposeUnique_2()
            Dim xLRow, xLCount As Long
            Dim xRg As Range
            Dim xOutRg As Range
            Dim xObjRRg As Range
            Dim xTxt As String
            Dim xCount As Long
            Dim xVRg As Range
            On Error Resume Next
            xTxt = ActiveWindow.RangeSelection.Address
            Set xRg = Application.InputBox("please select data range:", "Kutools for Excel", xTxt, , , , , 8)
            Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
            If xRg Is Nothing Then Exit Sub
            If (xRg.Rows.count < 2) Or _
            (xRg.Areas.count > 1) Then
            MsgBox "Invalid selection", , "Kutools for Excel"
            Exit Sub
            End If
            Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
            If xOutRg Is Nothing Then Exit Sub
            Application.ScreenUpdating = False
            xLCount = xRg.Columns.count
            For xLRow = 1 To xRg.Rows.count
            Set xObjRRg = Range(xRg.Cells(xLRow, 2), xRg.Cells(xLRow, xLCount)).SpecialCells(xlCellTypeConstants)
            xObjRRg.Copy
            xOutRg.Offset(, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            Application.CutCopyMode = False
            Range(Cells(xOutRg.Row, xOutRg.Column), Cells(xOutRg.Row + xObjRRg.count - 1, xOutRg.Column)).Value = xRg.Cells(xLRow, 1).Value
            Set xOutRg = xOutRg.Offset(RowOffset:=xObjRRg.count)
            Next
            Application.ScreenUpdating = True
            End Sub
  • To post as a guest, your comment is unpublished.
    Pradeep · 2 years ago
    First step itself fails
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) gives Value Not Available error
  • To post as a guest, your comment is unpublished.
    Piyush · 2 years ago
    This was fantastic.
    I had an excel with around 2000 unique values in row A and couldn't have managed this exercise without your help.

    Many many thanks.
  • To post as a guest, your comment is unpublished.
    Tim · 2 years ago
    How would I go in the opposite direction? From multiple columns into a single column? Thanks in advance!

    Tim