Position of values

Starrbuckk

New Member
Joined
Mar 24, 2016
Messages
19
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.
 

Some videos you may like

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
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Jun 2, 2016
Messages
3,401
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
Joined
Mar 24, 2016
Messages
19
Are they in separate columns or is it just one value like this:

AB
1XXXZZZZXXZZXX
2
314
4810
512

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

Worksheet Formulas
CellFormula
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:

ABCDEFGHIJKLM
1XXXZZZZXXZZXX
2
314
4810
512

<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
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,"")

<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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,988
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top