+ Reply to Thread
Results 1 to 17 of 17

Compare an array in one file against contents of another file

  1. #1
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Compare an array in one file against contents of another file

    I have a reference spreadsheet of names and DOBs that rarely changes. Column "A" is person name and "B" is DOB. The spreadsheet has a single page named "Sheet1" and remains closed at location "C:\dncs\dnc.xlsx".
    Each week I am given a different spreadsheet with a single page named "Report 1", called "checkme.xlsx", which I must compare to the reference spreadsheet called dnc.xlsx .
    The first two columns in checkme.xlsx are person and DOB, along with data in columns C through F which are irrelevant.
    If I find the same DOB in checkme.xlsx as exists in ANY ROW of Sheet1 of dnc.xlsx then I must type into checkme.xlsx on the matching DOB row the text value "DNC" into Column G of Report 1 and also type in the name (from column A of Sheet1 of dnc.xlsx, same row as the DOB) into Column H of Report 1 of checkme.sls.

    IN THE ATTACHED EXAMPLE FILE OF CHECKME.xlsx I HAVE DONE THIS FOR THE TWO ROWS THAT HAVE MATCHING DOBS. I just want this to happen by formulae instead of me laboring over it each week.

    What I need to figure out is the syntax for vlookup to compare the value in column B (the DOB) in each row of checkme.xlsx against the entire array of DOB values from the reference spreadsheet called dnc.xlsx and return the text "DNC" into Column G on the appropriate row of checkme.xlsx if the two DOB values match.
    If G populates with DNC (no quotes in the cell) then the formula for column H of Report 1 needs to fetch the contents of Column A(name) from the reference spreadsheet dnc.xlsx.

    My hope is that I can do a global paste of the formulae for both "G" and "H" each time I get a new checkme.xlsx and have those two columns populate accurately and automagically. The page names and the files names/locations never change, and I won't be editing the reference file while accessing the checkme.xlsxx file.

    For column G, I've tried defing an array lookup like this for Column G in the checkme.xlsx file: =IF(ISERROR(VLOOKUP(A1,'C:\dncs\[dnc.xlsx]Sheet1'!$A$1:$A$200,1,FALSE)),"","DNC")

    For H, I've tried something like this for column H in the checkme.xlsx file: =IF(G1="DNC",VLOOKUP(A1,'C:\dncs\[dnc.xlsx]Sheet1'!$A$1:$A$200,1,0),"")

    There are NOT 200 records in the real dnc.xlsx, but it may over time increase to close to that - it currently has 156 rows of data, I don't know if that matters.
    These two formulae do not work as described above.
    What do I have wrong or maybe, do I have anything remotely right?
    Attached Files Attached Files
    Last edited by TexARC; 12-20-2020 at 09:45 PM. Reason: Solved by ChemistB

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,279

    Re: Compare an array in one file against contents of another file

    In G1 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In H1 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ChemistB
    My 2Ę

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    Quote Originally Posted by ChemistB View Post
    In G1 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In H1 copied down
    Formula: copy to clipboard
    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).

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,447

    Re: Compare an array in one file against contents of another file

    Replacing the comma separator with a semi-colon is only required in a locale where the comma is used as a decimal separator. Most of Europe is affected, but the English-speaking world isnít, so US, UK, Australian locales etc. will use the comma as the formula separator.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,447

    Re: Compare an array in one file against contents of another file

    This is a known issue.

    Remove the formulae in the column in question, and then, with the column selected, ensure that the formatting is set to general. Then enter the formula into the first cell required and drag copy down. Rinse and repeat for other affected columns.

    Don't ask why this happens - it's one of life's little mysteries.

  7. #7
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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. ??

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,447

    Re: Compare an array in one file against contents of another file

    Pretty hard to say without seeing the file itself.

  9. #9
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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.

  10. #10
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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.

  11. #11
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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. "

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,279

    Re: Compare an array in one file against contents of another file

    Sorry about that. Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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).

  14. #14
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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....

  15. #15
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

    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.

  16. #16
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,279

    Re: Compare an array in one file against contents of another file

    Glad things are working for you. Sounds like you have it under control. Use the thread tools in post 1 to mark this thread as "Solved."

  17. #17
    Registered User
    Join Date
    12-16-2020
    Location
    Lago Vista, TX
    MS-Off Ver
    Office 2019
    Posts
    11

    Re: Compare an array in one file against contents of another file

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need to clear contents of an excel file using command prompt or a batch file
    By ananthblaze in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-29-2020, 03:17 PM
  2. Compare file list with contents of folders then hyperlink
    By ShirleyP in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-02-2015, 05:50 PM
  3. [SOLVED] Code to open another excel file, file name depending on cell contents
    By L-Drr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2013, 11:09 AM
  4. [SOLVED] Macro to create text file and write contents of selected cells to the file
    By m@cro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2013, 10:21 AM
  5. [SOLVED] reading contents of a file without opening the file using INDIRECT function
    By luv2glyd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-22-2013, 01:55 PM
  6. Replies: 0
    Last Post: 03-04-2013, 03:40 AM
  7. Compare file names to folder contents
    By marlea in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2006, 11:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1