analyzing data with VBA

[Nicole Knapp]

My project involves 4 scantron-like cards

Each card has 4 section to it. The first 3 sections are the same on each
card type

1st section - ID# and Date
2nd Section - Department (12 choices - none or multiple allowed)
3rd Section - Project Area (12 choices - none or multiple allowed)
4th Section - Attributes (From 13 to 19 choices - each with a 1, 2, 3 or
nothing allowed)

The software produces a worksheet for each card with ID#, Date, Each Dept,
Each Project, each Attribute as a header (so from column A to AA is filled
with the same headers on each sheet, Starting at AB, the headers differ
between sheets) The software records a "0" for each blank circle in sections
2, 3 & 4. A "1" for each filled circle in sections 2 & 3). The same person
can fill out multiple cards per day.

I have different reports that track this information for daily counts.
Counts by ID#, Counts per Date.

I have a VBA that splits out the attribute information by date

Attribute| Rating|1/27/2009|1/28/2009
Type A| 1| | |
2| 3| 7|
3| 2| 1|
Type B| 1| | |
2| 3| 7|
3| 1| |
Type C| 1| | |
2| 3| 4|
3| 2| 2|

Here is the code that was written for me:

Sub AttributeCount()

Sheet6.Select
lastrow_data = ActiveCell.SpecialCells(xlLastCell).Row
lastcol_data = ActiveCell.SpecialCells(xlLastCell).Column

Sheets("Attributes by Date").Select
lastcol = ActiveCell.SpecialCells(xlLastCell).Column
daterow = 2
Dim myattribs(1 To 13) As Integer

For i = 3 To lastcol ' dates going across
Sheets("Attributes by Date").Select
searchdate = Cells(2, i).Value

Sheet6.Select
For q = 2 To lastrow_data ' capture data
Sheet6.Select

testvalue = DateValue(Cells(q, 3).Text)
If testvalue = searchdate Then
For c = 1 To 13 ' columns 28 to 40
myattribs(c) = Cells(q, c + 27).Value
Next

Sheets("Attributes by Date").Select
Cells(3, i).Select
rownumber = 3
For t = 1 To 13
For b = 1 To 3
If myattribs(t) = b Then
Cells(rownumber, i).Value =
Cells(rownumber, i).Value + 1
End If
rownumber = rownumber + 1
Next
Next
End If
Next
Next

End Sub

My questions:
--Is there a quick way to change this code so that it looks for the ID#
within the data (down the column) and counts for it by date rather than for
the attribute (which is the header)? or some other way to count them up? (I
have the IDs in a list.)
--Is there a quick way to change this code so that it looks at the
department (or project) and counts up the attributes (1s, 2s, 3s) for each?
By date?

I have pivot tables doing a little bit of this, but they are very large.
The workbooks with this code in them are not as big.

So far I have 1900 ID#s (and a subset of 65 of those that I track for) and
over 1000 cards have been scanned.

 

[exceluserforeman]

Send the workbook to
(e-mail address removed)


I can rewrite the program (conditional). Please include this post as well
for reference. Allow up to 7 days for a result.

Who ever wrote this for you has no principles in coding. I hope you did not
pay for it.

See my sites for references.
http://au.geocities.com/excelmarksway
http://www.geocities.com/excelmarksway

I use Office 2003 sp3 but it shouldn't make any difference what version as I
use a standard format for most of my coding and rarely use VBA AddIns.

regards
- exceluserforeman
PS I am not an expert.

 

[Nicole Knapp]

sorry - don't have 7 days. And I can't give you the spreadsheet for business
security.

I didn't pay for the code - borrowed it from a finance spreadsheet.

The original sheet the data is pulling from will be overwritten up to
several times each day. So I have a button that clears the all last copied
data and then the code seeks each line for the corresponding header date and
pulls the attribute counts.

The original spreadsheet looks like this:
ID#|Date |D1|D2|D3|D4|P1|P2|P3|P4|A1|A2|A3|A4
001|021009| 1| 0| 0| 0| 0| 0|1| 1| 0| 1| 3| 2
011|020909| 0| 0| 0| 0| 1| 0|0| 0| 3| 2| 0| 2
800|020909| 0| 0| 0| 0| 0| 0|0| 0| 0| 2| 1| 0
001|020809| 0| 0| 1| 0| 0| 0|0| 0| 1| 2| 0| 3
076|020909| 1| 0| 0| 0| 0| 0|0| 0| 2| 2| 2| 1
800|020909| 1| 0| 0| 0| 0| 0|0| 0| 3| 3| 1| 0
034|021709| 1| 0| 0| 0| 0| 0|0| 0| 2| 2| 1| 3

I want to return the count of each attribute for each date where there is a
"1" in column "D1" and put it in a worksheet labeled "D1" - and repeat that
for each department & each project.

So on sheet D1 I should see that on 2/9, A1 had a 2 & 3, A2 had a 2 & 3, A3
had a 1 & 2, A4 had a 1; on 2/10, A2 had a 1, A3 had a 3 & A4 had a 2; on
2/17 A1 had a 2, A2 had a 2, A3 had a 1 and A4 had a 3

I shouldn't have even posted the code. I should've just posted this.

 

[exceluserforeman]

Discussion groups are not designed to do the work for you for free. We will
help you with bits and pieces but not the whole project. Either you contract
someone or you learn how to do it properly. You could break the query into
several questions thus sidestepping the entire project design.

Many people, like yourself I suspect, are paid to know these things then you
come here and expect the "experts" to do the work for you. This is not fair.
This is why may answers are either not answered or are vaguely responded.
Many do answer questions like this in full but it eventually eats into them
and they eventually burn out.

I used to do these problems back in the early days but haven't done anything
for about 5 years. I cannot even guarantee that the "job" will be completed
on time. I am not even an expert. I have done tasks like these for more
important business than yours including Government work. For an "expert" to
complete this task expect to pay at least $200

I expect you only have 5 days left. If you find no result will you lose your
job or your credibility? I am a disabled person and have all the time in the
world....

Good luck. This question is already on Page 5. By tomorrow it will on 6 or
7. Many people do not even scroll back past page 3.

I recommend Gordon Dibben or David McRitchie.

 

[Nicole Knapp]

Guess what - I'm not paid for what I am doing with these spreadsheets - I'm
not in IT, I'm on a team that is put together once every 2 years for a month
& has no IT support. So rather than having to rework a solution every year,
I'm trying to figure out some other way to do it - and LEARN in the process,
making it easier for the next team in 2 years. This isn't my entire project
- I have already created charts, pivot tables, etc by myself - but I need the
detail broken down a bit more.

I'm looking for a simple nested loop to look for a date & at a column in a
different worksheet to see if there is a '1' or a '0' in the column and then
copy (or count) the found row into the active worksheet. I know how to make
a macro to clear the data & then point to the loop. I get some of how to do
the loop, but the row, column, column, row throws me.

I was trying to do this in Access and the users in that discussion group
sent me here.

If you aren't going to offer any help - then don't respond & leave the
thread unanswered. I'll survive.