Hi,
This is driving me nuts! I would like to pull the first 5-8 index values from a column based on random position of names. The values are randomly spaced including two in a row sometimes.
Column A:
Name1
Name1
Name1
Name3
Name2
Name1
Name2
Name3
Name3
Name2
Name2
Name1
Name1
Name2
Name2
Name1
Name1
Name1
Name3
Name2
Name1
Name2
Name3
Name3
Name2
Name2
Name1
Name1
Name2
Name2
Name1
Name1
I would like to get the index values for Name1, Name2, Name3. The index values for Name1 should be 1,2,3,6,12,13,17,16,18,21,27,28,31,32. The index values for Name2 should be 5,7,10,11,14,15,20,22,25,26,29,30. The index values for Name 3 should be 4,8,9,19,23,24.
I have tried the following column B formula: =MATCH("Name1",A:A,0) for the first row (B1) and =IFERROR(MATCH("Name1",OFFSET($A$1:$A$20,MATCH("Name2",A:A,0)+B1-1,0),0)+B1+1,"No Data") for the subsequent rows (B2, B3, B4) where the B1 is stepped to B2 in cell B3 and so on..
I get whacky answers depending on the position of the found name. I can not get the series right for all instances.
Please help!
Andrew_Step
Bookmarks