Counting Initials and Dates

[knohbody]

Hello everyone,
I'm new to the forums, though I've solved a couple of my problems by lurking here.
I'm currently having a problem with a worksheet I've been working on at work. I'm not very experienced in Exel and have inherited a worksheet that keeps track of the machines we do maintenance on during the year. What I have now is a spreadsheet with columns alternating between initials and date, and rows with the machine names all the way down. It looks similar to this :

Name of machine Initials date initials date initials date initials date etc.
machine 1
machine 2
machine 3
machine 4
...
machine 121

I'd like to keep this format, but we need to keep track of who does what within a certain week, which means I need to count the number of instances of an initial, then compare it with the date in the column next to it, which I don't think would be a problem with just two columns, but I'm having problems wrapping my brain around how to do so with 20-30 columns.
I'm using date ranges in some of my calculation formulas, which I think necessitates the date being in a separate column than the text.

Any suggestions?

Thanks

 

[Greg Truby]

So, you want to know how many machines each person did each day? Correct? If that's the case, we can get you there lickety split using a pivot table. If I have misunderstood your request, please clarify.

Which version of Excel are you using?

 

[knohbody]

Right now, I'm using Excel X for mac... its only around 9 years old. We have a PC version at work, but I'm not entirely sure what version it is, but I'm sure it's newer than my mac version.

What I need is:
1) a count of all machines done by a certain person during a week (Thursday to Wednesday, don't ask)
2) a count of all machines done by a certain person during a month
3) a count of all machines done by a certain person during a year

I also need an overall count of all of those, but I already have these setup by counting the dates using sumproduct(). (i.e. count all dates between the value in z1 and the value in z2 in the range b2:x120)

I'm interested in using the pivot table or any alternate ways. If I can't get the pivot table to work here, I can try it out at work on the newer version of Excel.

 

[Greg Truby]

OK, this should be fun. I'll have to hope the Mac Excel menus approximate Excel 2003's??? [Last time I even *saw* someone using Excel on a Mac was Excel 1.0 in 1988.]

How much experience do you have creating pivot tables?

 

[knohbody]

Greg,

As far as pivot tables go, I have no experience with them whatsoever.

As far as the menu items go, I believe Excel X for Mac approximates to Excel 2000 for Windows. The copyright date is 2001.

I'm fairly good with muddling through things, though, so if you point me in the direction and can explain what somethings supposed to do, I can generally figure it out.

Thanks,
James

 

[Greg Truby]

The lowest version of Excel I have access to is XP (2002). I'll try running from there.

Hopefully you have a Data menu option. On the Data menu there should be an option for PivotTable and PivotChart Report...

• Click that to open the wizard and, if you did this while you were within the data block, you default to the correct data block for your source range. If you click "Finish" on the dialog, hopefully you get a blank worksheet with a grid on it.
• In the big section of the unformed pivot, it should say "Drop Data Items Here". If you drop the column with the person's initials into the data section the default summarizing function Excel picks should be COUNT, i.e. you should see something like "Count of Mechanic" in cell A3 and an overall count in cell B4.
• Now if you drag and drop your Date field over the "Total" in Cell A4 you should see counts by date. If you right click a date field, you can pick Group... from the popup and select how you want to group them by various pre-defined date units.

This may not be a final solution for you due to the Thursday to Wednesday thing, but try this to see if it gets you close to what you need.

 

[knohbody]

Greg,

I do indeed have pivot tables in my version of Excel, so we're good on that part. I've been playing with it a bit, but I think I'm having a bit of a problem with the way my data is formatted.

The columns labeled initials aren't tied to one person. They have several sets of initials in each column. The date columns are the same way. They aren't set to a specific date range, but occur as a machine is worked on.

So when I'm making my pivot tables I seem to be able to put a column of initials in, and a column of dates in, but if i do more than that, it starts counting the second column of initials under the first persons initials, and gets very confusing, very quickly.

I'll try to give a better idea of my spreadsheet below, obviously it extends beyond this.

