excel grouping

[irvgotti]

hi

say i have a column with values between 1 and 20, now i want to group these values in the next column in the following way, group 1 are values 1,6,11,16 group 2 are values 2,7,12,17, group 3 values 3,8,13,18, group 4 values 4,9,14,19, and group 5, values 5,10,15,20, how could i do this

1
2
4
7
9
4
3
15
16
11
etc

[Jonmo1]

Try this in B1 to retrieve A1 A6 A11 A16

=INDEX($A$1:$A$20,((ROWS(A$1:A1)-1)*5+1)+(COLUMNS($A1:A1)-1))
Fill it Down/Right as far as needed.

$A$1:$A$20 is the source list
DO NOT change the A1:A1 references, they are not related to your data.

[irvgotti]

hi

say i have a column with values between 1 and 20, now i want to group these values in the next column in the following way, group 1 are values 1,6,11,16 group 2 are values 2,7,12,17, group 3 values 3,8,13,18, group 4 values 4,9,14,19, and group 5, values 5,10,15,20, how could i do this
----- group(formula needed to this)
1 ---- 1
2 ---- 2
4 ---- 4
7 ---- 2
9 ---- 4
4 ---- 4
3 ---- 3
15 ---- 5
16 ---- 1
11 ---- 1
etc ---- etc

i am trying to do this with excels LOOKUP function but cant quite figure it out, any help would be appreciated thanks

[Jonmo1]

The example you're posting is not making any sense.
Can you post a sample book with expected results?

[irvgotti]

Column A (data values points that range between 1 and 20)
Column B (and want to group these values into 1 of 5 groups as indicated above) here they are again: group 1 are data points (1,6,11,16) group 2 (2,7,12,17) group 3 numbers (3,8,13,18) group 4 (4,9,14,19)and group 5 (5,10,15,20)

Column A (data values)--------Column B( groupings - formula needed for this )

4--------------------------------group 4 (i do not need it to display group just 4 )
16-------------------------------group 1
5--------------------------------group 5
20------------------------------group 5
17 ----------------------------group 2
8 ------------------------------group 3
8 ------------------------------group 3
9 ------------------------------group 4
etc (hundreds of data points)

I have tried =If(and(A1=1,6,11,16),1,if(and(a1=2,7,12,17),2,if(and(a1=3,8,13,18),3, etc - but this did not work
hope my example makes more sense now

[Jonmo1]

Try

=MOD(A1-1,5)+1

[irvgotti]

thank you so much for that
could u explain the formula if you have time, i dont understand how its working, but thanks again

[Jonmo1]

MOD is the remainder after a division.

Take
=MOD(9,2)
2 can be devided into 9 4 times.
so 2*4 = 8
the remainder is 1 (9-8=1)

=MOD(17,3)
3 can be devided into 17 5 times
3*5 = 15
the remainder is 2 (17-15=2)

So that's what MOD does
If the 2nd number is LARGER than the 1st number, then Mod just returns the 1st number.

Here's an example book with that broken out into 3 seperate formulas so you can see what's going on.
ModGroups.xlsx