商品名から余分な情報を省きたい

質問

商品名が並ぶ同じ列の中を見比べて、
途中まで同じ名称があれば、
次のシート上で、同じ文字や数字の範囲までのみにカットして、
異なる部分からを次の列に移動させることはできますか？

エクセル利用して、棚おろし用の管理データ（図赤枠）から
同じ商品名をまとめた表（図緑枠）をつくりたいたいのですが、
発行月やセット数などが商品名にくっついてしまっていて
ひとつづつカウントしたり、名称をコピペ修正しています。

マクロをご利用いただいても構いません。
もしくは図の緑枠のようにならなくてもいいので
図の青枠のように商品名以降が分断される形でも大丈夫です。

下手な説明文で申し訳ございません。
もしご存じの方がいらっしゃいましたら
どうぞよろしくおねがいします。

回答No.6

n-jun · ベストアンサー

＃４です。

Ａ２　あいうえお１０月用
Ａ３　あいうえお１１月用
を
Ａ２　あいうえお１　　Ｂ２　０月用
Ａ３　あいうえお１　　Ｂ３　１月用
にするのなら、

Sub try()
Dim i As Integer
Dim st As String

For i = 1 To Len(Range("A2").Value)
If Mid(Range("A2").Value, i, 1) = Mid(Range("A3").Value, i, 1) Then
st = st & Mid(Range("A2").Value, i, 1)
Else
Exit For
End If
Next

Range("B2").Value = Replace(Range("A2").Value, st, "")
Range("B3").Value = Replace(Range("A3").Value, st, "")
Range("A2:A3").Value = st

End Sub

こんなのでも出来ますが、これを１万行に対してやるとなると対策もややこしそうです。（私の頭の中では）

お礼

n-jun様

どうもありがとうございます！
n-jun様がおっしゃられていた意味が..やっと分かりました
（頭弱くてすみません....）

いただいたマクロは「おぉ」と言ってしまうほど
2つの項目がきれいに要素が分かれたのですが

2つを比べて、変更点を見つけて、分けて入れる作業を
1万回繰り返してやっと1つできて、2つ目を9999回繰り返して2つできて…
と途方もないことになるんですね。。

もしくは名前順に並び替えた後に
奇数行と偶数行を比較した後に、
偶数行と奇数行を交互に比較したとしても
各1万回近く見比べることになったり..
どんな方法をとっても途方の無い感じがしました。

いただいたマクロを自分なりに分かる範囲で
セルっぽい数字を入れ替えたり、加えたりしてみましたが
複数行で見比べる方法すらわからなく。

ひとまずエクセルに頼り切らずに
区切り位置を地道に見つけていってみます。
アドバイス本当に感謝しています、
どうもありがとうございます！

回答No.5

tom04

No.2です！
たびたびごめんなさい。

やはり一筋縄ではいかないのですね？

規則性がないということが一番ネックのような感じがします。

根本的な解決法ではないのですが、一つの案としては
マクロに記録してみてはどうでしょうか？

当方使用のExcel2003での場合ですが
元データのSheet1はそのままで
メニュー　→　ツール　→　マクロ　→　新しいマクロの記録
これでマクロの記録ダイアログボックスが出ますので
そこでショートカットキーを設定しておきます。
仮にキーをｑに設定すれば、次からはそのブックを開きデータが増えた段階でCtrl＋ｑ　でマクロが実行されます。

新しいマクロの記録を開始します

Sheet1のデータをSheet2にコピー　→　名前順で並び替えてみてはどうでしょう？
オートフィルタで関連性がありそうなものだけを抽出　（○○を含むでフィルタをかければ抽出できると思います。）

抽出したデータを別Sheetにコピーします。
同様に何種類かの似たようなデータをSheet別にコピーしていきます。

それぞれのSheetで
メニュー　→　データ　→　区切り位置　を選択しセルを分割します。

最後に分割したSheetを一つのSheetにコピーしてまとめます。

そして、Sheet1の空白セルをアクティブにして「記録終了」

