セル内の何行目にあるかを数えて、該当の値を返す方法

お世話になります。
エクセル2016を使用しております。

特定の文字が、同一セル内の何行目にあるかを探し、
別のセルの同じ行数にある値を返す方法をご教授ください。

例
A1セルに
改行
改行
◎

B1セルに
一組
二組
三組

とあった場合に、A1の◎のある行数にあたる
B1の値「三組」をC1のセルに返したいのです。

※ 行数は行ごとにランダムに変わります。

関数でできるでしょうか？
不可能であれば、マクロでも構いません。

宜しくご教授ください。

No.3 回答者： mike_g 回答日時： 2019/03/19 22:11

１．D列は空白のままに放置して、次式を入力したセル E1 を右方にオートフィル

￣￣=IFERROR(FIND(CHAR(10),$B1,D1+1),999)
２．次式をセル C1 に入力
￣￣=IF(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")),MID(B1,INDEX(E1:H1,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))+1,INDEX(E1:H1,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)-INDEX(E1:H1,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))-1),MID(B1,1,E1-1))
３．範囲 C1:H1 を下方にオートフィル

この回答へのお礼

早速の回答ありがとうございます。

このままコピペして試したところ、なぜかすべての解が三組に…。
しかし、根気よく読み解き、少し加工し、
希望通りの解を得ることができました。

とても参考になりました。
ありがとうございました。

お礼日時：2019/03/20 18:01

No.2 回答者： tom04 回答日時： 2019/03/19 19:02

こんばんは！

A列・B列とも1セル内で改行されているのですね。

VBAになりますが、一例です。
↓の画像のようになっているとします。

Sub Sample1()
　Dim i As Long, k As Long
　Dim myAry1, myAry2
　　Range("C:C").ClearContents
　　　For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
　　　　myAry1 = Split(Cells(i, "A"), vbLf)
　　　　myAry2 = Split(Cells(i, "B"), vbLf)
　　　　　For k = 0 To UBound(myAry1)
　　　　　　If myAry1(k) = "◎" Then
　　　　　　　With Cells(i, "C")
　　　　　　　　If .Value = "" Then
　　　　　　　　　.Value = myAry2(k)
　　　　　　　　Else
　　　　　　　　　.Value = .Value & vbCrLf & myAry2(k)
　　　　　　　　End If
　　　　　　　End With
　　　　　　End If
　　　　　Next k
　　　Next i
End Sub

上記マクロを実行すると
画像のC列のような表示になります。m(_ _)m

この回答へのお礼

早速の回答ありがとうございます。

そのままコピペして試し、望み通りの解が得られました。
ただ、少し加工したいと思うとまだ不勉強なため
難しいところがありました。

これを機会にVBAも勉強していきたいと思います。
とても参考になりました。
ありがとうございました。

お礼日時：2019/03/20 17:56

No.1 回答者： nouble1 回答日時： 2019/03/19 16:16

文字は　主に、

SUBSTITUTE構文や、FIND構文で、
探します。


SUBSTITUTE構文では、

探したい　文字を、
「"'」(※注：空文字)に、
置換する　事により、

一文　総文字数の、
変化を　きたせ、

此を　検知して、
見分けます。


FIND構文では、

見つかれば、数値を、
見つからなければ　エラーを、
返す　仕様を、
利用して、

ISNUMBER構文や、ISERROR構文、
等で、
指定検索文字の　有無を、
見極めます。


さて、
指定検索文字の　有無が、
判ると、
次は　位置ですが、

有無判定結果を　論理値で、
返させて、

行位置なら　ROW構文を、
列位置なら　COLUMN構文を、
かけます。


すると、
指定検索文字が　ある所を、
TRUE値に　しておく事で、

見つからなかった　箇所では、
掛け合わせた　結果が、
ゼロに、

見つかった　箇所では、
掛け合わせた値が、

各々　残ります。


見つかりそうな　箇所が、
一箇所しか　無い場合は、

SUMPRODUCT構文で、
総和を　取れば、
いいですし、

多数　ありそうな場合は、

SMALL構文や、LARGE構文で、
順番に表せば、

シート上に　上手に、
表せます。

この回答へのお礼

早速の回答ありがとうございました。

エクセルの関数の検索では、上手く希望の関数を
引き当てることが出来ずにいたので、とても参考に
なりました。

おかげさまで、なんとか、希望の解を得る関数式を
組み立てる事ができました。

ありがとうございました。

お礼日時：2019/03/20 18:06