Attributing a group to a number.

[serge]

Hi,


I have 39 numbers that are divided into 6 groups of 7 numbers, ( Group 6 only have 4 numbers ).


Group 1 = 1,2,3,4,5,6,7.
Group 2 = 8,9,10,11,12,13,14.
Group 3 = 15,16,17,18,19,20,21.
Group 4 = 22,23,24,25,26,27,28.
Group 5 = 29,30,31,32,33,34,35
Group 6 = 36,37,38,39.


I'm not concidering the Left digit 1,2 and 3 only the digit on the right.


I would like the formula to attribute the group first and the digit last.


For example :


If I have this draw of 5 numbers starting in D6:H6 : 8 27 32 35 38
I would need the formula to return this in J6:N6 : 28 47 52 55 68


Another :


If I have this draw of 5 numbers starting in D7:H7 : 3 14 24 26 30
I would need the formula to return this in J7:N7 : 13 24 44 46 50

Thank you.

 

[Trebor76]

Hi serge,

This is what I did to get the desired result:

• In cell A1 entered the text "Group No."
• In cell B1 entered the text "Value"
• In cells B2:B40 entered the values 1 to 39
• In cells A2:A40 entered the group number associated with each number
• In cell J6 entered the formula...
=INDEX($A:$A,MATCH(D6,$B:$B,0))&RIGHT(D6,1)
...and copied it across cells K6 to N6
• Copied the formula in cells J6:N6 down as required.

Hope that helps,

Robert

 

[serge]

Hi Trebor76,

Thank you for responding to my post.

Im not sure I understand, when you say :

• In cell A1 entered the text "Group No."
• In cell B1 entered the text "Value"

 

[serge]

I got it.

Thank you very much Trebor76, It work very well I appreciate the help.
Serge.

 

[Trebor76]

They were just the headings I used. I'm glad we got it sorted