これで、Sheet1に何か入力されたら、Ctrl＋ｑ　でお母様も簡単にマクロが実行できると思いますが・・・

この程度しか思い浮かばないのであまり参考にならないかもしれませんね。

何度も失礼しました。m(__)m

お礼

tom04 様

すみません、お礼ではなく補足にコメントを入れてしまいました。
数度にわたりアドバイスをいただきどうもありがとうございました。

解決には至りませんでしたが、
そもそも解決しない漠然とした質問をしてしまっている内容に対して
丁寧にアドバイスをしていただき感謝しています。

アドバイスしていただいた方法を時短に試しつつ
作業させていただきたいと思います。
エクセルの機能を知ることができた事にも感謝しつつ
お礼にかえさせていただきます。
本当にどうもありがとうございました。

補足

tom04 様

たびたびごめんなさいだなんてとんでもありません！
丁寧なアドバイス恐れ入ります、感謝しています。
どうもありがとうございます。

二つに分ける、同じものを見つける、というよりも
分け目の規則性をみつける事が重要だったんですね、
その点が発見できただけでも自分には前進だと思いますm(_ _)m

アドバイスいただきましたマクロの記録方法試してみました、
（自分のエクセル上での動作を繰り返してくれるんですね（驚））

当初知人の方にオートフィルタを勧められたのですが、
私の説明下手もあり、したいものとは全く違うものになり
“オートフィルタではできない”という認識をしておりましたが
選別方法の一部に利用して、動作をマクロで記録しておいて
まとめて活用するというアイデアは目から鱗です！

試してみたものはなかなかうまくいきませんでしたが
区切り位置をするのに具合のいいフィルタのかけ方を
いじりながら探ってみます。どうもありがとうございます。

マクロの記録は他のものでも活用させていただきます！
どうもありがとうございます。

回答No.4

n-jun

＃３に補足します。

Ａ２　あいうえお１０月用
Ａ３　あいうえお１１月用

と言うデータがあったとします。
セルＡ２を基準としてＡ３と比較した場合、頭の５文字を順に比較していくと、
一致しないのは７文字目の”０”と”１”です。

この時一致する部分と言えば”あいうえお１”ですが、今回”１”は外さないといけないようです。

この場合外す条件として”数字である事”と言えれば楽ですが、質問文からそれは無理なようです。

となると一致する部分と分ける部分の区別方法が重要になってきます。
これは”目で見てやる作業”とは異なるため、やっかいになると思ってます。

逆に言えば「”○○月用”と言う物を省く」とできれば、＃１で回答した感じでいけるかと。
あとはそのパターンが何種類になるか位でしょう。

補足

n-jun様

我儘な質問にも関わらず丁寧にアドバイス、補足いただき
本当にどうもありがとうございます！

“あいうえお１”と“１”を付けた状態でいいとしても
1万行を1データづつ比較する必要があって
とんでもない作業になってしまうとのことですが、
完全にでは無いとしても、補正が必要になる箇所を一部分だけ残すだけで
かなりイメージに近い状態までできると思うんです。

補足でいただきましたような
頭の文字から比較するような方法（仕組み？）
お教えいただけると非常に助かります。
どうかよろしくお願いします。

回答No.3

n-jun

＃１です。

分割したいデータ部分の条件が明確になれば対応も可能かも知れません。

＞先頭から一定の文字数の内容が同じものと
＞そこから後にある個別の要素を分けたいと思ってます。
元のデータが１万行あり、その中に２行類似する物が存在したとします。
その２行を”類似した物”と識別するためには、１万行のデータを１文字ずつ
比較しなければならないと思います。
⇒区切られる位置が一定でないために。

なので、とんでもなく大変なものになると私は思います。
⇒試してないので自信はありませんが、区切る条件を与えた方が楽に感じてます。

回答No.2

tom04

こんばんは！
参考になるかどうか判りませんが・・・

無理矢理って方法です。
↓の画像のような感じで表を作ってみました。

質問では検索文字列は3種類だけみたいですが、実際はもっとたくさんあるのではないかと思います。

