# Position of values

#### Starrbuckk

##### New Member
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.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### MickG

##### MrExcel MVP
If you can work with a bit of code , this may do !!
NB:- Your Data in row 1 starting "A1", Results start "A3"
Code:
``````[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

##### Well-known Member
Are they in separate columns or is it just one value like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">XXXZZZZXXZZXX</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">12</td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">=IFERROR(<font color="Blue">FIND(<font color="Red">"X",\$A\$1,B2+1</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=IFERROR(<font color="Blue">FIND(<font color="Red">"Z",\$A\$1,A3</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Separate columns like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">X</td><td style=";">X</td><td style=";">X</td><td style=";">Z</td><td style=";">Z</td><td style=";">Z</td><td style=";">Z</td><td style=";">X</td><td style=";">X</td><td style=";">Z</td><td style=";">Z</td><td style=";">X</td><td style=";">X</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">12</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">=IFERROR(<font color="Blue">MATCH(<font color="Red">"X",OFFSET(<font color="Green">\$A\$1:\$M\$1,0,B2+0</font>),0</font>)+B2,""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=IFERROR(<font color="Blue">MATCH(<font color="Red">"Z",OFFSET(<font color="Green">\$A\$1:\$M\$1,0,A3</font>),0</font>)+A3,""</font>)</td></tr></tbody></table></td></tr></table><br />

WBD

#### Starrbuckk

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

AB
1XXXZZZZXXZZXX
2
314
4810
512

</tbody>
Sheet1

Worksheet Formulas
CellFormula
A3=IFERROR(FIND("X",\$A\$1,B2+1),"")
B3=IFERROR(FIND("Z",\$A\$1,A3),"")

</tbody>

<tbody>
</tbody>

Separate columns like this:

ABCDEFGHIJKLM
1XXXZZZZXXZZXX
2
314
4810
512

</tbody>
Sheet2

Worksheet Formulas
CellFormula
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,"")

</tbody>

<tbody>
</tbody>

WBD

Thank you kind Sir,

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

Thanks again.

Replies
13
Views
249
Replies
26
Views
1K
Replies
0
Views
25
Replies
6
Views
101
Replies
3
Views
49