Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluating new project idea
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluating formula in VBA | New Users to Excel | |||
Evaluating division by zero | Excel Worksheet Functions | |||
Function not evaluating | Excel Discussion (Misc queries) | |||
Excell error "Can't find Project or Library" Project VBAProject | Excel Worksheet Functions | |||
How to convert MS Project to MS Excel. I don't have MS Project. | Excel Discussion (Misc queries) |