Finding 1st and last occurrence

[michaelsmith559]

I am trying to find a formula/code that will find the 1st and last occurrence of the 2nd value. Needs to be easily changed for 3rd, 4th, 5th values as well. Below is what I am looking to accomplish. The formulas should be able to be copied down. The values it will be looking for in the 2nd position will be from 2 to 67. Thanks for any help.

mm combinations.xlsm
	X	Y	Z	AA	AB
1	1	1, 2, 3, 4, 5	Value	Start Row	End Row
2	50116	1, 2, 68, 69, 70	2	1	50116
3	50117	1, 3, 4, 5, 6	3	50117	98021
4	98021	1, 3, 68, 69, 70
Sheet2

 

[awoohaw]

please update your profile button to show what version of Excel you are using?

 

[michaelsmith559]

please update your profile button to show what version of Excel you are using?

2013

 

[michaelsmith559]

please update your profile button to show what version of Excel you are using?

I'm not sure how to do that. I'm using 2013.

 

[awoohaw]

will the look for numbers ever repeat in the same cell?

 

[michaelsmith559]

will the look for numbers ever repeat in the same cell?

No, no values repeat twice.

 

[awoohaw]

okay. can there be helper columns? With 2013 there is no way I know of that can treat the contents of a cell like an array.
It can be done with VBA probably, but I am not experienced in that.

 

[michaelsmith559]

Here is a link where I used sumproduct for the 1 digit. I've tried adjusting it to count the 2nd digit but it gives wrong results.

Help fixing Sumproduct formula

Need help fixing the sumproduct formula. I'm trying to get it to use the values in column N but I've tried a couple of different ways and I get a #value error or I manually have to change the reference in the formula to the value shown in column N when it becomes a double digit such as 10...

www.mrexcel.com

 

[awoohaw]

i'm not sure sumproduct works on values separated by commas to treat them as numeric. Can you use helper columns?

 

[Fluff]

How about in AA2

=INDEX(X:X,AGGREGATE(15,6,ROW($X$1:$X$4)/(REPLACE(MID($Y$1:$Y$4,1,FIND(",",$Y$1:$Y$4,4)-1),1,FIND(" ",$Y$1:$Y$4),"")=Z2&""),1))

and in AB2

=INDEX(X:X,AGGREGATE(14,6,ROW($X$1:$X$4)/(REPLACE(MID($Y$1:$Y$4,1,FIND(",",$Y$1:$Y$4,4)-1),1,FIND(" ",$Y$1:$Y$4),"")=Z2&""),1))