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

[TABLE="width: 195"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 2"]ID [/TD]
[TD]Score [/TD]
[/TR]
[TR]
[TD]342 [/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]344[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]346[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Not sure I understand your question. Maybe this:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Score​

[/td][td="bgcolor:#F3F3F3"]

Group​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

9​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td]B2: =LOOKUP(A2, {0,1,3,7,11}, {"0","1-2","3-6","7-10","11-13"})[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

6​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

7​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

11​

[/td][td="bgcolor:#E5E5E5"]11-13[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]1-2[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

6​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

8​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

8​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]1-2[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

7​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]1-2[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]0[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]1-2[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

6​

[/td][td="bgcolor:#E5E5E5"]3-6[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]7-10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]0[/td][td][/td][/tr]
[/table]

 

[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