Positions

[alliswell]

I have names in column A, in H1 is the name i want to find in column A for example : in H1 is John i want to list all john with their position number in Column A

Thanks

 

[awoohaw]

If you click the link it will take you to Techonthenet's description of the Search function which lists all the main versions of Excel it was available in.

Microsoft only supports versions of standalone Excel for a certain time period, the same as they do with versions of Windows

Now I understand what you mean. It's ashame that microsoft doesn't want to keep an archive of sorts on their products, though. They are missing out on ad revenue clicks.

 

[awoohaw]

@Peter_SSs ,
I do not see an applies to link on that help page (the one in the second image above). I searched for the word "applies" and it was not found.

 

[MARK858]

do not see an applies to link on that help page

 

[awoohaw]

View attachment 109244

what link is that image from? (The techonthenet?)

 

[MARK858]

The Techonthenet link from post 7

 

[awoohaw]

So, after all that. here is the original version that I had edited out. I'm sure there are more succinct formulas. But, this seems to work.
And should be entered with the CNTR-SHFT-ENTR (CSE) keystroke.

Book1
	A	B					G	H	I
1								John	WILson
2	George Johnson							1	2
3	Sally Wilson							3	4
4	George Johnson							12	6
5	Sally Wilson							14	13
6	Sally Jones							16
7	George Wilson							17
8	George Smith							18
9	George Jackson							19
10	George Smith							20
11	Sally Jackson							21
12	George Jones							23
13	John Jackson							24
14	George Wilson
15	George Johnson
16	George Smith
17	John Johnson
18	John Jackson
19	Sally Johnson
20	George Johnson
21	John Smith
22	John Smith
23	George Jones
24	John Jackson
25	Sally Johnson
Sheet1

Cell Formulas
Range	Formula
H2:H25	H2	=IF((ROW()-1)>SUM(--(ISNUMBER(SEARCH($H$1,$A$2:$A$25)))),"", SMALL(IFERROR((ROW($A$2:$A$25)-1)*(ROW($A$2:$A$25)-1)/(ISNUMBER(SEARCH($H$1,$A$2:$A$25))*(ROW($A$2:$A$25)-1)),2^100), ROW()-1))
I2:I25	I2	=IF((ROW()-1)>SUM(--(ISNUMBER(SEARCH($I$1,$A$2:$A$25)))),"", SMALL(IFERROR((ROW($A$2:$A$25)-1)*(ROW($A$2:$A$25)-1)/(ISNUMBER(SEARCH($I$1,$A$2:$A$25))*(ROW($A$2:$A$25)-1)),2^100), ROW()-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

i want to find in column A for example : in H1 is John i want to list all john with their position number in Column A

I'm assuming that "John" would need to be a stand-alone word, not part of a larger word like "Johnston". If that is the case, try this.

24 04 01.xlsm
	A						G	H	I
1								John	Wilson
2	George Johnson							13	3
3	Sally Wilson							17	5
4	George Johnson							18	7
5	Sally Wilson							21	14
6	Sally Jones							22	25
7	George Wilson							24
8	George Smith
9	George Jackson
10	George Smith
11	Sally Jackson
12	George Jones
13	John Jackson
14	George Wilson
15	George Johnson
16	George Smith
17	John Johnson
18	John Jackson
19	Sally Johnson
20	George Johnson
21	John Smith
22	John Smith
23	George Jones
24	John Jackson
25	Wilson Johnson
26
Sheet2 (2)

Cell Formulas
Range	Formula
H2:I9	H2	=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&H$1&" "," "&$A$1:$A$25&" ")),ROW($A$1:$A$25)),ROWS(H$2:H2)),"")

 

[alliswell]

I'm assuming that "John" would need to be a stand-alone word, not part of a larger word like "Johnston". If that is the case, try this.

24 04 01.xlsm
	A						G	H	I
1								John	Wilson
2	George Johnson							13	3
3	Sally Wilson							17	5
4	George Johnson							18	7
5	Sally Wilson							21	14
6	Sally Jones							22	25
7	George Wilson							24
8	George Smith
9	George Jackson
10	George Smith
11	Sally Jackson
12	George Jones
13	John Jackson
14	George Wilson
15	George Johnson
16	George Smith
17	John Johnson
18	John Jackson
19	Sally Johnson
20	George Johnson
21	John Smith
22	John Smith
23	George Jones
24	John Jackson
25	Wilson Johnson
26
Sheet2 (2)

Cell Formulas
Range	Formula
H2:I9	H2	=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&H$1&" "," "&$A$1:$A$25&" ")),ROW($A$1:$A$25)),ROWS(H$2:H2)),"")

Yes peter SSs correct. Thanks

And thanks to other experts too.

 

[Peter_SSs]

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