当方使用のExcel2003ではIF関数のネストは7つまでしかできませんけど、
ちょっとしたヒントになればと思い顔を出しました。

検索文字列を表にしておきます。
画像ではE2～E4までです。

B2セルに
=IF(A2="","",SUBSTITUTE(A2,C2,""))

C2セルに
=IF(COUNTIF(A2,$E$2&"*"),SUBSTITUTE(A2,$E$2,""),IF(COUNTIF(A2,$E$3&"*"),SUBSTITUTE(A2,$E$3,""),IF(COUNTIF(A2,$E$4&"*"),SUBSTITUTE(A2,$E$4,""),"")))

として、B2・C2セルを範囲指定し、C2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、検索文字列がもっと多いのであれば
とりあえず、7個のデータで数式を作ります。

検索文字列が指定されていない行はB列が空白になると思いますので
オートフィルタでB列の空白以外を選択
これでとりあえず、セル分割できているものが表示されますので
データ範囲を選択　→　コピー　→　別のセル（別Sheetが良いかも知れません）に　形式を選択して貼り付け　→　「値」にチェックを入れOK

そして、Sheet1に戻りE列に入りきらなかった検索文字列を入れ替えます。
同様に、オートフィルタでB列の空白以外を選択し、

先ほど貼り付けたセルの下へ「値」で貼り付けます。

かなり無理矢理の方法で手間がかかりますが、希望に近い形になると思います。

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

補足

tom04様

画像もご用意いただきアドバイスどうもありがとうございます。
早速試させていただきましたところ、サンプルできれいに2つに分けれました。
アドバイスいただいたIFの関数も若干理解できました。
丁寧なご説明とアドバイス本当にどうもありがとうございます。

tom04様のおっしゃるように実際にはもっと数がありまして、
アドバイス中の“E列”に商品を並べていくことについてなのですが、
3・4つほど単語が並んだ商品もありまして
どこまでを共通とすれば複数でまとめられるのか
多い商品の中で見つけていくのに骨が折れてしまいそうです

パソコンに疎い母にも管理してもらうので
できれば棚おろし用の管理データやPOSなどのデータを
張り付けるだけで余分な情報を省かれた状態にできるような
仕組みができればと思っています。。

回答No.1

n-jun

マクロで一例。

Sub try()
Dim myReg As Object
Dim r As Range

Set myReg = CreateObject("VBScript.RegExp")
myReg.Pattern = "([０-９]+月用|[０-９]+セット|[春夏秋冬]{1}号)"

For Each r In Range("A2:A20") 'セル範囲は任意です 例：A2～A20
If myReg.Test(r.Value) Then
r.Offset(, 1).Value = myReg.Execute(r.Value)(0).SubMatches(0)
r.Value = Replace(r.Value, r.Offset(, 1).Value, "")
End If
Next

Set myReg = Nothing

End Sub

ご参考になれば。

補足

n-junさま
早々のご回答感謝です、ありがとうございます。
マクロがどう動いているか分からないのですが
サンプルファイルきれいに要素を分離できました！

ただ実行したいエクセルファイルで試してみると
思うよう動作してくれませんでした（泣
文字と文字の分かれ目を設定されていそうな
恐らく下記↓この部分が触れそうだったのですが..
"([0-9]+月用|[0-9]+セット|[春夏秋冬]{1}号)"

色々変更して試してみたのですが分かりませんでした、
申し訳ありません。。

私の説明不足が原因なのですが、
元のファイルは1万商品近くありまして
商品名の後ろにいきなり製品番号が入るものや
本ですと、本の名前の後ろに（文庫）or（単行本）などや
POSデータだとお食事名の後ろにドリンクのセットなど
分かれ目が結構バラバラな状態なのです。

先頭から一定の文字数の内容が同じものと
そこから後にある個別の要素を分けたいと思ってます。

ただプログラムが分からないので
そもそも分ける基準が明確でないものを分けるということが
無理なこと言ってるかもしれません。