Evaluating new project idea

[Bruce Roberson]

I am evaluating whether to change how I do a severance tax
report to the State of New Mexico based on whether I can
formulate an approach to importing data as detailed below.
The alternative to importing is to continue keying an ever
increasing number of records, and to manually maintain
several linking tables in the spreadsheet for each new
property that is added.

I can visualize a time savings in not having to key values
each month if I can figure out how to have the spreadsheet
make some decisions based on the constraints described
below. It has to do some linking and find some table
information based on common field values and vlookup
operations.

This may seem like more of a job for Alpha V, the
database, rather than a spreadsheet application for this
phase. I can't use Access right now because my boss
doesn't think I need access to Access <LOL>. Besides, that
is another database to learn while I'm still learning
Excel.


There will be imported data with the fields Property_Name,
and Product Code. For each record, there will be at least
1 corresponding database record on a separate worksheet
with those same field values, as well as additional
fields , Pun, School, and Suffix.

The first operation is to place a number in a column
beside the right most column of the imported data records
for each record that is a count of the number of matching
records in the table named database.

To further illustrate:

In Sheets(“Imported_Data”)

Property Name Product Code
ABC Property 2
DEF Property 2
HIJ Property 1

In Sheets(“Database”)

Well Name MOC Prod NM Prod Code
Pun School Suffix
ABC Property 2 04
1234567 1510 S
ABC Property 2 04
1234567 1510 F
ABC Property 1 04
1234567 1510 S
ABC Property 1 04
1234567 1510 F
DEF Property 2 04
1357900 1530 F

The field names in the two sheets have different names,
but you can tell the similar data. For the first property
in the Data sheet above, ABC Property with product code 2
has two matches in the database sheet. Lines 3 and 4 in
sheet database are not a match for the ABC property
because the second column does not match the second column
in the Data column. Therefore, in sheets Data, in the
field “records to copy”, the value 2 would be placed. This
means that the data record ABC property in Sheets Data
would need to be copied 1 additional time, with all the
rows found in the row for that property in the sheet Data.
For property DEF Property. There is only 1 match, so the
value 1 goes in its “Records to Copy” column. Property HIJ
Property has 0 matches, so the value 0 will go in there
for it.

So, how would I set up a macro to evaulate for each
imported_data record, to mark how many matching records it
finds in the table Database?

Thanks,


Bruce

 

[keepitcool]

Bruce..

This'd be an ideal time to look at
DataQueries and PivotTables.

For what you intend to do, it's ideally suited.

Since the data is coming from multiple tables
you'll need to use a query to retrieve the data you need.

Step1.
Make sure MsQuery is installed
(it's an optional component under Office Tools I believe)

Step2.
Build a query to select the tables, define the joins (relationships
between key fields in the various linked tables) and select the fields
and criteria for the data you want to retrieve. (Data/ New Database
query)


Step3.
Run the query.

Step4.
Then fire up the PivotTable Wizard from the Data menu.


Step 1a,2a,3a,4a
Take a few minutes to read help on the subject.
You'll find it wont be time wasted.


Later you'll learn that the query and the pivot
can be combined. Not to complicate matters
use the split approach in the beginning.


Have fun!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

I am evaluating whether to change how I do a severance tax
several linking tables in the spreadsheet for each new
property that is added.

phase. I can't use Access right now because my boss
doesn't think I need access to Access <LOL>. Besides, that
is another database to learn while I'm still learning
Excel.

I agree with your Boss..