Mixed Data in column

[Greg]

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]

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").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 here:
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]

Dave. I like the idea. I see you were able to add the name to the date.
That opens up another question for me.

Is it possible to copy the info from Sheet1 manipulate it like you did on
Sheet2 then paste it back into Sheet1, either as a new column, or replacing
column A. That way if someone looks at the spreadsheet, it's not too
diffrent, but I'm still able to reference all the other data on that sheet.

In other words, take this:
COLUMN A
Smith, Joe
Jun 01
Jun 02
etc........
Jones, Pam
Jun 01
Jun 05

and replace it with this:

COLUMNA
Smith, Joe...Total
Smith, Joe...Jun 01
Smith, Joe...Jun 02
Jones, Pam...Total
Jones, Pam...Jun 01
Jones, Pam...Jun 05

Is that possible?

Thank you for all your assistance so far. I appreciate it very much.

Greg

 

[Dave Peterson]

The last section could change to:

With NewWks
With .Range("a1:A" & .Cells(.Rows.Count, "B").End(xlUp).Row)
.Formula = "=b1&""...""&c1"
.Value = .Value
CurWks.Columns(1).Insert
.Copy _
Destination:=CurWks.Cells(FirstRow, "A")
End With
.UsedRange.Columns.AutoFit
End With

I don't like overlaying the original data--just in case something goes wrong.
So if you have to rerun the code, just delete this new column A and run the
macro.

 

[Greg]

Dave:

That's Awesome! Great stuff. When I have more time I need to really figure
out how you combined the name with the date. It's great!

One more thing if you don't mind. I'm now doing a lookup based on the new
column and I am not getting the correct answer.

Here's what I'm using to try and find the value equal to Allen, Emily...Jun
01 (which I copied from the actual cell so I know there aren't typos, extra
spaces, etc.)

{=IF($A$8:$A$8000="Allen, Emily...Jun 01",$D$8:$D$8000,999)}

Any more ideas.

I really appreciate your help with this.

Greg

 

[Dave Peterson]

There's a better function to use:

=vlookup("allen, emily...jun 01",a:d,4,false)

That was the =vlookup() suggestion in that earlier message.

If you need help with this function...

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble

Dave:

That's Awesome! Great stuff. When I have more time I need to really figure
out how you combined the name with the date. It's great!

One more thing if you don't mind. I'm now doing a lookup based on the new
column and I am not getting the correct answer.

Here's what I'm using to try and find the value equal to Allen, Emily...Jun
01 (which I copied from the actual cell so I know there aren't typos, extra
spaces, etc.)

{=IF($A$8:$A$8000="Allen, Emily...Jun 01",$D$8:$D$8000,999)}

Any more ideas.

I really appreciate your help with this.

Greg