Formating excel data for import into employee schedule software

[jthatch]

I have an interesting problem that I hope someone can help me with. Our employee schedule is done first in excel and then manually entered into our timeclock software. I am looking to format the data in the excel sheet so that we can import the data directly instead of manually entering it.

The format of the data is as follows.

In the top row starting in column b is the time of day. 5 am to 11pm. Each hour represented by an individual cell.

In column A is a list of positions that need to be filled.

Employees are entered into the sheet in a gantt like format. I need to be able to identify the position, same row column A and the number of times the name appears in that row. Also some rows contain multiple names that I need to distinguish from one another. To make things more difficult if an employee starts on the half hour, their name is preceded by a "/" and if they end on the half hour their name is followed by a "/"

A single row could look like this

5am 6am 7am 8am 9am 10am 11am 12pm
position /fred fred fred fred fred/ /george george george/

I need to be able to format this so that it looks like this

position fred 5:30am 9:30am
position george 10:30am 12:30pm

is this even possible, or is it a lost cause?

Thanks in advance

 

[Bong]

It's certainly possible, but I don't think it will be pretty. The task would be made simpler if you had a list of possible names. Do you? If not, I think I'd start with code to do that.

Let's say your range, for simplicity, is [b2:t20] (this is ugly and untested):

dim cNames as new collection
for each c in [b2:t20].cells
  strT =  c.value
  if (instr(1,strT,"/")==1) then strT=right(strT,len(strT)-1)
  if (instrrev(strT,"/")>0) then strT=left(strT,len(strT)-1)
  bFlag==true
  for each n in cNames
   if (n == strT) then 
       bFlag==false
       exit for
   end if
  next
  if bFlag then cNames.add(strT)
next

Now the collection, cNames, should be a complete set of the names that are used.

Now you can march down the rows where ColA is the position. For each of those rows/positions, check if each name in the collection appears. If it does, it gets an output row, and you need to see which columns it appears in. Try a few things and report back.

_________________
Bob Rashkin

 

[SkipVought]

The basic problem is with your source data. If it were normalized, a simple query could give you the results you want in a matter of seconds.
[tt]
[Position Field], Employee, [Time Slot]
[/tt]
[tt]
Select [Position Field], Employee, Min([Time Slot]), Max([Time Slot])
From [YourSheetName$]
Group By [Position Field], Employee
[/tt]




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]