納品書の宛名を簡単入力する方法

質問

顧客シートの電話番号下４桁が一致した行の名前を検出しリストボックスに表示したいのですが設定の仕方がわかりません。
電話番号下４桁ですので一致するセルが複数になり関数vlookでは対応できません。
初心者です。簡単な方法ご伝授ください。よろしくお願いいたします。

回答No.4

hige_082 · ベストアンサー

＃３です
　　Ａ　　　　　　Ｂ　　　　　　　　　　Ｃ　　　Ｄ　　　　Ｅ
１　検索番号　電話番号　　　　　名前　検索値　検出された名前
２　　　　　　　　03-1234-5678　伊藤　　　
３　　　　　　　　03-2345-6789　斉藤　　　　　　
４　　　　　　　　03-3456-7890　佐藤　　　　　　
５　　　　　　　　06-1234-5678　首藤
６　　　　　　　　06-2345-6789　江藤　
７　　　　　　　　06-2345-5678　藤田
上のような表を想定しています
ただ、＃３では項目名を想定していない数式だったので
項目名（1行目）を考慮した式へ変更しています
A2へ
=COUNTIF($B$2:B2,"*-"&$D$2)
A3～A7へコピー（データが100行あれば：A3～A100までコピーする）
E2へ
=IF(COUNTIF(A:A,ROW(A1))=0,"",VLOOKUP(ROW(A1),$A$2:$C$7,3,0))
E3～E5までコピー（重複番号は最大４つなので）
注意
データが100行の場合（実際のデータ行数が分らないので、上の数式は上の表を対象（データ2～7行）とした数式です）
E2の数式を
=IF(COUNTIF(A:A,ROW(A1))=0,"",VLOOKUP(ROW(A1),$A$2:$C$100,3,0))
と書き換えて再度、E3～E5までコピーしてください

後は、D2（検索値）へ検索したい電話番号の下4桁を入力すれば
E2以下最大4つまで検索できます
例
D2へ 5678 と入力
E2以下、伊藤、首藤、藤田と順に表示されます

＞電話番号は、Ｂ列に下４桁で揃えました。
必要ありません、＃３の最初にも書きましたが、＃１の回答は無視してください
電話番号は03-1234-5678か1234-5678で対応しています

＞必要な行はいくつでどの列のどの行にコピーすればよろしいでしょうか。
知りたいのは、回答者のほうです
もともと質問の仕方が良くありません
最初の質問で表の構成やデータの行数の説明が文章や画像であれば
回答者のほうで上のような表や説明は不要なはず

お礼

質問の仕方が悪くてお悩ませして申し訳ございません。
１，５００件の名簿からなんとか４桁の数字だけで納品書に名前を打ち込みたいともがいてきました。
　質問の仕方が良くなくてすみませんでした。

回答No.3

hige_082

複数ですか
前回の解答は忘れてください
A列：検索番号、B列：電話番号、C列：名前、D1:検索値、E1：検出された名前とします
電話番号の前に1列挿入し
次の数式を設定します(A列)
=COUNTIF($B$1:B1,"*-"&$D$1)
必要な行までコピーします
E1に、次の数式を設定します
E1
=IF(COUNTIF(A:A,ROW(A1))=0,"",VLOOKUP(ROW(A1),$A$1:$C$20,3,0))
重複する最大値分下方向へコピーしてください

D1:検索値へ検索したい電話番号下4桁入力してください
E1に検出された名前が表示される
一致する電話番号が複数ある場合は下方向に名前が表示されます

以上参考まで

補足

ご回答ありがとうございました。申し訳ございませんが、もう少しお教えいただけないでしょうか。
> =COUNTIF($B$1:B1,"*-"&$D$1)
必要な行までコピーします
電話番号は、Ｂ列に下４桁で揃えました。重複番号は最大４つです。
　必要な行はいくつでどの列のどの行にコピーすればよろしいでしょうか。
　色々試してみたのですが、どうしてかE1が空白になってしまいます。
どうかよろしくお願いいたします。

回答No.2

fujillin

＞簡単な方法ご伝授ください。

簡単な方法でもないですが…
ワークシートの値が変わった時に、実行されるマクロを利用して、リストから該当するものをピックアップして、入力規則のリストに代入するというものです。

リストの位置や入力欄などは、サンプルとして適当に仮定しています。
意味不明の場合は、無視してください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ListRng As Range, rw As Long, col As Long, i As Integer
Dim reslt As String, num As String, tmp As String
Const comma = ","
Const ListAddr = "A3:B7" '//顧客リストの位置
Const target_column = "D" '//番号入力欄の列
Const target_row = 3 '//番号入力欄の最小行番号

col = Columns(target_column).Column
If Target.Column <> col Or Target.Row < target_row Then Exit Sub
If Target.Text = "" Then Exit Sub
Set ListRng = Range(ListAddr)
num = Right(Target.Text, 4)
result = ""

For rw = 1 To ListRng.Rows.Count
　If Right(ListRng.Cells(rw, 2).Text, 4) = num Then
　　tmp = ListRng.Cells(rw, 1)
　　Do While InStr(tmp, comma) > 0
　　　i = InStr(tmp, comma)
　　　tmp = Left(tmp, i - 1) & "，" & Right(tmp, Len(tmp) - i)
　　Loop
　　result = result & comma & tmp
　End If
Next rw

If result <> "" Then
　With Cells(Target.Row, Target.Column + 1).Validation
　　.Delete
　　.Add Type:=xlValidateList, Formula1:=Right(result, Len(result) - 1)
　End With
End If
End Sub

お礼

ご回答ありがとうございました。
コピーして動かしてみたのですが、最初に準備する表の作り方がわからず”end sub"が必要ですと、エラーが出て動いてくれません。
　どうすれば画像のような結果が出るか勉強してみます。御礼が遅くなりまして申し訳ございません。ありがとうございました。

回答No.1

hige_082

電話番号の後に１列挿入し
電話番号下４桁を取出しておく
=RIGHT(電話番号,4)
この列をvlookupで検出するのが
簡単な方法だと思います

補足

ご回答のお礼が遅くなり申し訳ございません。ご回答ありがとうございました。
この方法を試してみたのですが５６３－１２３４、５６４－１２３４と
下４桁２つ同じ番号がある場合二つをリストに載せたいのですが、１つしか検索してくれません。
２つとも検出する方法はございませんでしょうか。
　再度よろしくお願い申し上げます。