VBAを教えてください。次の行との部分一致

質問

こんばんは。過去の質問で探したんですが見つからないので、どなたか助けて下さい。

EXCELでA列に社名or店名、B列は空白、C列に電話、D列に住所、E・F・・・、と情報が入っています。
それを住所順(or電話)で並び替え、次の行とA列の社名が部分一致していればB列に
なにかしらのフラグをたてる(1とか●とか)VBAもしくは関数を教えていただけますか？

基本的に次の行と住所が同じならば 同じ会社or店だと思うんですが、
万が一違う会社or店だった場合に備え、部分一致している行にフラグがたっていれば、
それ以外(B列が空白行)をチェックする となればかなりラクです。
※店名の書き方が統一されていないため、単純にIFでは比べられません(ミス○ードーナ○ と ミス○等）
部分一致は3文字ぐらいで大丈夫かな？と思っているんですが。

回答No.6

Wendy02 · ベストアンサー

こんばんは。

#3　のお礼で書かれていた程度なら、こんなコードで済みますが、私が以前手がけたものは、
以下のコードの中の、

Const WLEN As Integer = 3

これを変数にして、6ぐらいから、だんだん減らして、3まで持っていく方法です。つまり、以下のコードで、もう一段、ループが必要になってきますから、時間も掛かります。それでヒットしたレベルを入れていくのです。ただ、1万行というような大量のレベルではありませんでした。

たぶん、こんなものでは足りないのでしょうけれども、一度、スモールサンプルを取り出して、チェックしてみてください。このぐらいなら、夕飯前ぐらいですが（^^;　実際に、マクロを行う場合は、Applicationの前の['] は外してください。10倍以上のスピードが違うことがあります。また、範囲は、A1 ～になっていますが、タイトル行などがあれば、そのままでも誤動作はしませんが、A2 ～にしても可能です。

---------------------------------------------------
'標準モジュールです。
--------------------------------------------------
Sub Test1()
Dim c As Range
Dim i As Long
Const WLEN As Integer = 3 '検索文字列数

'ここのApplicationの前の[']を外すと速くなる　--True 側も解除
'Application.ScreenUpdating = False
With ActiveSheet
For Each c In .Range("A1", .Range("A65536").End(xlUp).Offset(-1))
　 For i = 1 To Len(c.Value) - WLEN + 1
　　　If InStr(1, c.Offset(1).Value, Mid(c.Value, i, WLEN), vbTextCompare) > 0 Then
　　　　 c.Offset(, 1).Value = "●"　'ヒットが出れば、その行は終わり
　　　　 Exit For
　　　End If
　 Next i
Next c
End With
'Application.ScreenUpdating = True '上を外したら、こちらも外す
End Sub

お礼

御礼が遅くなって申し訳ありません。
今日会社で試してみました!　出来ました!!
とりあえず5千件でやってみたところ、結構あっという間に完了しました。
本当にありがとうございました。

回答No.8

Wendy02

こんばんは。

#6の回答者です。

>これからもできる限り過去の質問&回答を探しますが、またお世話になることがあるかと思いますのでよろしくお願いいたします。

Google 検索で、十分にここの内容の検索は可能のようですが、具体例を出して質問していただいたほうが早いかもしれません。質問する人と回答する人のバランスによって、こうしたQ&A掲示板は成り立っていますからね。

私が前に、回答した内容で2件ほど同様のものがあるという記憶ですが、それらからすると、比較的易しいです。

上下の隣り合ったという設定ですが、私は、最初、そのようなことは一切考えていなかったということです。全行に対するか、もしくは、A,B例以外が同じものに対して、検証するということも考えていたからです。それらは、難易度が、ワンランク上になります。

もうひとつは、漢字の読みがなを検索するというものです。

それから、最後に、検索の単語を6～5から3までの数を減らして、似ているヒットのランクをB列に出すというものです。

なお、
>(株)とか(有)以外にも、会社名or店名の整備が必要ですね。

(教えて!goo 側）
QNo.3983946 エクセルで１セル内の法人格（文字列）を分割するには？
http://oshiete1.goo.ne.jp/kotaeru.php3?qid=3983946
#3　がそれに当たります。

集計用や統計用の場合は、どうしても、このような整形マクロが必要になってしまいます。
括弧やアルファベットは半角にするとか、株式会社は、全部、後ろ(株)にするとかして、並べ替え用に利用するのです。それは、お客様に出すわけではないのです。何かを基準にしなければいけませんし、前(株)表記にしたら、とてもきちんとした並べ替えが出来ません。主とする名称を前に出して並べ替えをするわけですね。コードのランクとしては、少し上になります。

入力時　　　　　　　　　　　　マクロで整形後
---------------------------------------------
株式会社ＯＫＷＡＶＥ　　---　OKWAVE(株)
ＯＫＷＡＶＥ株式会社　　---　OKWAVE(株)
（有）OKwave　　　　　　---　OKwave(有)
オーケーＷａｖｅ（合)　 ---　オーケーWave(合)
オーケーwave(株)　　　　---　オーケーwave(株)
有限会社ABC　　　　　　---　ABC(有)

お礼

御礼が遅くなって申し訳ありません。
教えていただいた(作っていただいた？)VBA本当に助かっています。

>(株)とか(有)以外にも、会社名or店名の整備が必要ですね。
に関しては、現状すべて記憶マクロでやっています。
お客様に出すデータではないのですが、書き方を整え、比べ、マージする作業のため、前株を後株にするわけにはいかないんですが、参考にさせていただきます。

今回VBAを教えていただき、やっぱり便利だなぁと痛感しました。
『所詮派遣のおばちゃんだしぃ』と勉強する気になりませんでしたが、少しずつ学習したいと思います。
Google検索のコツもつかめるように頑張ります。
本当にありがとうございました。

回答No.7

tinu 2000（@tinu2000）

乗り掛かった舟ですので・・・
もう少しファジーにしましょう。
列がどこまで使っているのか分かりませんので、
AA列からAJ列を作業エリアとして使います。

AA1に「=IF(LEN($A1)<COLUMN()-24,"",IF(ISERROR(FIND(MID($A1,COLUMN()-26,3),$A2,1)),"","●"))」
と入力して、横にAJ1までコピー
さらに、AA1からAJ1までを下にコピー
これは、
AA列は、上の行の左から１文字目から３文字が下の行のどこかにあれば"●"を表示
AB列は、上の行の左から２文字目から３文字が下の行のどこかにあれば"●"を表示
と順番に、
AJ列は、上の行の左から１０文字目から３文字が下の行のどこかにあれば"●"を表示
つまり、A列の会社名は１２文字までと考えています。
(1)もっと多ければ適当に横にコピーしたらOKです。

最後に、
B1に「=IF(COUNTIF(AA1:AJ1,"●")>0,"●","")」を入力して下へコピー

AA1:AJ1　のAJ1　は(1)の会社名が１２文字以上で横にコピーした列の最後の列名を入力して下さい。

これで、
上の行のどこかの３文字が、下の行のどこかにあれば"●"が表示されます。
かなりファジーな部分一致な検索になります。

使っている関数の、LEN、COLUMN()、ISERROR、FIND、MID、-24、-26
はそれぞれの機能や意味を調べて理解して下さいね。
ではでは

お礼

御礼が遅くなって申し訳ありません。
最後まで舟に乗っていただきありがとうございました！
教えてgooに質問する前に、同じ考え方（あくまでも考え方だけですが)で、挑戦してみたんです。しかしながらどうやってもできなかったので助けを求めさせていただきました。
わかりやすく教えていただき大変感謝しております。
試す際にCOLUMNの意味がわからなくて、B列～そのまま式をコピーして「何もでないなぁ？」となりましたが、やっと理解することが出来ました。
ありがとうございました。

