Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to pull data with criterea for placement on a new sheet
I would like the code to figure out how many rows will be needed. Ideally, I
would like 3 blank rows in between the top & middle and middle & bottom. Thanks for all your help!! -- Trina "Otto Moehrbach" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a formula on wk sheet 2 to pull data from wk sheet 1 | Excel Discussion (Misc queries) | |||
VLookup / pull data from 1 sheet to another | Excel Discussion (Misc queries) | |||
Pull data from multiple sheet | Excel Worksheet Functions | |||
How do I pull certain data from several worksheets in one sheet? | Excel Worksheet Functions | |||
pull data from sheet two, then fill in the data to sheet one (part | Excel Worksheet Functions |