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
XXXZZZZXXZZXX

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=IFERROR(FIND("X",$A$1,B2+1),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IFERROR(FIND("Z",$A$1,A3),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Separate columns like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M
X	X	X	Z	Z	Z	Z	X	X	Z	Z	X	X

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=IFERROR(MATCH("X",OFFSET($A$1:$M$1,0,B2+0),0)+B2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IFERROR(MATCH("Z",OFFSET($A$1:$M$1,0,A3),0)+A3,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

Thank you kind Sir,

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

Thanks again.