補足

こちらに書くのは使い方として間違っているかもしれませんが、回答いただいた皆様へどうしても一言。
今回いつも見ているだけの教えてgooにドキドキしながら初めて質問させていただきました。
見ず知らずの方にこんなに親切に質問の仕方やルール、回答をいただき感謝感激しました。
これからもできる限り過去の質問&回答を探しますが、またお世話になることがあるかと思いますのでよろしくお願いいたします。
本当にありがとうございました。

回答No.5

tinu 2000（@tinu2000）

> 住所に関してはすべて都道府県から、
> 電話番号に関してはすべて「－」をいれて統一させています。
> また社名についても(株)とか(有)とかは全て抜いてあります。
ここまで整備されているのでしたら、言うこと無いですね。

部分一致の仕方を
上の行の会社名の先頭３文字が、下の行の会社名の中のどこかにあれば"●"、無ければ""、で良いのでしたら、
住所が一致、電話番号が一致のチェックは必要無く、
B1に、「=IF(ISERROR(FIND(LEFT(A1,3),A2,1)),"","●")」
を入力して下へコピー
です。

LEFT(A1,3)は、A1の左から３文字分の意味です。
２文字でチェックするのでしたら、LEFT(A1,2)となります。

お礼

ありがとうございます。
私のスキルで考えられたのは、B1に「=IF(LEFT(A1,3)=LEFT(A2,3),"●","")」でした。それだと全然意味ないなぁと思っていたんですが　(例：A1→白木屋　A2→居酒屋白木屋　「●」が入ってほしいけど入らないから)
『下の行の会社名の中のどこかにあれば』であれば多少目視チェックがラクになりそうです。
ただ上の行の頭から3文字が大事になってくるので、(株)とか(有)以外にも、会社名or店名の整備が必要ですね。
(株)や(有)は記憶マクロで""に置換させているので、それにいろんな文字列を追加してみます。

