I have a worksheet with 2 colums. The rating column will contain 1 of 9
scores. There are about 50 data lines. I need to goup them by that score in
a new worksheet in a block format. I added the example and look of finished
product I need. Can a macro or Excel function make this work?

Data Worksheet:
Rating Name
1A Smith
1A Jerry
2A Jones
3A Daye
1B Wilson
2B Johnson
2B Wint
3B Lager
1C Swith
2C Jackson
3C Cole


Output Worksheet
__________________________________________________ ________________
|All 1C listed here | All 1B listed here | All
1A listed here |
| Swith | Wilson |
Smith |
| | |
Jerry |
|__________________|______________________|_______ ________________|
|All 2C listed here | All 2B listed here | All
2A listed here |
| Jackson | Johnson |
Jones |
| | Wint |
|
|__________________|______________________|_______ ________________|
|All 3C listed here | All 3B listed here | All
3A listed here |
| Cole | Lager |
Daye |
|__________________|______________________|_______ ________________|
--
Trina

Trina
A Pivot Table might work for you. Look it up in Help. If not, the
following macro will do it for you. I chose "Sheet2" as the name of the
second or destination sheet. The sheet that holds the original 2 columns (A
& B in this macro) must be the active sheet when you run this macro. Look
at the Select Case part of the macro. The letters that follow the word
Case are your ratings. Type in your ratings in place of what I have. The
numbers following TheCol= are the column numbers in the second sheet in
which the macro will place the names. A 1 is Column A, a 2 is Column B, and
so on. HTH Otto
Sub ShuffleData()
Dim rColB As Range
Dim i As Range
Dim TheCol As Long
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColB
Select Case i.Offset(, -1).Value
Case "A": TheCol = 1
Case "B": TheCol = 2
Case "C": TheCol = 3
Case "D": TheCol = 4
Case "E": TheCol = 5
Case "F": TheCol = 6
Case "G": TheCol = 7
Case "H": TheCol = 8
Case "I": TheCol = 9
End Select
With Sheets("Sheet2")
.Cells(Rows.Count, TheCol).End(xlUp).Offset(1).Value =
i.Value
End With
Next i
End Sub
"Trina" wrote in message
...
I have a worksheet with 2 colums. The rating column will contain 1 of 9
scores. There are about 50 data lines. I need to goup them by that score
in
a new worksheet in a block format. I added the example and look of
finished
product I need. Can a macro or Excel function make this work?

Data Worksheet:
Rating Name
1A Smith
1A Jerry
2A Jones
3A Daye
1B Wilson
2B Johnson
2B Wint
3B Lager
1C Swith
2C Jackson
3C Cole


Output Worksheet
__________________________________________________ ________________
|All 1C listed here | All 1B listed here |
All
1A listed here |
| Swith | Wilson |
Smith |
| | |
Jerry |
|__________________|______________________|_______ ________________|
|All 2C listed here | All 2B listed here |
All
2A listed here |
| Jackson | Johnson |
Jones |
| | Wint |
|
|__________________|______________________|_______ ________________|
|All 3C listed here | All 3B listed here | All
3A listed here |
| Cole | Lager |
Daye |
|__________________|______________________|_______ ________________|
--
Trina

WOW! This is GREAT. I was able to make it work. I wanted to see if I
chould designate the Select Case locations to be a specifc column and row and
not sure how to modify the code. Can that be done?
--
Trina


"Otto Moehrbach" wrote:

Trina
A Pivot Table might work for you. Look it up in Help. If not, the
following macro will do it for you. I chose "Sheet2" as the name of the
second or destination sheet. The sheet that holds the original 2 columns (A
& B in this macro) must be the active sheet when you run this macro. Look
at the Select Case part of the macro. The letters that follow the word
Case are your ratings. Type in your ratings in place of what I have. The
numbers following TheCol= are the column numbers in the second sheet in
which the macro will place the names. A 1 is Column A, a 2 is Column B, and
so on. HTH Otto
Sub ShuffleData()
Dim rColB As Range
Dim i As Range
Dim TheCol As Long
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColB
Select Case i.Offset(, -1).Value
Case "A": TheCol = 1
Case "B": TheCol = 2
Case "C": TheCol = 3
Case "D": TheCol = 4
Case "E": TheCol = 5
Case "F": TheCol = 6
Case "G": TheCol = 7
Case "H": TheCol = 8
Case "I": TheCol = 9
End Select
With Sheets("Sheet2")
.Cells(Rows.Count, TheCol).End(xlUp).Offset(1).Value =
i.Value
End With
Next i
End Sub
"Trina" wrote in message
...
I have a worksheet with 2 colums. The rating column will contain 1 of 9
scores. There are about 50 data lines. I need to goup them by that score
in
a new worksheet in a block format. I added the example and look of
finished
product I need. Can a macro or Excel function make this work?

