データの整理

質問

お世話になります。
不特定のデータの整理に困っています。お願いします。

　　　　　A｜　　　 B ｜　　　　 C｜　　　　 D｜
1｜重さ：10｜長さ：3 ｜サイズ：S ｜（空白） ｜
2｜長さ：20｜重さ：10｜耐久性：有｜サイズ：M｜
3｜重さ：30｜長さ：3 ｜サイズ：L ｜（空白） ｜

のような項目が、あったとします。行も列も不特定の数です。
内容も様々なものがあります。
「重さ」（"："の前の文字）を項目名とすると、項目名順にそろえたいと思っています。

　　　　　A｜　　　 B ｜　　　　 C｜　　　　 D｜
1｜重さ：10｜長さ：3 ｜サイズ：S ｜（空白） ｜
2｜重さ：10｜長さ：20｜サイズ：M｜耐久性：有｜
3｜重さ：30｜長さ：3 ｜サイズ：L ｜（空白） ｜

こんな感じです。列か行単位で並べ替えることしかできないのはわかっているんですけど…。
ちなみに列で一つの商品です。
こんなデータが300件あって困っています。
もっと理想的なことを言うのであれば、上のように整えたあと、重複している内容を削除して

　　　　　A｜　　　 B ｜　　　　 C｜　　　　 D｜
1｜重さ：10｜長さ：3 ｜サイズ：S ｜（空白） ｜
2｜　　　　 ｜　　：20｜サイズ：M｜耐久性：有｜
3｜　　： 30｜　　　　｜サイズ：L ｜（空白） ｜

なんてこともできればなぁ、と夢のようなことを考えています。
項目名はもともとわかれていたものを"&"でくくっているので分裂可能です。
今は並べ替えをして手作業で最終の形に直しています。
最初は上の行と比較して同じならば表示しない、みたいなことをしていましたがそうすると1と3の行が同じだった場合とかに適応されず…
オートフィルタもうまくできませんでした。
VBAは簡単なものならできます。
無理なのは重々承知ですが、どうかお願いいたします。

エクセル：Excel2004forMac
環境：IntelMac

回答No.1

mt2008 · ベストアンサー

まずは項目順に揃えたデータにする事が先決ですね。
その後はいかようにでも出来るでしょう。

こんなのはいかが？
添付の図を見てください。
整理する表に「重さ：」などの項目名を付けます。
各行で、その項目名が含まれる順番をMATCHで取得し（例：1列目＝１）、OFFSETでその値を取り出します（例：「重さ：10」）。次にSUBSTITUTEで、項目名を取り去っています（例：「重さ：10」→「10」）。
また、各行に存在しない項目名がある事を考慮し、MATCHの結果がN/Aの場合は空白にしています。

添付の図では、F2に↓を入れ、コピーしています。
=IF(ISNA(MATCH(F$1&"*",$A2:$D2,0)),"",SUBSTITUTE(OFFSET($A2,0,MATCH(F$1&"*",$A2:$D2,0)-1),F$1,""))

お礼

ありがとうございます！おかげでやることが出来ました！！
なんとなく、OFFSETは使えないかなぁと思っていたのですがうまい案が思い浮かばず…他の関数と組み合わせば良かったのですね。
もっと勉強して精進いたします。
本当にありがとうございました。