Counting locations for the first time only

[montecarlo2012]

Hello.
Where is the numbers of the column H at in the last time

Taking the B2:F2 as the last line.

And the counter will display the results on I

The numbers to be search are on column H

So for example number 1 is founded at number 9 position

For the first time, that’s all I want, no the second time



I would like this with any set of numbers.

To avoid complications I didn’t use the real data, instead

Letters are used.

Book1
	A	B	C	D	E	F	G	H	I
1
2	IMPACT LINE	X	X	X	X	X		1	9
3		9	x	x	x	x		2	8
4		9	8	x	x	x		3	7
5		x	8	7	x	x		4	6
6		x	x	7	6	x		5	5
7		x	x	x	6	5		6	4
8		x	x	x	4	5		7	3
9		x	x	3	4	x		8	2
10		x	2	3	x	x		9	1
11		1	2	x	x	x
12		1	N	N	N	N
13
Sheet1

 

[Automatrix]

Not pretty, but it works, provided your Excel version can use array formulas.

Book1
	A	B	C	D	E	F	G	H	I
2	IMPACT LINE	X	X	X	X	X
3		9	x	x	x	x		1	9
4		9	8	x	x	x		2	8
5		x	8	7	x	x		3	7
6		x	x	7	6	x		4	6
7		x	x	x	6	5		5	5
8		x	x	x	4	5		6	4
9		x	x	3	4	x		7	3
10		x	2	3	x	x		8	2
11		1	2	x	x	x		9	1
12		1	N	N	N	N
Sheet5 (2)

Cell Formulas
Range	Formula
I3:I11	I3	=MATCH("*"&H3&"*",$B$3:$B$12&$C$3:$C$12&$D$3:$D$12&$E$3:$E$12&$F$3:$F$12,0)

Otherwise, this even less pretty alternative will work too.

Book1
	A	B	C	D	E	F	G	H	I
2	IMPACT LINE	X	X	X	X	X		1	9
3		9	x	x	x	x		2	8
4		9	8	x	x	x		3	7
5		x	8	7	x	x		4	6
6		x	x	7	6	x		5	5
7		x	x	x	6	5		6	4
8		x	x	x	4	5		7	3
9		x	x	3	4	x		8	2
10		x	2	3	x	x		9	1
11		1	2	x	x	x
12		1	N	N	N	N
Sheet5

Cell Formulas
Range	Formula
I2:I10	I2	=MIN(IFNA(MATCH($H2,B$3:B$12,0),99),IFNA(MATCH($H2,C$3:C$12,0),99),IFNA(MATCH($H2,D$3:D$12,0),99),IFNA(MATCH($H2,E$3:E$12,0),99),IFNA(MATCH($H2,F$3:F$12,0),99))

 

[AhoyNC]

Another option.

Book1
	A	B	C	D	E	F	G	H	I
1
2	IMPACT LINE	X	X	X	X	X		1	9
3		9	x	x	x	x		2	8
4		9	8	x	x	x		3	7
5		x	8	7	x	x		4	6
6		x	x	7	6	x		5	5
7		x	x	x	6	5		6	4
8		x	x	x	4	5		7	3
9		x	x	3	4	x		8	2
10		x	2	3	x	x		9	1
11		1	2	x	x	x
12		1	N	N	N	N
Sheet1

Cell Formulas
Range	Formula
I2:I10	I2	=AGGREGATE(15,6,(ROW($B$3:$B$12)-ROW($B$3)+1)/SEARCH(H2,$B$3:$F$12),1)

 

[montecarlo2012]

Automatrix and AhoyNC, thank you so much for your time, and of course the formulas work, I really appreciate your time, the thing is I am expecting a code.
I tried something like this

Sub distance()
With Range("B2:F12")
    Range("I2:I10").FormulaArray = "=AGGREGATE(15,6,(ROW($B$3:$B$12)-ROW($B$3)+1)/SEARCH(H2,$B$3:$F$12),1)"
  End With 
 End Sub

But, does not work, I am still waiting.
The other point is, my real data is different workbooks, and different sets of numbers, the reason why I rather code.
Thank you