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.

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.

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.