[[20160830183555]]　『検索番号別にデータの並び替え(関数希望)』（うさ）　ページの最後に飛ぶ

『検索番号別にデータの並び替え(関数希望)』（うさ）

関数でA列の検索番号別に、データを整理したいのですがなかなかいい案が見つかりません。お力貸して下さい。

元データ
シートa, bそれぞれに同じ形式のデータがあります。(実際はa-eまで5シートありますが省略します。

シートaの内容例

    A        B   
1  検索番号  コメント
2  NO150   あ
3  NO150   ああ
4  NO100   あああ
5  NO120   ああああ
6  NO120   あああああ
7  NO130   ああああああ

シートbの内容例

    A        B   
1  検索番号  コメント
2  NO150   い
3  NO100   いい
4  NO100   いいい
5  NO100   いいいい
6  NO120  いいいいい
7  NO130   うううううう

このデータを一つにまとめたいです。

アウトプット

    A        B        C
1  検索番号  aコメント   bコメント
2  NO150   あ           い
3  NO150   ああ
4  NO100   あああ       いい
5  NO100               いいい
6  NO100              いいいい
7  NO120   ああああ     いいいいい
8  NO120   あああああ
9  NO130   ああああああ   うううううう

因みに、検索番号は全部で50パターンあります。連番等ではありません。
よろしくお願いします。

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

 こんばんわ。

 関数には向いてなさそうな要件ですね。
 上級者なら良い式が出るかも知れませんが、5つもシートがあるなら私だと作業列を使ってもかなり長く複雑な式になりそうです。
 マクロはダメなんですか？

 後もう少し仕様を明確にして下さい。

 NO150
 NO100
 NO150
 のように同じNOが離れる事は無いですか？

 aシートは、
 NO150
 NO100
 bシートは、
 NO100
 NO150
 のようにシートでNOの並びが違う事は無いですか？
 もしあれば、どちらの並びを優先させるんですか？

 NOでaシートにはあるが、bシートには無いと言うパターンや、その逆のパターンが混在している事は無いですか？
 以下のような事です。
 aシート
 NO150 ←bシートには無い
 NO100
 NO120
 bシート
 NO100
 NO130 ←aシートには無い
 NO120

（sy） 2016/08/30(火) 20:52

syさん
早速の返信ありがとうございます。
また、質問内容が分かりづらくすみません。

同じNOが離れる事はありません。
また、シート違いでNOの並びが逆転するような事もありません。
ただし、シート違いでNOがあったり、該当がなかったり、というのはあり得ます。

できれば関数でやりたいと思っています。
宜しくお願いいたします。
（うさ） 2016/08/30(火) 21:14

 ＞シート違いでNOがあったり、該当がなかったり、というのはあり得ます。 
 一番厄介な条件が有効ですね。。。

 以下のような例で、並び順まで指定されたら私ではすぐに思いつきません。

 aシート
 NO150
 NO100
 NO160
 NO120
 NO110
 bシート
 NO150
 NO100
 NO120
 NO130
 NO110

 アウトプットが以下のようになら出来ます。
 aシートのNOは並び順(以下ではNO160が該当)、bシートのNOはaシートにある項目より下になる(以下ではNO130が該当)
 NO150
 NO100
 NO160
 NO120
 NO110
 NO130
 このNO130がNO110より上に来るようにしなければいけないと言う事でしたら、私では簡単には思いつかないので、上級者の回答をお待ち下さい。

 後各シートのデータが最大100行くらいでは問題ありませんでしたが、数百行になるとかなり重くなると思います。
 1000行とかを超えると使えないかも？
 あまり良い式でもないので誰かが効率の良い式を提示してくれたら、そちらを使って下さい。
 提示の式は各シート100行までで作成しています。

 アウトプットシートのH～K列を作業列にします。
 H2 =IF(a!A2="",NA(),a!A2)
 　H100までオートフィル
 H102 =IF(b!A2="",NA(),b!A2)
 　H200までオートフィル
 H202 =IF('c'!A2="",NA(),'c'!A2)
 　H300までオートフィル
 H302 =IF(d!A2="",NA(),d!A2)
 　H400までオートフィル
 H402 =IF(e!A2="",NA(),e!A2)
 　H500までオートフィル

 I2 =IFERROR(INDEX(H$1:H$500,AGGREGATE(15,6,1/(MATCH(H$2:H$500,H$1:H$500,0)=ROW(H$2:H$500))*ROW(H$2:H$500),ROW(H1))),"")
 J2 =MAX(COUNTIF(H$2:H$100,I2),COUNTIF(H$102:H$200,I2),COUNTIF(H$202:H$300,I2),COUNTIF(H$302:H$400,I2),COUNTIF(H$402:H$500,I2))
 K2 2を入力
 K3 =2+SUM(J$2:J2)
 それぞれ全て表示されるくらいまで下にオートフィル(最大500行)

 A2 =INDEX(I$2:I$500,MATCH(ROW(),K$2:K$500))
 B2 =IF(OR(A2="",COUNTIF(a!A$2:A$100,A2)<COUNTIF(A$2:A2,A2)),"",INDEX(INDEX(a!B$1:B$100,MATCH(A2,a!A$1:A$100,0)):INDEX(a!B$1:B$100,MATCH(A2,a!A$1:A$100,0)+COUNTIF(a!A$2:A$100,A2)),COUNTIF(A$2:A2,A2)))
 C2 =IF(OR(A2="",COUNTIF(b!A$2:A$100,A2)<COUNTIF(A$2:A2,A2)),"",INDEX(INDEX(b!B$1:B$100,MATCH(A2,b!A$1:A$100,0)):INDEX(b!B$1:B$100,MATCH(A2,b!A$1:A$100,0)+COUNTIF(b!A$2:A$100,A2)),COUNTIF(A$2:A2,A2)))
 D2 =IF(OR(A2="",COUNTIF('c'!A$2:A$100,A2)<COUNTIF(A$2:A2,A2)),"",INDEX(INDEX('c'!B$1:B$100,MATCH(A2,'c'!A$1:A$100,0)):INDEX('c'!B$1:B$100,MATCH(A2,'c'!A$1:A$100,0)+COUNTIF('c'!A$2:A$100,A2)),COUNTIF(A$2:A2,A2)))
 E2 =IF(OR(A2="",COUNTIF(d!A$2:A$100,A2)<COUNTIF(A$2:A2,A2)),"",INDEX(INDEX(d!B$1:B$100,MATCH(A2,d!A$1:A$100,0)):INDEX(d!B$1:B$100,MATCH(A2,d!A$1:A$100,0)+COUNTIF(d!A$2:A$100,A2)),COUNTIF(A$2:A2,A2)))
 F2 =IF(OR(A2="",COUNTIF(e!A$2:A$100,A2)<COUNTIF(A$2:A2,A2)),"",INDEX(INDEX(e!B$1:B$100,MATCH(A2,e!A$1:A$100,0)):INDEX(e!B$1:B$100,MATCH(A2,e!A$1:A$100,0)+COUNTIF(e!A$2:A$100,A2)),COUNTIF(A$2:A2,A2)))
 それぞれ全て表示されるくらいまで下にオートフィル(最大500行)

（sy） 2016/08/31(水) 00:14

 横から失礼します。

 もしかしたら、今後、シートが増減するかもしれませんね。　シート b がなくなったり　シート z が追加になったり。
 あるいは、各シートのレイアウトがちょっと変更になったり。

 うさ　さんが　関数スペシャリストで syさん提示の式も、ごくごく自然に理解できる、変更があっても自分でなんとでも対応できる
 ということなら 関数処理もいいでしょうけど、要件が変わった時に、また　質問しなければいけないのであれば
 しょせん（？）提示された式も【呪文】のようなものですよね。

 であれば、同じ【呪文】かもしれませんが、マクロコードのほうが、ずっとシンプルですっきりしていて
 シート増減などにも自動で対応できるといったメリットもありそうですよ。

（β） 2016/08/31(水) 06:27

syさん
ご丁寧な回答ありがとうございます。
まだ完全には理解出来ていませんが、ご提示頂いた式で出来ました。
本当にありがとうございました。

βさん、syさん
お二人のおっしゃる通り、このケースは関数向きではないようですね。
今までマクロには抵抗があり、関数でずっと対応してきましたが、作成データも複雑になってきているので、これを機会にマクロも勉強してみようと思います。
ありがとうございました。
（うさ） 2016/08/31(水) 08:22

コメント返信：

[ 一覧(最新更新順) ]

[[20160830183555]] 『検索番号別にデータの並び替え(関数希望)』（うさ） ページの最後に飛ぶ

[[20160830183555]]　『検索番号別にデータの並び替え(関数希望)』（うさ）　ページの最後に飛ぶ