I've got a dataset that I'm working with. It basically includes a PWA number, year (2005 or 2006), # of units, & test time. There are 1-several occurances of each PWA, each with an associated year, # of units, & test time.
I'm using a pivot table to sum the # of boards and test times broken up by year and PWA #. Then I'm creating another dataset that will include the average time per test classified by board and year (the point of the year differentiation is to determine if my project had noticeable impact on test time from year to year).
The problem I"m having is that some of the boards only have data for 2005 or 2006, not both. This messes the sequencing up for my calculations. I want to edit my original data list so if a PWA does not have at least 1 entry for both 2005 and 2006 it is discarded (or not copied to a new data list, either is fine).
Help anyone?
Note: I've never done VBA scripts before. I'm comfortable with c++, java, and php, but I'd prefer not to have to learn VBA for this if at all possible.
Posted by TomDavidson (Member # 124) on :
You could parse the Excel file (as .csv, if necessary) with PHP or Java, then write the changes back. But it's actually much easier with VBA.
You could also probably achieve a similar result with lookups, although this would require two extra sheets.
I'd recommend the VBA approach, though. This isn't a particularly difficult macro.
Posted by Dagonee (Member # 5818) on :
If the excel tables are well-formed and you have Access, you could attach them in an Access DB as tables and use SQL to do what you want very simply.
Posted by BaoQingTian (Member # 8775) on :
Cool, thanks for the feedback Tom and Dag....I really wanted to make sure there wasn't an easier way to do this in Excel. I don't think they have a convenient PHP server at work here (and if they did it'd take forever to get access to it), so I'll try the VBA route and the SQL. I've never used access, but if I can use SQL commands, it should work out.
Thanks Again
Posted by BaoQingTian (Member # 8775) on :
How's this look (I realize it's not very elegant, but does it work)?
Sub only_pairs() num_rows = ActiveSheet.UsedRange.Rows.Count found_flag = 0 For cur_row = num_rows To 2 Step -1
For scan_row = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(scan_row, 1).Value = pwa Then If Cells(scan_row, 2).Value <> test_date Then scan_row = ActiveSheet.UsedRange.Rows.Count found_flag = 1 End If End If
Next scan_row
If found_flag = 0 Then Rows(cur_row).Delete End If
found_flag = 0
Next cur_row End Sub
Edit: Sorry, tabs aren't showing
[ July 10, 2006, 06:57 PM: Message edited by: BaoQingTian ]
Posted by BaoQingTian (Member # 8775) on :
New Question: The VBA script I did appears to have worked & shaved down my data from 4300 points to 3700 (I'm hoping it didn't eliminate anything valid though). However, I'm having another problem.
In my pivot table, there is data spaced like this:
A4,A10,A16,A22..... I'm creating a new datalist from the pivot table to play with the numbers. For example, I'll need to do D6/D5, D12/D11....
However, Autofill does not want to cooperate with formulas. If I do =A4, =A10, =A16, it gives me =A7,=A13,=A19 as the next cell contents. I understand what it is doing, but not how to make it do what I want....any experience with this one?
Or will I have to do a normal series a4, a10, a16, and then use string concatenation to add the = sign to the beginning of each?
Ok, knowledge of the INDIRECT function is good...used this:
[ July 11, 2006, 12:39 PM: Message edited by: BaoQingTian ]
Posted by TomDavidson (Member # 124) on :
quote:I understand what it is doing, but not how to make it do what I want....any experience with this one?
Try filling out at least three fields with the correct function, then select all three before using AutoFill. By giving the system the chance to see the proper progression, you'll increase the chance that it'll get it right.