Position of values

[Starrbuckk]

Hi,

I need to find the position of 2 values, lets call them "X" and "Z", on an array.
I've got a board that repeats X and Z multiple times, and what i wanted is the column position of the first time X presents itself on the Row im looking, then the first time Z presents itself on the same row, then i want the second time X presents itself again after Z, and the same thing for Z.

Ex.

XXXZZZZXXZZXX

X=1
Z=4
X=8
Z=10
X=11

So i was thinking i need some formula that changed the array according to the last position found(Array column+1/4/8) but maybe theres a simpler way.

Thanks in advance.

 

[MickG]

If you can work with a bit of code , this may do !!
NB:- Your Data in row 1 starting "A1", Results start "A3"

[COLOR="Navy"]Sub[/COLOR] MG09May14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, xRng [COLOR="Navy"]As[/COLOR] Range, yRng [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
Rw = 2
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn.Value = temp [COLOR="Navy"]Then[/COLOR]
        Rw = Rw + 1
        Cells(Rw, 1) = Dn.Value: Cells(Rw, 2) = Dn.Column
    [COLOR="Navy"]End[/COLOR] If
temp = Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[wideboydixon]

Are they in separate columns or is it just one value like this:

Book1
	A	B
1	XXXZZZZXXZZXX
2
3	1	4
4	8	10
5	12
Sheet1

Cell Formulas
Range	Formula
A3	=IFERROR(FIND("X",$A$1,B2+1),"")
B3	=IFERROR(FIND("Z",$A$1,A3),"")

Separate columns like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	X	X	X	Z	Z	Z	Z	X	X	Z	Z	X	X
2
3	1	4
4	8	10
5	12
Sheet2

Cell Formulas
Range	Formula
A3	=IFERROR(MATCH("X",OFFSET($A$1:$M$1,0,B2+0),0)+B2,"")
B3	=IFERROR(MATCH("Z",OFFSET($A$1:$M$1,0,A3),0)+A3,"")

WBD

 

[Starrbuckk]

Are they in separate columns or is it just one value like this:

	A	B
1	XXXZZZZXXZZXX
2
3	1	4
4	8	10
5	12

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A3 =IFERROR(FIND("X",$A$1,B2+1),"") B3 =IFERROR(FIND("Z",$A$1,A3),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Separate columns like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	X	X	X	Z	Z	Z	Z	X	X	Z	Z	X	X
2
3	1	4
4	8	10
5	12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A3 =IFERROR(MATCH("X",OFFSET($A$1:$M$1,0,B2+0),0)+B2,"") B3 =IFERROR(MATCH("Z",OFFSET($A$1:$M$1,0,A3),0)+A3,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WBD

Thank you kind Sir,

I think i can adapt the second solution to what i need.

Thanks again.