[[20210426221532]]　『VLOOKUP関数で重複は省いて2番目以降のデータも抽』（右近）　ページの最後に飛ぶ

『VLOOKUP関数で重複は省いて2番目以降のデータも抽出したい』（右近）

お尋ねいたします。
以下のような表があります。
A列の値を元に重複は省いて、VLOOKUP関数で2番目以降のデータもD～H列に抽出したいと思います。
以下の関数まではどうにか設定できたのですが、重複を省く、ことが出来ません。
ご教授のほどよろしくお願いいたします。
D2=VLOOKUP(C2,INDEX(A2:A51,MATCH(C2,A2:A51,0)):B51,2,)
E2=VLOOKUP(C2,INDEX(A2:A51,MATCH(C2,A2:A51,0)+1):B51,2,)
F2=VLOOKUP(C2,INDEX(A2:A51,MATCH(C2,A2:A51,0)+2):B51,2,)
G2=VLOOKUP(C2,INDEX(A2:A51,MATCH(C2,A2:A51,0)+3):B51,2,)
H2=VLOOKUP(C2,INDEX(A2:A51,MATCH(C2,A2:A51,0)+4):B51,2,)

  A    B       C      D      E      F      G      H
2 11   a地区   11     a地区  c地区  d地区  e地区
3 11   a地区
4 11   c地区
5 11   d地区
6 11   e地区
7 12
8 12
・
・
・
51

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 D2 =IF($C2="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$51)/(MATCH($C2&$B$2:$B$51,$A$2:$A$51&$B$2:$B$51,0)=ROW($B$1:$B$51)),COLUMN(A1))),""))
 右コピー

 ■作業列を使ってもいいのなら･･･
 例）J列

 条件を指定するのは「C2セルだけ」という前提で

 J2 =IF(A2<>$C$2,"",IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,ROW(),""))
 51行目まで下コピー

 D2 =IFERROR(INDEX($B:$B,SMALL($J:$J,COLUMN(A1))),"")
 右コピー

 以上
（笑） 2021/04/26(月) 23:27

笑さん、ご返事が遅くなりすみません。
作業列は作りたくないため、
=IF($C2="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$51)/(MATCH($C2&$B$2:$B$51,$A$2:$A$51&$B$2:$B$51,0)=ROW($B$1:$B$51)),COLUMN(A1))),""))
を設定したところ、見事に重複を省いてくれました！素晴らしいです！
ありがとうございました。
（右近） 2021/04/27(火) 12:55

お尋ねいたします。
2021/04/26(月) 23:27　笑さんにご教授いただいた以下の関数を、
D2=IF($C2="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$51)/(MATCH($C2&$B$2:$B$51,$A$2:$A$51&$B$2:$B$51,0)=ROW($B$1:$B$51)),COLUMN(A1))),""))
D2からR2まで右コピーさせて、
Z2セルにデータの入力規則のリストを設定(空白を無視するにチェック)し、
その場合、右コピーした結果が「0」の場合は、リストから除外するようにしたいのですが、
どうしても「0」がリストに表示されてしまいます。
入力規則に設定した関数は、以下のとおりです。どこを修正すれば「0」を除外できるでしょうか？
=IF($C2="","",OFFSET($D2,,,,IFERROR(MATCH(0,$D2:$R2*1,0)-1,15)))
「0」を除外という意味は、例えば、D2からF2までの結果が「0」以外の値ならリストは3つのみ。D2からH2までの結果が「0」以外の値ならリストは5つのみ。表示させたいというものです。
ご教授の程よろしくお願いいたします。
（右近） 2021/06/23(水) 07:32

 INDEXの戻り値（B列の値）が例示通りすべて文字列なら
 入力規則の元の値
 =OFFSET(D2,0,0,1,COUNTIF(D2:R2,"?*"))

 ■ところで、その1
 なんで「0」が表示されるんですかね？
 A列に番号が入っているのに、B列が空白の場合があるってこと？

 A列の同じ番号内で、B列の途中が空白になることはないのなら
 つまり ↓ なことはないのなら

	A	B	
2	11	a地区	
3	11	a地区	
4	11		←A列の同じ番号内で途中に空白
5	11	b地区	
6

 1）数式を入れたセルの表示形式をユーザー定義で　#　にする
 2）または、数式の最後の方
 　（前略）…,COLUMN(A1))),""))
 　　　　　　　　↓
 　（前略）…,COLUMN(A1)))&"",""))
 　                       ~~~
 　　波線部を追加

 これで「0」は表示されません。
 どちらの方法で「0」を非表示にしても、冒頭の入力規則の式はそのままでオッケー
 B列が文字列なら、ですけど･･･

 ■ところで、その2
　↓ はどうなったんですか？
[[20210428214402]]『連番を振る関数について』（右近）

 以上
（笑） 2021/06/23(水) 17:13

笑いさん、ありがとうございます。
まず最初に、以下を入力規則に設定したところ、見事にリストの0も空白行もなくなりました！！すごいです！！ありがとうございます。
=OFFSET(D2,0,0,1,COUNTIF(D2:R2,"?*"))
その場合は、その1の設定は必要ないでしょうか？

その2については、大変失礼しました。
笑さんからご教授いただいたように設定してみたところ、見事に連番を割り振ることが出来、日常的に仕事で使用しております。これも、ありがとうございます。

今後ともどうぞよろしくお願いいたします。
（右近） 2021/06/23(水) 21:42

コメント返信：

[ 一覧(最新更新順) ]

[[20210426221532]] 『VLOOKUP関数で重複は省いて2番目以降のデータも抽』（右近） ページの最後に飛ぶ

[[20210426221532]]　『VLOOKUP関数で重複は省いて2番目以降のデータも抽』（右近）　ページの最後に飛ぶ