Any help would be appreciated. I currently have a drop down menu, 1-12, and a list or column of names that I would like excel to number (in column B) depending on the selected drop down. For example, I select 5 from the dropdown menu. Next to the first column (Column B) , I would like it to number in the following order: 1,2,3,4,5,2,3,4,5,1,3,4,5,1,2 ... until all the names from column A are numbered. Does anyone have an idea on how to do this? Or the Formula I would use? Thanks in advance.
Can you explain more in detail the sequence, because name "snake order" more often means 1 2 3 4 5 5 4 3 2 1 1 2 3 and so on.
Best Regards,
Kaper
sure, So in my column A, I will have say 100 accounts. From my drop down menu select I will put it in 5 groups. I want it to order or number like this:
Ex with 5
A - 1
B - 2
C - 3
D - 4
E - 5
F - 2
G - 3
H - 4
I - 5
J - 1
K - 3
L - 4
M - 5
N - 1
O - 2
P - 4
Q - 5
...
I may have used the wrong term with "snake order." I just need it to order in a pattern such as above with the selected drop down menu. If it is 4, then I need the pattern to be 1,2,3,4,2,3,4,1,3,4,1,2...
Thanks in advance for any help.
if your drop down is connected with cell F1, write in B2 (or wherever you want to start):
=TRUNC(MOD((ROW(1:1)-1)*(F$1+1)/F$1,F$1)+1)
and copy down.
the key to the trick is multiplying row number (1, 2, 3 etc.) by
(n+1)/n
if you (one) wonder how it works - start with
=ROW(1:1)-1
then add next steps - watch this one in particular:
=(ROW(1:1)-1)*(F$1+1)/F$1
and then
=MOD((ROW(1:1)-1)*(F$1+1)/F$1,F$1)
PS. Typical snake formula would be:
=MIN(MOD(ROW(1:1)-1,2*F$1)+1,2*F$1-MOD(ROW(1:1)-1,2*F$1))
Again - max in F1. Formula anywhere and copy down.
Last edited by Kaper; 02-11-2014 at 01:01 PM.
Worked perfectly. Thank you for your help, this has been giving me major trouble
=TRUNC(MOD((ROW(1:1)-1)*(F$1+1)/F$1,F$1)+1)
What in the world is the purpose of a sequence like that, Kaper?
Entia non sunt multiplicanda sine necessitate
Hi shg
If you are asking about excel side - I am ready to explain,
but if about merit - good question, but rather not to me but to Shudodger
basically for any n it produces
1
2
.
.
.
n-1
n
2
3
.
.
n-1
n
1
3
4
.
.
n
1
2
....
n-1
n
1
.
.
n-3
n-2
etc.
I understand the formula, I just can't imagine a purpose.
wouldnt
=MOD(ROW(A1)-1,$G$1)+1 where g1 is dropdown and formula put in first row of data in col b do?
Last edited by martindwilson; 02-11-2014 at 02:21 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thank you Kaper, it is exactly what I needed.
Hi martindwilson,
I wouldn't say your formula would do.
It is just returning the same sequence without shift after each full set.
Write 1 in B2
Then formula for B3:
and copy down.
Please Login or Register to view this content.
Ok i re read the original and see what you mean sems a strange requirement tho
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks