Create a list of cells containing a text string from single column arrays and also returning row number in the adjacent found cell.

[molbio]

I'm having a devil of a time coming up with a formula that evaluates a single column array for a string and creates a list of found cells. In the adjacent cell to that found cell, I would like to return the row number that contained the identified cells.

 

[Peter_SSs]

What about a small set of sample dummy data and the expected results with XL2BB and explain again in relation to the sample data. 10-20 rows should be ample to show any variety in your data?

 

[molbio]

I'm having a devil of a time coming up with a formula that evaluates a single column array for a string and creates a list of found cells. In the adjacent cell to that found cell, I would like to return the row number that contained the identified cells.

xl2bb test data.xlsx
	A	B	C	D
1	Blah, blah, blah 1		sold a total of 24 items	8
2	Blah, blah, blah 2		sold a total of 56 items	12
3	Blah, blah, blah 3		sold a total of 37 items	18
4	100		sold a total of 18 items	25
5	350
6	Blah, blah, blah 4
7	John Doe
8	sold a total of 24 items
9	Blah, blah, blah 5
10	Blah, blah, blah 6
11	Bambi
12	sold a total of 56 items
13	Blah, blah, blah 7
14	3405
15	Blah, blah, blah 8
16	Blah, blah, blah 9
17	Dasie Mae
18	sold a total of 37 items
19	Blah, blah, blah 10
20	Blah, blah, blah 11
21	Blah, blah, blah 13
22	Blah, blah, blah 14
23	234
24	John Doe
25	sold a total of 18 items
Sheet1

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=MATCH(C1,$A$1:$A$25,0)

 

[molbio]

This data is quite unstructured In my particular case the string is " sold a total of " and I want a list of those cells that contain that string. I also need to identify the row number where the string was found.

 

[Fluff]

ok, how about

Fluff.xlsm
	A	B	C	D
1	Blah, blah, blah 1		sold a total of 24 items	8
2	Blah, blah, blah 2		sold a total of 56 items	12
3	Blah, blah, blah 3		sold a total of 37 items	18
4	100		sold a total of 18 items	25
5	350
6	Blah, blah, blah 4
7	John Doe
8	sold a total of 24 items
9	Blah, blah, blah 5
10	Blah, blah, blah 6
11	Bambi
12	sold a total of 56 items
13	Blah, blah, blah 7
14	3405
15	Blah, blah, blah 8
16	Blah, blah, blah 9
17	Dasie Mae
18	sold a total of 37 items
19	Blah, blah, blah 10
20	Blah, blah, blah 11
21	Blah, blah, blah 13
22	Blah, blah, blah 14
23	234
24	John Doe
25	sold a total of 18 items
Data

Cell Formulas
Range	Formula
C1:C4	C1	=FILTER(A1:A25,ISNUMBER(SEARCH("sold a total",A1:A25)))
D1:D4	D1	=MATCH(C1#,$A$1:$A$25,0)
Dynamic array formulas.

 

[Peter_SSs]

In case there might be multiple rows with the same total sold, perhaps this?

22 08 12.xlsm
	A	B	C	D
1	Blah, blah, blah 1		sold a total of 24 items	8
2	Blah, blah, blah 2		sold a total of 56 items	12
3	Blah, blah, blah 3		sold a total of 37 items	18
4	100		sold a total of 24 items	25
5	350
6	Blah, blah, blah 4
7	John Doe
8	sold a total of 24 items
9	Blah, blah, blah 5
10	Blah, blah, blah 6
11	Bambi
12	sold a total of 56 items
13	Blah, blah, blah 7
14	3405
15	Blah, blah, blah 8
16	Blah, blah, blah 9
17	Dasie Mae
18	sold a total of 37 items
19	Blah, blah, blah 10
20	Blah, blah, blah 11
21	Blah, blah, blah 13
22	Blah, blah, blah 14
23	234
24	John Doe
25	sold a total of 24 items
Sold

Cell Formulas
Range	Formula
C1:D4	C1	=LET(rws,IF(ISNUMBER(SEARCH("sold a total",A1:A25)),ROW(A1:A25),""),idx,FILTER(rws,rws<>""),CHOOSE({1,2},INDEX(A:A,idx),idx))
Dynamic array formulas.

 

[molbio]

In case there might be multiple rows with the same total sold, perhaps this?

22 08 12.xlsm
	A	B	C	D
1	Blah, blah, blah 1		sold a total of 24 items	8
2	Blah, blah, blah 2		sold a total of 56 items	12
3	Blah, blah, blah 3		sold a total of 37 items	18
4	100		sold a total of 24 items	25
5	350
6	Blah, blah, blah 4
7	John Doe
8	sold a total of 24 items
9	Blah, blah, blah 5
10	Blah, blah, blah 6
11	Bambi
12	sold a total of 56 items
13	Blah, blah, blah 7
14	3405
15	Blah, blah, blah 8
16	Blah, blah, blah 9
17	Dasie Mae
18	sold a total of 37 items
19	Blah, blah, blah 10
20	Blah, blah, blah 11
21	Blah, blah, blah 13
22	Blah, blah, blah 14
23	234
24	John Doe
25	sold a total of 24 items
Sold

Cell Formulas
Range	Formula
C1:D4	C1	=LET(rws,IF(ISNUMBER(SEARCH("sold a total",A1:A25)),ROW(A1:A25),""),idx,FILTER(rws,rws<>""),CHOOSE({1,2},INDEX(A:A,idx),idx))
Dynamic array formulas.

You are quite correct in the supposition that duplicate entries will occur. Though I am having difficulty sussing out how the syntax of the formula parses across the two columns, nevertheless, it works beautifully, elegantly. Thank you so much for all your effort.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.