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

molbio

New Member
Joined
Apr 6, 2015
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?
 
Upvote 0
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
ABCD
1Blah, blah, blah 1 sold a total of 24 items8
2Blah, blah, blah 2 sold a total of 56 items12
3Blah, blah, blah 3 sold a total of 37 items18
4100 sold a total of 18 items25
5350
6Blah, blah, blah 4
7John Doe
8 sold a total of 24 items
9Blah, blah, blah 5
10Blah, blah, blah 6
11Bambi
12 sold a total of 56 items
13Blah, blah, blah 7
143405
15Blah, blah, blah 8
16Blah, blah, blah 9
17Dasie Mae
18 sold a total of 37 items
19Blah, blah, blah 10
20Blah, blah, blah 11
21Blah, blah, blah 13
22Blah, blah, blah 14
23234
24John Doe
25 sold a total of 18 items
Sheet1
 
Last edited by a moderator:
Upvote 0
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
Excel Formula:
=MATCH(C1,$A$1:$A$25,0)
 
Upvote 0
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.
 
Upvote 0
ok, how about
Fluff.xlsm
ABCD
1Blah, blah, blah 1 sold a total of 24 items8
2Blah, blah, blah 2 sold a total of 56 items12
3Blah, blah, blah 3 sold a total of 37 items18
4100 sold a total of 18 items25
5350
6Blah, blah, blah 4
7John Doe
8 sold a total of 24 items
9Blah, blah, blah 5
10Blah, blah, blah 6
11Bambi
12 sold a total of 56 items
13Blah, blah, blah 7
143405
15Blah, blah, blah 8
16Blah, blah, blah 9
17Dasie Mae
18 sold a total of 37 items
19Blah, blah, blah 10
20Blah, blah, blah 11
21Blah, blah, blah 13
22Blah, blah, blah 14
23234
24John Doe
25 sold a total of 18 items
Data
Cell Formulas
RangeFormula
C1:C4C1=FILTER(A1:A25,ISNUMBER(SEARCH("sold a total",A1:A25)))
D1:D4D1=MATCH(C1#,$A$1:$A$25,0)
Dynamic array formulas.
 
Upvote 0
In case there might be multiple rows with the same total sold, perhaps this?

22 08 12.xlsm
ABCD
1Blah, blah, blah 1 sold a total of 24 items8
2Blah, blah, blah 2 sold a total of 56 items12
3Blah, blah, blah 3 sold a total of 37 items18
4100 sold a total of 24 items25
5350
6Blah, blah, blah 4
7John Doe
8 sold a total of 24 items
9Blah, blah, blah 5
10Blah, blah, blah 6
11Bambi
12 sold a total of 56 items
13Blah, blah, blah 7
143405
15Blah, blah, blah 8
16Blah, blah, blah 9
17Dasie Mae
18 sold a total of 37 items
19Blah, blah, blah 10
20Blah, blah, blah 11
21Blah, blah, blah 13
22Blah, blah, blah 14
23234
24John Doe
25 sold a total of 24 items
Sold
Cell Formulas
RangeFormula
C1:D4C1=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.
 
Upvote 0
Solution
In case there might be multiple rows with the same total sold, perhaps this?

22 08 12.xlsm
ABCD
1Blah, blah, blah 1 sold a total of 24 items8
2Blah, blah, blah 2 sold a total of 56 items12
3Blah, blah, blah 3 sold a total of 37 items18
4100 sold a total of 24 items25
5350
6Blah, blah, blah 4
7John Doe
8 sold a total of 24 items
9Blah, blah, blah 5
10Blah, blah, blah 6
11Bambi
12 sold a total of 56 items
13Blah, blah, blah 7
143405
15Blah, blah, blah 8
16Blah, blah, blah 9
17Dasie Mae
18 sold a total of 37 items
19Blah, blah, blah 10
20Blah, blah, blah 11
21Blah, blah, blah 13
22Blah, blah, blah 14
23234
24John Doe
25 sold a total of 24 items
Sold
Cell Formulas
RangeFormula
C1:D4C1=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.
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top