Lotus 123 Macro Writing Problem

[viperguy]

Way back in 92-93 I began using Lotus and wrote a custom app using ver 2.4. It has served us well up to now. With the advent of XP and faster machines with over 2Mhz CPU, I am getting copy errors where data gets copied into strange places when the user executes certain macros that make extensive use of the copy function. I haven't been able to pinpoint the problem so it is definitely time to move into the windows spreadsheet world.

I did some research and based on this forum and other sources have settled on 123R5 as my best bet for a windows upgrade. That said, I am having trouble with writing a new macro to automate a particular task and I am hoping some of the experts out there may be able to guide me. I am pretty much of an intermediate user and have only done this one app. I learned the macro language over 3 days and am pretty much at or near the end of may limited skills....

Here is a description of what I need to do:

On one workbook page, (Sheet B), I have a series of records arranged in rows. Column A contains a person's name, col B, a short text description of their work duties, column C, a 2 digit text abbreviation of the person's state location (CA, MN, etc) , column D, a 4 digit numeric work code (1234, 9987, etc), and then a series of up to 24 adjacent columns (or fewer, depending on circumstances) containing numeric data which would represent the payroll of the person on that row.

I would like to scan columns C % D and then sum all of the payroll data in Column E onward for all record with matching sets of state identifiers and work codes and display the results on another workbook page (A) in columns. (Hope that is fairly clear)

As an example, assuming each person (record) on each row had eaxctly 10,000 in annual wages (detailed in columns E onward) and the distribution was 4 CA/1234, 4 MN/1234 and 1 CA/9987 and 2 MN/9987 the desired output (on summary sheet A) would be:

CA 1234 $40,000
CA 9987 $10,000

MN 1234 $40,000
MN 9987 $20,000

Thanks in advance for any suggestions you may have on how I could attack this

 

[DaleWatson123321]

Hi viperguy,

I won't be able to help out until tomorrow, but if you'd like "specific" help, the best way would be for you to email your file. I'll then be able to make the adjustments required and email it back.

If you have any "sensitive" data in your model, perhaps you could replace it with fictitious data that still reflects the type of data you're working with.

I hope this can help.

Regards, ...Dale Watson
home: nd.watson@shaw.ca

 

[crazybird]

It is not possible to aggregate in two dimensions at once using data queries. So you'll need to add a TOTAL column to your data input table range with a @SUM accross the amount fields. Then you can simply do a /Data Query aggregate. The aggregate is produced by using a computed output header for the amount field, which in this case would be: @SUM(TOTAL).

You'll need a criteria range as well, just copy the table headers and define a 2-row range.

In the output range include only the fields by which you wish to aggregate, plus the @SUM(TOTAL), which will evaluate to 'ERR', but this is benign.