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]

James,

Yes, you would probably end up with a situation where you want to go ahead and keep your current format for inputting in all of the data into your logs. People are used to it and for a user interface it is, indeed, probably better for them. You don't want to create your user interface based on your data-processing requirements. That's a case of the "tail wagging the dog". I don't know how many times I've seen business requirements be placed subservient to accounting's or HR's SOP's - but I digress. Keep your users happy and use VBA to deal with the crappy setup.

Yes this code does exactly that - it reformats the data to make it pivot friendly. So now what? Well what you want is to create additional code that will grab the data from your human-input logs and create a copy. Then apply this code to reformat the data so it's pivot-friendly. Then have code generate your pivot table. When you have the code all written, it's "click and done" and you go get a cup of coffee and surf Facebook with your newfound spare time.

You have the macro recorder at your disposal which should get you pretty close to everything you need to pull off the sheet copy and the pivot creation.

 

[knohbody]

Greg,

Thanks for your patience and time, as well as the effort you put into helping me. If I have any problems, you can be sure I'll be back to ask. Thanks again,

-James

 

[Greg Truby]

We're just lucky that you're running on a mac version that still has macro capabilities. Excel 2008 for the mac was stripped of VB; which to me makes it (Excel2008, not the mac) pretty useless.