エクセルでの文字列の検索と追加表示していきたい。

質問

Ａ　　Ｂ　　　　Ｃ　
１　 い　　
２　 ろ　
３　 い　　１
４　 に　
５ 　ほ　
６　 い　　１、３
７ 　と　
８　 ろ　　２
９　 り
10　に　　４
11　い　　１，３、６
12　ろ　　２、８

文字列Ａ内で重複するものの行の番号を上のように追加していきたいのですが、可能でしょうか？
自分でいろりろ試してみましたが、ダメでした。
この方法を教えてください！

回答No.3

noname#52504 · ベストアンサー

かなり強引な方法ですが、作業列を使えば、数式でもできなくはないです。

各列1行目を

B列：作業列１
=COUNTIF($A$1:A1,A1)

C列：作業列２
=A1&"【"&B1

D列：作業列３
=IF(B1=1,"",MATCH(A1&"【"&(B1-1),$C$1:C1,0)-1)

E列：結果列
=IF(B1=1,"",IF(B1=2,"",OFFSET($E$1,D1,0)&",")&ROW(OFFSET($E$1,D1,0)))

として、以下末行までフィル。

"【"のところは、「4番目の413」と「34番目の41」を区別するための区切記号なので、A列の末尾にこない文字ならば何でもOKです。

0～999までのランダムな整数15,000行についてテストしてみましたが、職場のノート(CeleronM 1.4G)で２分弱、自宅のデスクトップ(Pentium 4G)でも70秒ほど要しました。

お礼

できました。御礼が遅くなってしまって申し訳ありません。
VBAは理解不能でした～（涙

回答No.2

Wendy02

こんばんは。

本来、1つの語だけの検索なら、配列数式で出来ますが、複数ある場合、VBAのユーザー定義関数のようなものにするか、並べ替えなどを使わなければ不可能だと思います。以下は、ワークシートの配列数式と同じ考え方です。

これは、もしかしたら、Excel 2000 以下で、大量のデータですと、支障があるかもしれません。

'<標準モジュール>
Function FindValueRows(myRange As Range, arg2 As Range) As String
　 Dim RangeAddress As String
　 Dim buf As String
　 Dim RowCount As Long
　 Dim Ar() As Variant
　 Dim i As Long
　 If IsEmpty(arg2) Or IsError(arg2) Then Exit Function

　 RangeAddress = myRange.Cells(1).Resize(Application.Caller().Row - myRange.Cells(1).Row + 1).Address
　 If Application.Caller().Row - myRange.Cells(1).Row = 0 Then Exit Function

　 Ar() = Evaluate(RangeAddress & "=" & arg2.Address)

　 For i = LBound(Ar(), 1) To UBound(Ar(), 1) - 1
　　　If Not IsError(Ar(i, 1)) Then
　　　　If Ar(i, 1) Then
　　　　　　buf = buf & "," & i
　　　　End If
　　　End If
　 Next i
　 FindValueRows = Mid$(buf, 2)
End Function

私の方の使い方は、FindValueRows(検索範囲,検索文字セル）
=FindValueRows($A$1:$A$12,A1)

式をドラッグして、フィルダウンコピーします。

回答No.1

BLUEPIXY

'標準モジュールに以下のような関数を定義する
'指定した範囲で指定した値がある位置をカンマ区切りの文字列で返す
Public Function dupPos(r As Range, v As String) As String
Dim ret As String, x As Range, pos As Long
ret = ""
pos = 0
For Each x In r
pos = pos + 1
If x.Value = v Then
ret = ret & pos & ","
End If
Next
If ret = "" Then
dupPos = ""
Else
dupPos = Left(ret, Len(ret) - 1)
End If
End Function
Ａ列にＡ１からデータが入っている時
Ｂ２に
=JIS(dupPos($A$1:A1,A2))
の式をいれ下にフィル(コピー)する