回答No.4

tinu 2000（@tinu2000）

例えば、
大阪府大阪市北区　と　大阪市北区
の場合、住所で並び替えても上下に続いて並ぶとは限りません。

ここでは電話番号で並び替えるのが一番良いような気がします。
ただし、電話番号の信頼性がわかりませんので、
06－XXX－YYYY　と　06XXXYYYY のように「-」が付いているのと付いていないのが混在している、さらに全角、半角が混在しているのでしたら、
予備の列に「=SUBSTITUTE(ASC(C1),"-","")」と入力して下へ全件コピーする。
全ての電話番号を半角の「06XXXYYYY」にして
この電話番号を並び替えて、単純にIF文で電話番号の一致かどうかでフラッグを立てる。
もちろん、本社、支社、営業所など住所が違うと電話番号が違うのでフラッグは立ちません。
あとは人間の目でファジーチェックしか無いと思います。

余計な事ですが、
ミスタードーナッツ　と　ミスド（と思いますが）
わざわざ伏字にしなくても良いと思います。
宣伝でもなく、批判でもなく、
質問者さんの「店名の書き方が統一されていないため」を説明するためですからね。

お礼

ありがとうございます。
住所に関してはすべて都道府県から、電話番号に関してはすべて「－」をいれて統一させています。
また社名についても(株)とか(有)とかは全て抜いてあります。
単純に住所と電話番号が一緒であれば、次の行と同じ会社or店だろうと思うんですが、万が一違った場合に備えてチェックさせたいらしいです。

ミスドとか書いちゃってもいいんですね。質問に直接関係ないので念のため○を使ってしまいました。
ご指摘ありがとうございました。

回答No.3

imogasi

