[[20171003162705]]　『【vlookup】1つの検索値で結果が複数あるとき』（ちこ）　ページの最後に飛ぶ

『【vlookup】1つの検索値で結果が複数あるとき』（ちこ）

・win7
・Excel2010

1つの検索値に対し複数の結果が存在する場合に
結果の全てを表示（横に並べて表示）させたい
のですが、どのよにするのが一番簡単でしょうか？
初心者の為、単純なvlookupしか思いつかず…
ご教示頂けますと幸いです。
実現したい内容の簡易例は以下となります。

【元のリスト】
A列 B列
1組赤
1組青
1組赤
2組青
2組緑
2組黄
2組黄
3組赤
3組赤
4組青
5組緑
5組黄

【作成したいリスト】
A列 B列 C列 D列
1組赤青
2組青緑黄
3組赤
4組青
5組緑黄

宜しくお願い致します。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 ＞簡易例

 あくまでその例に基づいた回答です。

 ・元のリストはSheet1とする
 ・データは2行目から始まっているものとする
 ・作成したいリストのA列「1組～5組」は入力済
 ・Sheet1に作業列を使う

 ■Sheet1（作業列）

 C2 =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,A2&COUNTIF($C$1:C1,A2&"*")+1,"")

 下へコピー

 ■作成したいリスト

 B2 =IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2&COLUMN(A1),Sheet1!$C:$C,0)),"")

 右と下にコピー

 実際の表のセル位置がどうなっていようと　COLUMN(A1) を変更してはいけません。

 参考まで
（笑） 2017/10/03(火) 17:16

 作業列を使わない方法。
 元のデータがSheet1で作成するデータがSheet2とする。
 Sheet2のB1セルに
 =IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW($1:$100),""),COLUMN(A1))),"")
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK)
 その後、右および下へフィルコピーで。
（ねむねむ） 2017/10/03(火) 17:22

 なおセル範囲が異なる場合もCOLUMN(A1)のA1部分は変えないように。
（ねむねむ） 2017/10/03(火) 17:22

 重複は除外だと思うんですけど、違うんですかね？

 例は除外されてますけど。
（笑） 2017/10/03(火) 17:31

 笑さん、ご指摘ありがとう。
 ちこさん、私の回答は重複もそのまま抜き出しているので無視してくれ。
（ねむねむ） 2017/10/03(火) 17:34

 Sub main()
    Dim sht As Worksheet, r As Range, c As Range, tempr As Range
    Set sht = ActiveSheet
    Sheets.Add after:=ActiveSheet
    ActiveSheet.Cells.ClearContents
    For Each c In sht.Range("A:A").SpecialCells(xlCellTypeConstants)
        Set r = ActiveSheet.Range("A:A").Find(c.Value)
        If Not r Is Nothing Then
            Set tempr = r
        Else
            Set tempr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
            tempr.Value = c.Value
        End If
        Set r = ActiveSheet.Rows(tempr.Row).Find(c.Offset(, 1).Value)
        If r Is Nothing Then
            ActiveSheet.Cells(tempr.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = c.Offset(, 1).Value
        End If
    Next c
End Sub
（mm） 2017/10/03(火) 18:30

(笑）さん、むねむねさん、（mm）さん
回答ありがとうございます。

＞　重複は除外だと思うんですけど、違うんですかね？

言葉足らずで失礼しました。
はい、重複は除外です。

時間がなくてまだ試せておらず・・・明日、チャレンジします。

（ちこ） 2017/10/03(火) 19:38

 ちなみに作業列を使わない場合の一例

 B2 =IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF((Sheet1!$A$1:$A$100=$A2)*(MATCH(Sheet1!$A$1:$A$100&Sheet1!$B$1:$B$100,Sheet1!$A$1:$A$100&Sheet1!$B$1:$B$100,0)=ROW($A$1:$A$100)),ROW($A$1:$A$100),""),COLUMN(A1))),"")

 Ctrl＋Shift＋Enter（CtrlキーとShiftキーを押しながらEnter）で確定
 右と下にコピー

 データが2行目から始まっていても $A$1:$A$100 のように数式内の範囲は1行目からにする。

 参考まで
（笑） 2017/10/03(火) 23:16

遅くなりましたが・・・

今回は、シンプルそう（に見えた）な（笑）さんのご提案を利用させていただきました。
自分が不慣れで少々梃子摺りましたが何とか希望の形のものを作成できました。

（余談）何しろ本チャンのデータはデータ量が多いのにＰＣスペックが低く
　　　　処理中に度々応答なしになったりしてイライラしましたがwww
　　　　ちょっとずつコピーしても結局再計算すると最初からなんですよね・・・
　　　　ドロドラしたセルだけ関数計算してくれればいいのに・・・

アドバイスくださった皆様ありがとうございました。
またお世話になることもあるかと思いますが、よろしくお願いします。

（ちこ） 2017/10/05(木) 15:04

　＞　　　　ちょっとずつコピーしても結局再計算すると最初からなんですよね・・・
　＞　　　　ドロドラしたセルだけ関数計算してくれればいいのに・・・
そういう時は、数式の再計算を手動にすると快適になります。
そのかわり、計算間違いじゃね？って言う状態が保持されますので、
最後にF9で再計算を忘れずに。
（まっつわん） 2017/10/05(木) 15:10

コメント返信：

[ 一覧(最新更新順) ]

[[20171003162705]] 『【vlookup】1つの検索値で結果が複数あるとき』（ちこ） ページの最後に飛ぶ

[[20171003162705]]　『【vlookup】1つの検索値で結果が複数あるとき』（ちこ）　ページの最後に飛ぶ