Group data (values 0-13) into 5 groups

[London123]

Hi,

I have an excel with many rows so I can't do this manually.

In one of the columns there is a value of 0- 13 for each row. I would like to create another column to group these values into five groups -
1: 11-13
2: 7-10
3: 3-6
4: 1-2
5: 0

ID	Score
342	7
344	5
345	2
346	1

<colgroup><col span="3"></colgroup><tbody>
</tbody>

 

[shg]

Not sure I understand your question. Maybe this:

	A​	B​	C​
1​	Score​	Group​	​
2​	9​	7-10	B2: =LOOKUP(A2, {0,1,3,7,11}, {"0","1-2","3-6","7-10","11-13"})
3​	6​	3-6
4​	7​	7-10
5​	4​	3-6
6​	5​	3-6
7​	11​	11-13
8​	2​	1-2
9​	6​	3-6
10​	8​	7-10
11​	8​	7-10
12​	1​	1-2
13​	5​	3-6
14​	7​	7-10
15​	1​	1-2
16​	10​	7-10
17​	0​	0
18​	2​	1-2
19​	10​	7-10
20​	3​	3-6
21​	4​	3-6
22​	6​	3-6
23​	10​	7-10
24​	0​	0

 

[cooper645]

Hi there.

My way of tackling this has been from a VBA side.

I have assumed your Scores are in Column B.
The code then inserts a blank column C and calculates which Group each row is,
it then sorts the groups from 1 to 5 in order and also the scores in column B, Desending.

If this is not what you were after I apologise, and would be happy to amend code to suit.
My method

Sub Group()'Sort into groups Col B
'Gp 1 - 11 to 13
'Gp 2 - 7 to 10
'Gp 3 - 3 to 6
'Gp 4 - 1 to 2
'Gp 5 - 0


Dim LastRow As Long


LastRow = Cells(Rows.Count, "B").End(xlUp).Row


'Insert column C for group sort
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


For i = 2 To LastRow
    If Cells(i, 3).Offset(0, -1).Value = 0 Then
        Cells(i, 3).Value = "Gp 5"
    ElseIf Cells(i, 3).Offset(0, -1).Value > 0 And Cells(i, 3).Offset(0, -1).Value < 3 Then
        Cells(i, 3).Value = "Gp 4"
     ElseIf Cells(i, 3).Offset(0, -1).Value > 2 And Cells(i, 3).Offset(0, -1).Value < 7 Then
        Cells(i, 3).Value = "Gp 3"
    ElseIf Cells(i, 3).Offset(0, -1).Value > 6 And Cells(i, 3).Offset(0, -1).Value < 11 Then
        Cells(i, 3).Value = "Gp 2"
    ElseIf Cells(i, 3).Offset(0, -1).Value > 10 And Cells(i, 3).Offset(0, -1).Value < 14 Then
        Cells(i, 3).Value = "Gp 1"
    End If
Next i


'Sort by Column C then B
Range("C2").CurrentRegion.Select
     Selection.sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Range("B2").CurrentRegion.Select
     Selection.sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
          


End Sub