#1です。お礼に関連して。
（１）質問者のレベルを誤解してました。ヒントを与えれば後は自分でVBAコードは出来ると思いました。
質問には具体例（心配なタイプの住所文字列の例など）を挙げてくだされば、小生が出来れば、考えて見ますが。
この質問コーナーは丸投げして全部コードを回答者に書かせるのは原則禁止のはずです。
（２）＃１で挙げたサイトは、少しレベルが高い方の場合、この質問にひょっとすると関係するかもしれないと思い、多分大學情報科、大学院レベルの話題のことと思いますが、WEB照会で目に付いたものを挙げておいたものです。
結果的には、無視してください。
すみませんお騒がせしました。
ーー
質問の具体例がなく、「部分一致」という表現に拘りすぎたと思います。
「人を見て・・」といいますが、質問ではその辺が掴みにくいので。
ーーー
関数レベルでやって、後は目視チェックではいけないですか。
ビジネスの問題は結果について総合的・人間直感的・目視チェックをやらないと結果に安心できないと、小生の経験上感じます。

お礼

ありがとうございます。
例えばですが3文字で部分一致があるか調べた場合(住所と電話は同じだったとして)
A列　　　　　B列　　　　C列　　　　　　D列
あいうえお　　●　　　0120-111　　東京都中央区1-2-3　　次の行と「あいう」が一致
かきあいう　　　　　　 0120-111　　東京都中央区1-2-3
さしかきす　　●　　　0120-111　　東京都中央区1-2-3　　次の行と「さしか」が一致
たさしかと　　　　　　 0120-111　　東京都中央区1-2-3
という感じです。丸投げなお願いをしてはいけないんでしたね。
初めての質問で、いきなりルール違反とはっ!!・・・すいませんでした。
私としても目視チェックしたいところなんですが、毎週何万件となるとかなり厳しいので良いアイデアをいただけないかなぁと質問させていただきました。
関数でできる限りキレイにしてから、地道にチェックします！
サイトまで探していただき、本当にありがとうございました。

回答No.2

Wendy02

こんにちは。

そのマクロは、ここの掲示板で発表したことがあります。
でも、なかなか難しいです。

その時の考え方は、段階的な検索方法を使っていきます。フラグを立てるのですが、その度合いを、ループで検索の度合い、一段ずつ下げていきます。◎,○,△ と落としていきます。

言い換えると、１語ずつ、検索する語を減らしていく方法です。

ただ、思い出したのですが、それは、Search 関数という手もありますね。
Search 関数は、Find 関数とは、機能が違います。

ミズホ
ミスター　　　○
ミズノ
ミスチル　

=IF(ISERROR(SEARCH("ミス?ー",A1)),"","○")

のような検索をします。多少のワイルドカードはあります。しかし、もう少し木目の細かい検索方法は、やはりVBAで作って上げなくてはならないかもしれません。

お礼

ありがとうございます。
プロ(っておかしいかな？）の方でもなかなか難しいのならば、素人の私がどうこうできるはずがないですね(*_*)
SEARCHは何かしらの検索文字列をいれなきゃならないんですよね？であればやはり一行ずつ目視チェックした方が良さそうです。
教えていただいたSEARCHは別の機会に使わせていただきます！

回答No.1

imogasi

文字数の長い文字列の方を対象に、短いほうを「Instr関数で」で探してください。エクセルではFind関数に近い機能の、旧Basic時代からある関数です。
WEBで調べれば判ります。
新宿店　と　西新宿駅前支店で新宿が共通というのは、意味的に良くある新宿という言葉を割り出さないとならないので、ちょっと難しい。
参考
＞http://oshiete1.goo.ne.jp/qa2196030.html
などにむつかいい議論の一端が見える
http://hp.vector.co.jp/authors/VA007799/viviProg/doc5.htm
http://itpro.nikkeibp.co.jp/article/MAG/20061115/253802/

お礼

早速の回答ありがとうございます。質問の書き方が悪くて申し訳ないのですが、VBAは書けません(作れません?)
いつも教えてgooで使えそうなVBAがあると、内容はわからないけど標準モジュールにコピペして使わせていただいてるレベルです。
そんなわけでせっかく教えていただいたサイトを見てもサッパリでした(*_*)
素人には無理みたいなので、ひたすら一行ずつ目追いします。