<table style="width: 482px; height: 127px;" border="1"><tbody><tr><th>Name of Machine
</th><td style="vertical-align: top;">Initials
</td><td style="vertical-align: top;">Date
</td><td style="vertical-align: top;">Initials
</td><td style="vertical-align: top;">Date
</td><td style="vertical-align: top;">Initials
</td><td style="vertical-align: top;">Date
</td></tr><tr><td>Name 1
</td><td style="vertical-align: top;">JA
</td><td style="vertical-align: top;">3/1/09
</td><td style="vertical-align: top;">RC
</td><td style="vertical-align: top;">5/9/09
</td><td style="vertical-align: top;">RD
</td><td style="vertical-align: top;">6/1/09
</td></tr><tr><td>Name 2
</td><td style="vertical-align: top;">RC
</td><td style="vertical-align: top;">2/3/09
</td><td style="vertical-align: top;">JA
</td><td style="vertical-align: top;">3/12/09
</td><td style="vertical-align: top;">JA
</td><td style="vertical-align: top;">7/9/09
</td></tr><tr><td style="vertical-align: top;">Name 3
</td><td style="vertical-align: top;">RD
</td><td style="vertical-align: top;">12/15/08
</td><td style="vertical-align: top;">RC
</td><td style="vertical-align: top;">1/28/09
</td><td style="vertical-align: top;">JA
</td><td style="vertical-align: top;">3/2/09
</td></tr></tbody></table>

 

[Greg Truby]

Oh - yuck. OK. Will be back in a bit with some code to reformat to what the pivot will need.

While the type of layout you have is not uncommon for this type of log, it makes it devilishly tough to do much with the raw data - pulling a pivot is a textbook case of why this type of layout is tough to use for analysis.

 

[Greg Truby]

Sub ReformatDataBlock()
 
    Const c_strStartOfInitials As String = "B1"
    Const c_intNbrOfHeaderRows As Integer = 1
 
    '// # of columns between "Initials Columns"
    Const c_intWidthOfSubGroup As Integer = 2
 
    '// position of date column relative to initials
    Const c_offsetInitToDate As Integer = 1
 
    Dim rngTargetCell As Excel.Range, rngHeaderCell As Excel.Range, _
        rngSource As Excel.Range, wsTarget As Excel.Worksheet, _
        lngRowsToCopy As Long
 
 
    '// Always remember: safety first.
    If vbNo = MsgBox("Are you running this on a backup copy of the file?", _
                     vbQuestion + vbYesNo + vbDefaultButton2) Then
        Exit Sub
    End If
 
    '// change this line if you need to work on some
    '// other worksheet that is not the activesheet
    Set wsTarget = ActiveSheet
 
    With wsTarget
        Set rngTargetCell = .Range(c_strStartOfInitials).End(xlDown).Offset(1)
        Set rngHeaderCell = .Range(c_strStartOfInitials).Offset(, c_intWidthOfSubGroup)
    End With
 
    Let lngRowsToCopy = rngTargetCell.Row - 1 - c_intNbrOfHeaderRows
 
    Set rngSource = rngHeaderCell.Offset(c_intNbrOfHeaderRows) _
                                 .Resize(lngRowsToCopy, c_intWidthOfSubGroup)
 
    Do Until IsEmpty(rngHeaderCell)
        rngSource.Cut rngTargetCell
        Set rngHeaderCell = rngHeaderCell.Offset(, c_intWidthOfSubGroup)
        Set rngSource = rngHeaderCell.Offset(c_intNbrOfHeaderRows) _
                                     .Resize(lngRowsToCopy, c_intWidthOfSubGroup)
        Set rngTargetCell = wsTarget.Range(c_strStartOfInitials).End(xlDown).Offset(1)
    Loop
 
    '// I leave the header clean up to you. :)
 
End Sub

 

[knohbody]

Greg,

I appreciate all the time you've put into this to help me. I'm assuming I need to put this code into the realbasic or visualbasic editor and then run it, which I have tried. It seems to put all of the initials into one column and the dates into another column.
This will likely work for the pivot table, but won't work for our day to day inputting of dates and such. As you said, the format makes it difficult to play with the data. Do you know of a better way to format this while still being able to manipulate the data easily? I'm trying to implement something that's easy for the user to input into, but will give the management the data summary they need.

Thanks,

James