Data Worksheet:
Rating Name
1A Smith
1A Jerry
2A Jones
3A Daye
1B Wilson
2B Johnson
2B Wint
3B Lager
1C Swith
2C Jackson
3C Cole


Output Worksheet
__________________________________________________ ________________
|All 1C listed here | All 1B listed here |
All
1A listed here |
| Swith | Wilson |
Smith |
| | |
Jerry |
|__________________|______________________|_______ ________________|
|All 2C listed here | All 2B listed here |
All
2A listed here |
| Jackson | Johnson |
Jones |
| | Wint |
|
|__________________|______________________|_______ ________________|
|All 3C listed here | All 3B listed here | All
3A listed here |
| Cole | Lager |
Daye |
|__________________|______________________|_______ ________________|
--
Trina

Trina
As written you can change the destination column, but the destination
row will always be the next empty row in whatever column it goes into. Tell
me more about what you want when you say "a specific column and row". Give
me a couple of examples. Specifically, tell me how you would determine what
column and row you want as the destination. Otto
"Trina" wrote in message
...
WOW! This is GREAT. I was able to make it work. I wanted to see if I
chould designate the Select Case locations to be a specifc column and row
and
not sure how to modify the code. Can that be done?
--
Trina


"Otto Moehrbach" wrote:

Trina
A Pivot Table might work for you. Look it up in Help. If not, the
following macro will do it for you. I chose "Sheet2" as the name of the
second or destination sheet. The sheet that holds the original 2 columns
(A
& B in this macro) must be the active sheet when you run this macro.
Look
at the Select Case part of the macro. The letters that follow the word
Case are your ratings. Type in your ratings in place of what I have.
The
numbers following TheCol= are the column numbers in the second sheet in
which the macro will place the names. A 1 is Column A, a 2 is Column B,
and
so on. HTH Otto
Sub ShuffleData()
Dim rColB As Range
Dim i As Range
Dim TheCol As Long
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColB
Select Case i.Offset(, -1).Value
Case "A": TheCol = 1
Case "B": TheCol = 2
Case "C": TheCol = 3
Case "D": TheCol = 4
Case "E": TheCol = 5
Case "F": TheCol = 6
Case "G": TheCol = 7
Case "H": TheCol = 8
Case "I": TheCol = 9
End Select
With Sheets("Sheet2")
.Cells(Rows.Count, TheCol).End(xlUp).Offset(1).Value =
i.Value
End With
Next i
End Sub
"Trina" wrote in message
...
I have a worksheet with 2 colums. The rating column will contain 1 of 9
scores. There are about 50 data lines. I need to goup them by that
score
in
a new worksheet in a block format. I added the example and look of
finished
product I need. Can a macro or Excel function make this work?

Data Worksheet:
Rating Name
1A Smith
1A Jerry
2A Jones
3A Daye
1B Wilson
2B Johnson
2B Wint
3B Lager
1C Swith
2C Jackson
3C Cole


Output Worksheet
__________________________________________________ ________________
|All 1C listed here | All 1B listed here |
All
1A listed here |
| Swith | Wilson |
Smith |
| |
|
Jerry |
|__________________|______________________|_______ ________________|
|All 2C listed here | All 2B listed here |
All
2A listed here |
| Jackson | Johnson |
Jones |
| | Wint |
|
|__________________|______________________|_______ ________________|
|All 3C listed here | All 3B listed here |
All
3A listed here |
| Cole | Lager |
Daye |
|__________________|______________________|_______ ________________|
--
Trina

I need the end data to be in a 3 x 3 grid:

1C Data 1B Data 1A Data

2C Data 2B Data 2A Data

2C Data 2B Data 2A Data




--
Trina


"Otto Moehrbach" wrote:

Trina
As written you can change the destination column, but the destination
row will always be the next empty row in whatever column it goes into. Tell
me more about what you want when you say "a specific column and row". Give
me a couple of examples. Specifically, tell me how you would determine what
column and row you want as the destination. Otto
"Trina" wrote in message
...
WOW! This is GREAT. I was able to make it work. I wanted to see if I
chould designate the Select Case locations to be a specifc column and row
and
not sure how to modify the code. Can that be done?
--
Trina


"Otto Moehrbach" wrote:

Trina
A Pivot Table might work for you. Look it up in Help. If not, the
following macro will do it for you. I chose "Sheet2" as the name of the
second or destination sheet. The sheet that holds the original 2 columns
(A
& B in this macro) must be the active sheet when you run this macro.
Look
at the Select Case part of the macro. The letters that follow the word
Case are your ratings. Type in your ratings in place of what I have.
The
numbers following TheCol= are the column numbers in the second sheet in
which the macro will place the names. A 1 is Column A, a 2 is Column B,
and
so on. HTH Otto
Sub ShuffleData()
Dim rColB As Range
Dim i As Range
Dim TheCol As Long
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColB
Select Case i.Offset(, -1).Value
Case "A": TheCol = 1
Case "B": TheCol = 2
Case "C": TheCol = 3
Case "D": TheCol = 4
Case "E": TheCol = 5
Case "F": TheCol = 6
Case "G": TheCol = 7
Case "H": TheCol = 8
Case "I": TheCol = 9
End Select
With Sheets("Sheet2")
.Cells(Rows.Count, TheCol).End(xlUp).Offset(1).Value =
i.Value
End With
Next i
End Sub
"Trina" wrote in message
...
I have a worksheet with 2 colums. The rating column will contain 1 of 9
scores. There are about 50 data lines. I need to goup them by that
score
in
a new worksheet in a block format. I added the example and look of
finished
product I need. Can a macro or Excel function make this work?

Data Worksheet:
Rating Name
1A Smith
1A Jerry
2A Jones
3A Daye
1B Wilson
2B Johnson
2B Wint
3B Lager
1C Swith
2C Jackson
3C Cole


Output Worksheet
__________________________________________________ ________________
|All 1C listed here | All 1B listed here |
All
1A listed here |
| Swith | Wilson |
Smith |
| |
|
Jerry |
|__________________|______________________|_______ ________________|
|All 2C listed here | All 2B listed here |
All
2A listed here |
| Jackson | Johnson |
Jones |
| | Wint |
|
|__________________|______________________|_______ ________________|
|All 3C listed here | All 3B listed here |
All
3A listed here |
| Cole | Lager |
Daye |
|__________________|______________________|_______ ________________|
--
Trina

Trina
I understand the 3x3 grid since there are only 9 ratings. What I don't
know is how many rows do you want to reserve in each rating. To be more
exact, how many rows in the top 3 ratings and how many rows in the second 3
ratings. Or maybe you want the code to figure that out. In that case, do
you want any blank rows (how many?) between the upper 3 and the middle 3 and
the lower 3? Otto
"Trina" wrote in message
...
I need the end data to be in a 3 x 3 grid:

1C Data 1B Data 1A Data

2C Data 2B Data 2A Data

2C Data 2B Data 2A Data




--
Trina


"Otto Moehrbach" wrote:

Trina
As written you can change the destination column, but the destination
row will always be the next empty row in whatever column it goes into.
Tell
me more about what you want when you say "a specific column and row".
Give
me a couple of examples. Specifically, tell me how you would determine
what
column and row you want as the destination. Otto
"Trina" wrote in message
...
WOW! This is GREAT. I was able to make it work. I wanted to see if I
chould designate the Select Case locations to be a specifc column and
row
and
not sure how to modify the code. Can that be done?
--
Trina


"Otto Moehrbach" wrote:

Trina
A Pivot Table might work for you. Look it up in Help. If not,
the
following macro will do it for you. I chose "Sheet2" as the name of
the
second or destination sheet. The sheet that holds the original 2
columns
(A
& B in this macro) must be the active sheet when you run this macro.
Look
at the Select Case part of the macro. The letters that follow the
word
Case are your ratings. Type in your ratings in place of what I have.
The
numbers following TheCol= are the column numbers in the second sheet
in
which the macro will place the names. A 1 is Column A, a 2 is Column
B,
and
so on. HTH Otto
Sub ShuffleData()
Dim rColB As Range
Dim i As Range
Dim TheCol As Long
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColB
Select Case i.Offset(, -1).Value
Case "A": TheCol = 1
Case "B": TheCol = 2
Case "C": TheCol = 3
Case "D": TheCol = 4
Case "E": TheCol = 5
Case "F": TheCol = 6
Case "G": TheCol = 7
Case "H": TheCol = 8
Case "I": TheCol = 9
End Select
With Sheets("Sheet2")
.Cells(Rows.Count, TheCol).End(xlUp).Offset(1).Value
=
i.Value
End With
Next i
End Sub
"Trina" wrote in message
...
I have a worksheet with 2 colums. The rating column will contain 1
of 9
scores. There are about 50 data lines. I need to goup them by that
score
in
a new worksheet in a block format. I added the example and look of
finished
product I need. Can a macro or Excel function make this work?

Data Worksheet:
Rating Name
1A Smith
1A Jerry
2A Jones
3A Daye
1B Wilson
2B Johnson
2B Wint
3B Lager
1C Swith
2C Jackson
3C Cole


Output Worksheet
__________________________________________________ ________________
|All 1C listed here | All 1B listed here |
All
1A listed here |
| Swith | Wilson |
Smith |
| |
|
Jerry |
|__________________|______________________|_______ ________________|
|All 2C listed here | All 2B listed here |
All
2A listed here |
| Jackson | Johnson |
Jones |
| | Wint
|
|
|__________________|______________________|_______ ________________|
|All 3C listed here | All 3B listed here |
All
3A listed here |
| Cole | Lager |
Daye |
|__________________|______________________|_______ ________________|
--
Trina