1

Currently, I am using Excel formulas between two sheets to complete this task: to merge score data on one sheet to the appropriate attendee on another. It is a bit tedious and you have to be comfy with data manipulation, which I am; however, I want anyone stepping up to the plate to be able to perform this task, so something more automated is preferable. Any/all ideas welcome...

Sheet 1 contains four columns: A full ID number, name, three columns of scores

Sheet 2 contains more data: The same ID number but only last four, name but in separate columns needed for merge purposes, email, address, etc. Shot of Sheet 1 and 2

We need to be 100% sure that the person to whom we are emailing a score letter is the person who took the exam, based on the ID number and their name. Some known issues: the last four alone cannot ID (duplicates exist); as you can see from 'Scores,' our sheets are often returned with typos, missing letters and/or special characters (' in O'Hara).

I start with sheet 1 and make a combo of the last four of the ID and the first three of their last name:

=RIGHT(B2,4)&LEFT(C2,3) then COPY AND PASTE VALUES INTO COLUMN A

Next, for Sheet 2, I add the four-digit ID number with the first three of last name:

=I2&LEFT(C2,3) COPY AND PASTE VALUES INTO COLUMN A

I then perform a VLOOKUP in an array:

{=VLOOKUP(A2,Sheet1!A:H{6,7,8},FALSE)}

and ultimately, get this: Final sheet before distribution

It works, it's just not perfect. If this could be automated in any way, that would be great. I am not opposed to using FileMaker Pro and scripts; I'm just a script newb and would need to be told what the script should be. Thank you.

MJH
  • 1,155
Kathy McEvers
  • 11

1 Answers1

0

You could definitely do it easy and automatic in FileMaker.

Steps: Import the two sheets into separate tables in the same file. Make a calculation field in each of the tables that gives the same result for each same person in the two tables. A combination of the last 4 digits of the ID and name as you have done in Excel. FileMaker has left and right functions with the same syntax as Excel that you can use. Then create a = relationship between the tables using the calculated fields on both ends.

After that, you choose one of the tables to build your final table layout in and include the fields you need from both tables. Next time, just delete everything and import fresh data and you are done.

No scripts necessary unless you want to automate deletions and imports.

These script steps would work: Go to layout (table1) Show all records Delete all records (no dialog) Go to layout (table2) Show all records Delete all records (no dialog) Go to layout (table1) Import dataset 1 Go to layout (table2) Import dataset 2

AndreasT
  • 156