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