The first thing I would do is rearrange that data to be more column oriented.

I'd want to be able to use =vlookup()'s to retrieve data. So I'd concatenate
the name and "date" into a single cell.

It would look like this

--------A----------- ----B----- --C--- ---D------
Smith, Joe...Total Smith, Joe Total 7:03:06:08
Smith, Joe...Jun 01 Smith, Joe Jun 01 08:04:23
Smith, Joe...Jun 02 Smith, Joe Jun 02 04:31:44
Jones, Pam...Total Jones, Pam Total 4:14:51:11
Jones, Pam...Jun 01 Jones, Pam Jun 01 08:00:01
Jones, Pam...Jun 05 Jones, Pam Jun 05 07:45:23


I'd use a macro to do this.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim ColonCtr As Long
Dim HasComma As Boolean
Dim TotStr As String
Dim CurName As String

Set CurWks = ActiveSheet
Set NewWks = Worksheets.Add
With NewWks
'make columns A:B text
.Range("B:C").NumberFormat = "@"
.Range("D:D").NumberFormat = "hh:mm:ss"
End With

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = 0

For iRow = FirstRow To LastRow
HasComma = InStr(1, .Cells(iRow, "A").Value, ",", vbTextCompare)
TotStr = .Cells(iRow, "B").Value
ColonCtr = 0
If IsNumeric(TotStr) = False Then
ColonCtr = Len(TotStr) _
- Len(Replace(TotStr, ":", ""))
End If

oRow = oRow + 1
If iRow = FirstRow _
Or HasComma = True _
Or ColonCtr = 3 Then
CurName = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "C").Value = "Total"
Else
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "A").Value
End If
'put the name and time
NewWks.Cells(oRow, "B").Value = CurName
NewWks.Cells(oRow, "D").Value = .Cells(iRow, "B").Value
Next iRow
End With

With NewWks
With .Range("a1:A" & .Cells(.Rows.Count, "B").End(xlUp).Row)
.Formula = "=b1&""...""&c1"
.Value = .Value
End With
.UsedRange.Columns.AutoFit
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

=======
You can store the macro in any workbook you want. Open that workbook and then
select the worksheet with the imported data. Hit Alt-F8 and run the macro
(rename TestMe to something more significant).

After it's finished, you can use:
=vlookup("Smith, Joe"&"..."&"Jun 01",somesheet!a:d,4,false)
or
=vlookup(A2&"..."&B2,somesheet!a:d,4,false)

or do some error checking
=if(isna(vlookup(...)),"no match",vlookup(...))

========
By having the data in columns/fields, you'll be able to do more stuff with it
(add headers). You could use autofilter, pivottables, charts, ...



Greg wrote:

Hi group. I'm hoping someone can help me.

I am working on a vacancy report comparing data dumped out of our phone
system to the Workforce Mangement Schedule.

My data comes out of the phone system like this:

Name Logged Time
Smith, Joe 7:03:06:08 (time logged for the month)
Jun 01 08:04:23 (time logged for the day)
Jun 02 04:31:44 (time logged for the day)
etc......
Jones, Pam 4:14:51:11
Jun 01 08:00:01
Jun 05 07:45:23

If there is no data for the day, then the row is not included, so the rows
float.

Any idea how I can say in a different spreadsheet (the schedule sheet) if
the cell = "Smith, Joe" find "Jun 01" without it pulling info from the other
people?

I hope that's a little clearer than mud.

Thanks in advance,

Greg

--

Dave Peterson