未使用数字を抽出する方法

質問

このカテゴリーのエクセルの達人の方々に質問です。
どなたか回答して頂ける方がおみえでしたらよろしくお願いします。
（ちゃんとした回答ではなく、文句やクレーム等を混ぜた記載はご遠慮ください。
　私の質問が気に入らない場合は無視して頂ければ結構です。）
【質問】
　添付図のとおり、「①使う予定の数字」として、Ｎ１～ＢＤ１迄の
４３個の数字。「➁使う数字」として、Ａ１～Ｊ４までの４０個の　
　エリアに数字が入っています。①から➁を見て、「③残った数字」
　をＮ３～に並べる方法が知りたいです。

　ＶＢＡでもその他方法でも構いません。
【注意事項】
　　・「①使う予定の数字」に入る数字は最大４３個で、
重複無しです。
　　・「➁使う数字」に入る数字は最大４０個で、重複無しです。
　　・「③残った数字」は、左から昇順に並んで欲しい。
　　・「①使う予定の数字」、「➁使う数字」、「③残った数字」
は、説明のためにつけているだけです。
　　・使用するエクセルは２０２１です。

以上、よろしくお願いします。

回答No.3

SI299792 · ベストアンサー

使う予定の数字に空白がないなら
=FILTER(N1:BD1,COUNTIF(A1:J4,N1:BD1)=0)

使う予定の数字に空白がありうるなら
=FILTER(N1:BD1,N1:BD1*(COUNTIF(A1:J4,N1:BD1)=0))

これだけのことがこれだけの関数でできる。
2021は便利ですね。

お礼

いつもお世話になっております。 エクセル２０２１だとこんなにスマートな記述で実現できるのですね。 早速やってみて得たい結果になりました。 ”空白がある”という前提も対応いただきありがとうございました。

Powered by GRATICA

回答No.4

msMike

添付図参照(Excel 2019)
事前に。下段図のように[参照範囲]に応じた[名前]を作成しておくこと。
１．式 =COUNTIF(Range,N1) を入力したセル N2 を右方にズズーッ
￣￣とオートフィル
２．式 =IFERROR(SMALL(IF(使用数=0,使用予定数),COLUMN(A1)),"")
￣￣を入力したセル N3 を右方にズズーッとオートフィル
【お断わり】ステップ２の式は必ず配列(CSE)数式として入力のこと

お礼

回答頂きありがとうございます。 質問する前に、ＣＯＵＮＴＩＦで考えてましたが上手くいかずに質問することにしました。それを使用してのご回答いただきありがとうございました。同じ結果になりました。

Powered by GRATICA

回答No.2

kkkkkm

回答No.1の記載忘れです。

N1から右にはシートの右端まで「使う予定の数字」以外のデータは無いものと考えています。
N3から右も同様にシートの右端までデータが無いものと考えています。

「使う数字」は数に関係なく質問の画像にある黒枠の範囲にA1から隙間なく記載されている前提で、黒枠の外周(K列と5行目)には数字は無いものと考えて

Set fRng = Range("A1").CurrentRegion

としています。

もし「使う数字」の範囲が上記の前提にあわない可能性がありましたら

Set fRng = Range("A1:J4")

として最大範囲に設定してください。

お礼

いつもお世話になっております。 一番に回答頂きありがとうございます。 空白が無いことが前提なんですね。 教えていただいたＶＢＡで実行結果問題なくでました。ありがとうございました。

Powered by GRATICA

回答No.1

kkkkkm

VBAでしたら以下で試してみてください

Sub Test()
Dim i As Long, j As Long, LastCol As Long
Dim mRng As Range, fRng As Range

Set fRng = Range("A1").CurrentRegion
j = Columns("N").Column
For i = Columns("N").Column To Cells(1, Columns.Count).End(xlToLeft).Column
Set mRng = fRng.Find(What:=Cells(1, i).Value, LookIn:=xlValues, LookAt:=xlWhole)
If mRng Is Nothing Then
Cells(3, j).Value = Cells(1, i).Value
j = j + 1
End If
Next

LastCol = Cells(3, Columns.Count).End(xlToLeft).Column
Range(Cells(3, "N"), Cells(3, LastCol)).Sort _
Key1:=Range("N3"), Order1:=xlAscending, Orientation:=xlSortRows

End Sub