Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Unorganized Data Into Different Spreadsheets
I'm attempting to take some very unorganized data and extract it into data
that I can use in 3 different spreadsheets, all at once. This is for payroll purposes, so I'm really wanting it to extract the names by category, but when I copy and paste the info into Excel it isn't broken into columns at all. For example, Column G1 will have a list of data that would look something like this: Group of People #1 Name 1 Name 2 Name 3 Name 4 Group of People #2 Name 5 Name 6 Name 7 Name 8 Group of People #3 Name 9 Name 10 Name 11 Name 12 But Columns A-F MIGHT have groups that I need as well. It all depends on how many people are scheduled for that day. It also might not always be under column G. Is there a way to create a dynamic filter that will extract all of the NAMES of just the people listed in Group of People #2? It will not always be the same number of people... So I guess I want it to filter and organize all of the names starting at Group of People #2 and ending at the beginning of Group of People #3. So the Filter would pull just "Group #2" and anyone listed under that Group? The "Groups of People" will always come in the same order, if that helps. Ultimately, here's what I want to have happen. 1. User will copy and paste the table from the internet into my excel sheet. 2. They will run my macro 3. The names will be organized by group on 3 different spreadsheets that all perform different functions. Let me know if you need more information. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Unorganized Data Into Different Spreadsheets
You can do this with code not functions. The following code will scan
down the master list of groups and names. If the text in a cell begins with "Group" it is assumed to be a group heading, not a person's name. In this case, a new worksheet is created having the name of the group. If a cell is not a group header, it is written to the appropriate group sheet. If you have AppendToWS = True then when you run the code a second time, values from the master sheet are added to the end of the data in the existing group worksheets. If AppendToWS = False the group worksheets are deleted and then recreated as empty sheets. Change the lines marked with <<< to the appropriate values. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Sub BreakoutToSheets() Dim LastRow As Long Dim WS As Worksheet Dim R As Range Dim CR As Range Dim Dest As Range Dim StartCell As Range Dim ColLetter As String Dim StartRow As Long Dim CurrentGroup As String Dim T As String Dim AppendToWS As Boolean Set WS = Worksheets("Sheet1") ' <<<< CHANGE to all names WS ColLetter = "A" '<<< CHANGE to column letter on WS that has names StartRow = 1 '<<< CHANGE to first row of data AppendToWS = True '<<< If True, existing worksheets remain ' intact and data is appended at the end ' of the existing data. If False, the ' existing worksheet will be deleted. With WS LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row End With Set R = WS.Cells(StartRow, ColLetter) CurrentGroup = R.Text If AppendToWS = False Then DeleteSheet CurrentGroup With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) WS.Name = CurrentGroup End With Else If SheetExists(CurrentGroup) = True Then Set WS = ThisWorkbook.Worksheets(CurrentGroup) Else With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) WS.Name = CurrentGroup End With End If End If With WS If AppendToWS = True Then Set Dest = .Cells(.Rows.Count, ColLetter).End(xlUp) If Dest.Text < vbNullString Then Set Dest = Dest(2, 1) End If Else Set Dest = .Cells(StartRow, ColLetter) End If End With Set R = R(2, 1) Do Until R.Row LastRow If R.Text < vbNullString Then T = IsGroup(R.Text) If T = vbNullString Then Set CR = R Do Until CR.Text = vbNullString Dest.Value = CR.Text Set Dest = Dest(2, 1) 'move down Set CR = CR(1, 2) ' move right Loop Else With ThisWorkbook.Worksheets If AppendToWS = False Then DeleteSheet T Set WS = .Add(after:=.Item(.Count)) WS.Name = R.Text Set Dest = WS.Range("A1") Else If SheetExists(T) = True Then Set WS = Worksheets(T) With WS Set Dest = .Cells(.Rows.Count, ColLetter).End(xlUp)(2, 1) End With Else With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) WS.Name = T End With Set Dest = WS.Cells(1, "A") End If End If End With End If End If Set R = R(2, 1) ' move down Loop End Sub Sub DeleteSheet(SheetName As String) On Error Resume Next Application.DisplayAlerts = False ThisWorkbook.Worksheets(SheetName).Delete Application.DisplayAlerts = True End Sub Function IsGroup(S As String) As String If StrComp(Left(S, 5), "Group", vbTextCompare) = 0 Then IsGroup = S Else IsGroup = vbNullString End If End Function Function SheetExists(SheetName) As Boolean On Error Resume Next SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name) ) End Function '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 16 Apr 2009 12:51:01 -0700, Crinks wrote: I'm attempting to take some very unorganized data and extract it into data that I can use in 3 different spreadsheets, all at once. This is for payroll purposes, so I'm really wanting it to extract the names by category, but when I copy and paste the info into Excel it isn't broken into columns at all. For example, Column G1 will have a list of data that would look something like this: Group of People #1 Name 1 Name 2 Name 3 Name 4 Group of People #2 Name 5 Name 6 Name 7 Name 8 Group of People #3 Name 9 Name 10 Name 11 Name 12 But Columns A-F MIGHT have groups that I need as well. It all depends on how many people are scheduled for that day. It also might not always be under column G. Is there a way to create a dynamic filter that will extract all of the NAMES of just the people listed in Group of People #2? It will not always be the same number of people... So I guess I want it to filter and organize all of the names starting at Group of People #2 and ending at the beginning of Group of People #3. So the Filter would pull just "Group #2" and anyone listed under that Group? The "Groups of People" will always come in the same order, if that helps. Ultimately, here's what I want to have happen. 1. User will copy and paste the table from the internet into my excel sheet. 2. They will run my macro 3. The names will be organized by group on 3 different spreadsheets that all perform different functions. Let me know if you need more information. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Unorganized Data Into Different Spreadsheets
Alrighty! I figured it was something like this, requiring VBA and so forth.
New quandry... Imagine the list looks something like this when pasted into Excel: Servers NAME 1 NAME 2 NAME 3 NAME 4 Bussers NAME 5 NAME 6 NAME 7 Will your program work for this too? Is there a way to get it to pull all of the servers, bussers, and other employees that I might need? Thanks so much. "Chip Pearson" wrote: You can do this with code not functions. The following code will scan down the master list of groups and names. If the text in a cell begins with "Group" it is assumed to be a group heading, not a person's name. In this case, a new worksheet is created having the name of the group. If a cell is not a group header, it is written to the appropriate group sheet. If you have AppendToWS = True then when you run the code a second time, values from the master sheet are added to the end of the data in the existing group worksheets. If AppendToWS = False the group worksheets are deleted and then recreated as empty sheets. Change the lines marked with <<< to the appropriate values. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Sub BreakoutToSheets() Dim LastRow As Long Dim WS As Worksheet Dim R As Range Dim CR As Range Dim Dest As Range Dim StartCell As Range Dim ColLetter As String Dim StartRow As Long Dim CurrentGroup As String Dim T As String Dim AppendToWS As Boolean Set WS = Worksheets("Sheet1") ' <<<< CHANGE to all names WS ColLetter = "A" '<<< CHANGE to column letter on WS that has names StartRow = 1 '<<< CHANGE to first row of data AppendToWS = True '<<< If True, existing worksheets remain ' intact and data is appended at the end ' of the existing data. If False, the ' existing worksheet will be deleted. With WS LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row End With Set R = WS.Cells(StartRow, ColLetter) CurrentGroup = R.Text If AppendToWS = False Then DeleteSheet CurrentGroup With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) WS.Name = CurrentGroup End With Else If SheetExists(CurrentGroup) = True Then Set WS = ThisWorkbook.Worksheets(CurrentGroup) Else With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) WS.Name = CurrentGroup End With End If End If With WS If AppendToWS = True Then Set Dest = .Cells(.Rows.Count, ColLetter).End(xlUp) If Dest.Text < vbNullString Then Set Dest = Dest(2, 1) End If Else Set Dest = .Cells(StartRow, ColLetter) End If End With Set R = R(2, 1) Do Until R.Row LastRow If R.Text < vbNullString Then T = IsGroup(R.Text) If T = vbNullString Then Set CR = R Do Until CR.Text = vbNullString Dest.Value = CR.Text Set Dest = Dest(2, 1) 'move down Set CR = CR(1, 2) ' move right Loop Else With ThisWorkbook.Worksheets If AppendToWS = False Then DeleteSheet T Set WS = .Add(after:=.Item(.Count)) WS.Name = R.Text Set Dest = WS.Range("A1") Else If SheetExists(T) = True Then Set WS = Worksheets(T) With WS Set Dest = .Cells(.Rows.Count, ColLetter).End(xlUp)(2, 1) End With Else With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) WS.Name = T End With Set Dest = WS.Cells(1, "A") End If End If End With End If End If Set R = R(2, 1) ' move down Loop End Sub Sub DeleteSheet(SheetName As String) On Error Resume Next Application.DisplayAlerts = False ThisWorkbook.Worksheets(SheetName).Delete Application.DisplayAlerts = True End Sub Function IsGroup(S As String) As String If StrComp(Left(S, 5), "Group", vbTextCompare) = 0 Then IsGroup = S Else IsGroup = vbNullString End If End Function Function SheetExists(SheetName) As Boolean On Error Resume Next SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name) ) End Function '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 16 Apr 2009 12:51:01 -0700, Crinks wrote: I'm attempting to take some very unorganized data and extract it into data that I can use in 3 different spreadsheets, all at once. This is for payroll purposes, so I'm really wanting it to extract the names by category, but when I copy and paste the info into Excel it isn't broken into columns at all. For example, Column G1 will have a list of data that would look something like this: Group of People #1 Name 1 Name 2 Name 3 Name 4 Group of People #2 Name 5 Name 6 Name 7 Name 8 Group of People #3 Name 9 Name 10 Name 11 Name 12 But Columns A-F MIGHT have groups that I need as well. It all depends on how many people are scheduled for that day. It also might not always be under column G. Is there a way to create a dynamic filter that will extract all of the NAMES of just the people listed in Group of People #2? It will not always be the same number of people... So I guess I want it to filter and organize all of the names starting at Group of People #2 and ending at the beginning of Group of People #3. So the Filter would pull just "Group #2" and anyone listed under that Group? The "Groups of People" will always come in the same order, if that helps. Ultimately, here's what I want to have happen. 1. User will copy and paste the table from the internet into my excel sheet. 2. They will run my macro 3. The names will be organized by group on 3 different spreadsheets that all perform different functions. Let me know if you need more information. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting data linked to other spreadsheets | Excel Worksheet Functions | |||
pulling data from two spreadsheets | Setting up and Configuration of Excel | |||
Data from 2 spreadsheets | Excel Discussion (Misc queries) | |||
Looking up data from several spreadsheets | Excel Discussion (Misc queries) | |||
compare data from several spreadsheets | Excel Discussion (Misc queries) |