Compare an array in one file against contents of another file

Originally Posted by ChemistB

In G1 copied down

Formula:

Please Login or Register  to view this content.

In H1 copied down

Formula:

Please Login or Register  to view this content.

wow, that was FAST! by region settings, I am guessing since we are both in the Yew Ess, I shouldn't replace all , that are inside of parentheses with a ; ? And I will give these a try and respond. Thanks, ChemistB, from pharmacistR (Ron).

In migrating the example to my realworld computer, putting these formulae into place does not result in the appearance of formulaic calculations - the text remains in each cell instead of being acted upon. I have checked "calculate" now and "calculate sheet", no change.
in this formula,
=IFERROR(INDEX('C:\Users\arc5627\Documents\dncs\[dnc.xlsx]Sheet1'!$A$1:$A$200, MATCH(B1,'C:\Users\arc5627\Documents\dncs\[dnc.xlsx]Sheet1'!$B$1:$B$200,0)),"") ,
the parentheses after INDEX is red, as is it's closing paren mate. It is as though the B1 in the middle, right after the term "MATCH(", is not recognized the B1 as a cell reference to the spreedsheet it is in.

for the other formula,
=IF(COUNTIF('C:\Users\arc5627\Documents\dncs\[dnc.xlsx]Sheet1!$B$1:$B$199,B2),"DNC","")
It also doesn't seem to "take" as a formula when I copy it into the cells.
This is probably something simple about Excel. ?
Here's what it looks like:
example.PNG

I deleted both columns, recreated them, formatted them both as general. Also verified that both the DOB columns in the two files are formatted date:12/01/2020 .(ie, the same).
I typed the formula rather than copy/paste, and then drag it down the entire column of rows.
In the first (F) Column, I get #Value! in each cell.
In the second I get some cells populating with a name retrieved from the reference spreadsheet, but not by DOB value - some names appear 3 or 5 times, none correspond to the actual DOB in the test spreadsheet. I would post the actual spreadsheet but they contain patient-sensitive data.
What I am noticing in the second column with this:
=IFERROR(INDEX('C:\Users\arc5627\Documents\dncs\[dnc.xlsx]Sheet1'!$A$1:$A252,MATCH(B1,'C:\Users\arc5627Documents\dncs\[dnc.xlsx]Sheet1'!$B1:$B$200,0)),"")
is that as I pull the cross down to populate the cells down the column, the ranges change, ie, in the above, the $A$1:$A$200 increases to $201, $202, etc. and the second range, $B1:$B$200 increments to $B2:$B$200 . I would expect ONLY the B1 reference (MATCH(B1, ) to increment as I pull the formula down. ??

very true. I reckon it was because I failed to type a $ in the "$B1", so that it treated the range properly - I should have typed "$B$1" . That solves the error on the formula of the second column I was causing, I think. It is retrieving names properly now.

I am confused why for the first column formula of
=IF(COUNTIF('C:\Users\arc5627\Documents\dncs\[dnc.xlsx]Sheet1'!$B$1:$B$200,B1),"DNC","") ,
The second paren and the next-to-last paren are in red (as if something is wrong in the path), and the cell never calls out to the dnc.xlsx file like the formula in the second column does(twice), and the cell populates with the #VALUE! result - the path is exactly the same as in the formula of the second column which is working. Does COUNTIF have the ability to reference a different, closed spreadsheet file in a path'd location? The cell, B1, referenced does show in blue in the formula bar.

ahhhh....relief...... Problem: The formula refers to cells in a closed workbook

COUNTIF/COUNTIFS functions that refer to a cell or a range in a closed workbook will result in a #VALUE! error.

Note: This is a known issue with several other Excel functions such as SUMIF, SUMIFS, COUNTBLANK, to name a few. See SUMIF, COUNTIF and COUNTBLANK functions return #VALUE! error for more information.

So ChemistB's solution does work, but only if I open the other file.... Is there a formula that allows that file to remain closed?
"Don't ask why this happens - it's one of life's little mysteries. "

ChemistB - thank you for your patience. I cannot try this until I return to the pharmacy Monday, but I promise to attempt it asap the workload of prescriptions allows/provides. thank you. Have a mellow weekend! ( I do have to work on Saturday, but not much free time due to 50% reduction in staff for well, current influential reasons).

BTW - in the 80s and until around 9/11 I was a real wizard with relational databases using a real-time C++ compiler running pharmacies in hundreds of hospitals throughout the US and one<G> in Canada but I refused out of spite to glom onto Lotus 1-2-3. I have had multiple occasions to regret that bias, such as now....

chemistB, that last was the cat's meow. It is so nice to see the column populate with the correct data, and the second column to provide secondary confirmations! I have a technician that can stop spending an hour each week doing scutwork thanks to the formulae doing that work in a few seconds, as long as I follow a series of steps: pasting the doc just in case it shows it all.
Here is the doc I created based on your formulae and help -it leaves out all the screen snippets but you'll get the jist of it:
Background:
We have a lot of patients in our clientele that do not like to be called with PMAR reminders, which we refer to as DNC (Do Not Call) and it is currently nearing 200 patients in all.
We spend a LOT of time with each new weekly PMAR report, first identifying all the patients that are DNC and marking them on the PMAR report. This process used to occupy more than an hour since the tech had to look them up in McKesson to see notations in patient information.

I started a reference spreadsheet of names and DOBs of all the DNCs marked on the "done" PMAR reports. It's nearly 200 patients so far but it is easy to add new DNCs as they present. Column A is the patient name as listed in the PMAR and Column B is their Date of Birth (DOB). I just type them in and save it each time it is updated.
The spreadsheet is called dnc.xlsx, kept in a folder named dncs under my Documents folder on my C: drive.
The absolute path for my workstation includes my Login Name "arc5627", so it is: C:\Users\arc5627\Documents\dncs\dnc.xlsx . As we encounter another patient requesting DNC we simply update the spreadsheet. I sort alphabetically on the names so that we can easily tell whether a patient is already in the file. We ONLY put each patient in once. We keep a current copy printed out after any updates for fall-back reference as described below.

Again, in dnc.xlsx , Column "A" is the person's name and "B" is their DOB. It's important you make sure that column B has a format which is the same as the PMAR sheet which is sent out, DATE *3/14/2012 .
The spreadsheet has a single page named "DNC" and remains closed at the location above during operations on the PMAR reports sent out I describe below.

Each week I am given a different password-protected spreadsheet PMAR with a single page named "Report 1", which I save to my desktop, name unchanged.

I open this sheet and highlight Column A below the header, and choose to sort it alphabetically, expanding to include everything when asked by Excel. This groups all meds by the patient name which eliminates calling a patient twice because they had one Rx on line 1 and two more on line 55.
After sorting, I SAVE the spreadsheet.
I then highlight all data, use Ctrl-C which copies all the data, and I then open a new spreadsheet and CTRL-V to paste all of the data in it.
THEN CLOSE THE ORIGINAL SPREADSHEET.

In the new spreadsheet:
Choose the Column B, DOBs, and format it to be DATE, *3/14/2012.

Choose the column just after the name of the drug (Usually Column E) and INSERT two new columns which pushes the other data to the right.

FORMAT each of these two columns as GENERAL.

CTRL-S to save this new spreadsheet into the dncs folder with a name you can identify the data as - I use PMAR01-03 (if the original PMAR report file was "_202 PMAR 01-03-2021 to 01-09-2021")
Now to make the new spreadsheet operational.
Delete the header row with the titles for each column, so that Row 1 is the first patient in the data.
The first new blank column is "E" so put your cursor into this cell on row 1.
Highlight the single line of code below and copy/paste it into the formula bar ABOVE all the cells, EDIT "YOURLoginNameHere" to be your login name and hit enter so it is inserted into Cell E1.
=IF(ISNUMBER(MATCH(B1,'C:\Users\YOURLoginNameHere\Documents\dncs\[dnc.xlsx]DNC'!$B$1:$B$10,0)),"DNC","")
This is what mine looks like:
YourLoginNameHere must match the folder in your Users folder on the C: drive, and it is case sensitive, probably all lowercase but you need to actually check using file explorer.
If you did this correctly your cursor moves to Row 2, Column E .
Click into Row 1, Column F
Highlight the single line of code below and copy/paste it into the formula bar above all the cells, edit YOURLoginNameHere again as above, and then hit enter to insert this code into Cell F1.
=IFERROR(INDEX('C:\Users\YOURLoginNameHere\Documents\dncs\[dnc.xlsx]DNC'!$A$1:$A$200,MATCH(B1,'C:\Users\YOURLoginNameHere\Documents\dncs\[dnc.xlsx]DNC'!$B$1:$B$200,0)),"")
BE SURE TO CHANGE the YOURLoginNameHere to be your own LoginName folder in both instances.
Here is what it looks like for me.

Now you need to populate each entire column E and F with the formula that each has in Row 1.
Click into line 1, Column E (Cell E1) and hover over the lower right corner of this cell until your mouse pointer turns into a + .
There will be a + symbol right on the green square.

Left-Click and hold, then drag down this column to the cell just below the last line of patient listings and release the left mouse button. The cells will be bounded by a green highlight as you go down the page:
Each cell is now checking to see if any DOBs in column B match ANY DOB in the reference spreadsheet called dnc.xlsx. IF a MATCH is found, the cell will populate with DNC. (Do Not Call). Column E will look like this:

NO MORE LOOKING UP EVERY PATIENT EACH WEEK!
There is a possibility that the DOB match may be to a different patient name with the same DOB. Therefore you need to repeat the click/drag and populate all of column F with the formula in Row 1, Column F so that the referenced patient DOB's name is also displayed to verify against Column A's name.
Click into line 1, Column F and hover over the lower right corner of this cell until your mouse pointer turns into a + . Left-Click and hold, and drag down to the cell just below the last line of patient listings and release the left mouse button. Each cell is now checking to retrieve the patient's name ONLY if there is any DOB match from column B in the reference spreadsheet called dnc.xlsx. So for every cell in E that has DNC, the F cell should show a name. Re-check everything above if you don't have any DNCs or else you have DNCs and no corresponding names in the cell to the right of the DNC .
IF THE NAME IN COLUMN F DOES NOT MATCH THE NAME IN COLUMN A and it won't occasionally, you will still need to check the patient's name in the dnc.xlsx by hand to verify the actual name is a DNC patient. We keep a paper printout of dnc.xlsx with all patients sorted in alpha order for this situation.
Insert a new row above row 1, and type in the PMAR date info into A1 so when you print a hard copy it indicates the date to start calling.
Highlight Columns A through F and print them as a landscape output and make your PMAR calls.
Once all are called and notations made, you must enter that information into the original spreadsheet on your desktop as you normally would and send to Lonna.
Once done, the matching "active" spreadsheet you created with the formulae in E & F can be archived or deleted.

This thread hereby declared solved, thanks to ChemistB ! Thanks....