並び替え

質問

宜しくお願いします。写真の様に、B列からM列O列からY列まで、N列を境に2種類のデータがあり、F列とS列に共通のcodeがあるのですが、並びがバラバラで行で比較出来ないのです。各codeのデータが各列記載されてるのですが、S、F列を端に持って言ってブイルックでやって元に戻すとか、ブイルックとインデックスで、2回やるのも時間がかかってしまうので、マクロで１回で出来ないでしょうか？助けてください

回答No.7

bunjii

回答No.2の補足に「F列とS列はF列の方が若干コード数が多いのでそれぞれ並び替えすると横並びに見れなくなるのでS列に無くF列にあるコード他データは下の方に集めるとかにしたいのです。」とありますが、B列からM列までの並び順がF列をキーに昇順でソートされているように見えます。
F列のコードを対象にO列からY列の並びを合わせる方法で良ければ未使用列（例えばAB列からAL列へ抽出することはExcelに標準で組み込まれた関数のみで可能です。（O列からY列をそのままの位置で関数でのソートは不可能なので未使用の列へ数式を組み込んで目的の配列を抽出します）
貼付画像はExcel 2013での検証結果です。
AB1セルに次の数式を設定してAL1までオートフィルコピーし、そのまま下へ20行目までオートフィルコピーしたものです。
=IFERROR(INDEX($O$1:$Y$20,SMALL(INDEX(($S$1:$S$20=$F1)*ROW(AB$1:AB$20)+($S$1:$S$20<>$F1)*ROW(AB$21),0,0),COUNTIF($F$1:$F1,$F1)),COLUMNS($AB1:AB1)),"")

F列に有ってS列に無いコードは4、6、10、13を模擬データとして追加してあります。
尚、VBAでも可能ですが失敗すると元データを壊すことも考えられますのでテストする場合はファイル（ブック）をコピーして実行するようにしてください。
参考のためにコードを貼付します。（結果の画像は添付しません）
Sub sort()
Dim myArray
myArray = Range("O1:Y20")
Dim i, j, k, l, m(), n
For i = 1 To 20
For j = 1 To 11
myArray(i, j) = Null
Next j
Next i
For i = 1 To 20
j = 0
k = 0
ReDim m(k)
Do
j = j + 1
If Cells(j, 19) = "" Then Exit Do
If Cells(i, 6) = Cells(j, 19) Then
k = k + 1
ReDim Preserve m(k)
m(k) = j
End If
Loop Until j = 21
If k > 0 Then
For n = 1 To k
For l = 15 To 25
myArray(i, l - 14) = Cells(m(n), l)
Next l
If k > 1 Then i = i + 1
Next n
End If
Next i
Range("o1:y20") = myArray
End Sub

回答No.6

bunjii

回答No.5の追加です。
回答No.2の補足で「F列とS列はF列の方が若干コード数が多いのでそれぞれ並び替えすると横並びに見れなくなる」と言うことですが、S列に有るコードはF列に必ず有るのですか？、無い場合はどのように対処しますか？
また、最終目的はどのような結果にしたいのですか？
VBAでセルの値を順次入れ替えたとき元の配列に戻せなくなりますがそれでも良いのですか？
結果の配列はB:Mを基準にしてF列と同じコードのS列のデータが同じ行になれば良いのでしたらINDEX関数とMATCH関数の組み合わせで数式を組めばAA列以降の未使用列へ抜き出せます。但し、F列のコードとS列のコードの重複は2件までとし、検出順位は上から順に宛がうことになります。
具体的な数式は補足説明を待って検証してからになります。
尚、当方のExcelは2013なので2007以降のバージョンが対象になります。

回答No.5

bunjii

>写真の様に、B列からM列O列からY列まで、N列を境に2種類のデータがあり、F列とS列に共通のcodeがあるのですが、並びがバラバラで行で比較出来ないのです。
N列を境に左右の列数が異なるようです。（B:Mは12列、O:Yは11列）
また、F列とS列のコード（数値？）は何れも11が2個ありますので他の要素と組み合わせないと判別できません。
比較する対象はB列とO列、C列とP列、D列とQ列、・・・・のようにしたときM列に対しては対象がありませんがそれでよろしいですか？

>ブイルックとインデックスで、2回やるのも時間がかかってしまうマクロで１回で出来ないでしょうか？
前述のようにF列とS列の共通コードが複数の場合は区別ができませんのでVLOOKUP関数やINDEX関数で行の抽出ができません。
勿論、VBAでも同様です。
もう1度、質問の要件を整理して不足している要件を補足（追加提示）してください。

回答No.4

Nobu-W

ごく簡単な方法ですが・・・
Ｏ１のセルからＶ１６のセルを選択し、エクセルのメニュ～でホ～ムの『並び替えとフィルタ～』選択して『ユ～ザ～設定の並べ替え』選択し、出てきたパネル
で最優先されるキ～に列Ｓを選択し、順序を昇順でＯＫ返すと左側の表と同じに
並び替えられませんか？

回答No.3

tsubu-yuki

一般論ですが、
VLOOKUP関数は「キーが重複している場合」には
思う答えを返してくれないことが多いですよ。
INDEX関数もまた然り、VBAを使っても困難な要件です。
VBAを使っても、キーが重複している以上、
おそらくお望みのものは出来上がりません。

「重複しないキー」を用意できるのであれば、
INDEX関数とMATCH関数でマッチングはできるかもしれません。
「左の表にはあるけど、右の表にはない」を確認する程度なら
おそらくCOUNTIF関数で事が足ります。
Ａ１セル辺りに式
　　=COUNTIF(S:S,F1)
を仕込んで、行方向にフィル。
これでＦ列に値がＳ列に存在しなければ「０」が返ります。
存在するなら、その数を返してきます。
あとはコレの内「０」を下に持って行くのであれば降順で
並べ替えてやればお望みのものに近いものが出来るような気がします。

ＶＢＡで組むのは造作無いですが、オススメはしません。
どうしてもソレがお望みなら、
現在やっている一連の作業を「マクロの記録」してみると良いです。
記録されたものに少し手を加えるだけなら簡単ですから。

回答No.2

msMike

「ブイルックでやって元に戻す」とは、その結果、どうなって欲しいことなんですか？
マクロをご希望のようだけど、貴方自身のマクロ操作レベルは如何ほどなんでしょうか？

補足

ご回答ありがとうございます。遅れてすいません。自分が扱ってるブックは色々あり、F列とS列はF列の方が若干コード数が多いのでそれぞれ並び替えすると横並びに見れなくなるのでS列に無くF列にあるコード他データは下の方に集めるとかにしたいのです。データによっては同じなのもあるかもしれませんがそれを防ぐためにマクロで解決できないかと思いました。よろしくお願いいたします！

回答No.1

f272

B列からM列を選択して，F列をキーにして並び替え
O列からY列を選択して，S列をキーにして並び替え
とすればよいのでは？