Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
analyzing data with VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
analyzing data with VBA
Send the workbook to
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
analyzing data with VBA
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
analyzing data with VBA
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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
analyzing data with VBA
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
analyzing data - accuracy assessment | Excel Discussion (Misc queries) | |||
useful templates for analyzing bloomberg data | Excel Programming | |||
Adding/Analyzing Data From One Worksheet to Another | Excel Worksheet Functions | |||
Problem analyzing my data | Excel Worksheet Functions | |||
analyzing poll data | Excel Programming |