+ Reply to Thread
Results 1 to 11 of 11

Inventory Reconciliation

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Inventory Reconciliation

    Hello Everyone,

    I am new the forum, but have been a lurker for-sometime now. With that said I've learned a vast amount of information; yet, now i find myself in dire need of a little help.

    To start, I'm trying to reconcile inventory (RX Scripts) that currently does not have a unique identifier. Though, I've created one with the data that appears on both the inventory sheet and the receipt. i.e. RX number and fill date combined together, which provides us with a relative unique number.

    However, in pharmacy we have secondary and primary billing which will send the same RX and fill date to two different insurance payers. One pays half and the other pays the remaining amount. Which throws off values on the inventory sheet where we reconcile the claims. Thus causing us to go in and add another unique number to each claim to make it truly unique. When you have only ten claims it's not an issue, but when we have a thousand or more it becomes a huge issue.


    Currently, we are using the function of if(isna(Vlookup( to provide reconciliation. If you have any helpful information, I would be forever grateful.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Inventory Reconciliation

    as a lurker
    should know provide some sanitized sample workbook helps greatly for anyone trying to understand your problem

    so does the inventory item or script itself have no unique item code?
    you have an rx number which is not unique? but you plug on a date to make it unique?
    i would assume rx number has to be unique ....what happens when fill two or more the same day?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Inventory Reconciliation

    An RX number is unique to the individual patient and schedule of drug. Therefore, an RX number does not correlate directly to specific drug, but a schedule of drug. When a patient comes in next month she will be given the same RX for that drug but a different fill date will be used.

    Quote Originally Posted by humdingaling View Post
    as a lurker
    should know provide some sanitized sample workbook helps greatly for anyone trying to understand your problem

    so does the inventory item or script itself have no unique item code?
    you have an rx number which is not unique? but you plug on a date to make it unique?
    i would assume rx number has to be unique ....what happens when fill two or more the same day?

  4. #4
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Inventory Reconciliation

    Attached you will find the trial data.


    example data.xlsx

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Inventory Reconciliation

    hmm doesnt really look like an inventory sheet, are you reconciling payment?

    is there ever cases where only 1 payer would occur?
    could you not assign code for the payer and add that in front of the RX + Date to make it truly unique?
    then only deduct inventory from 1 payer only?

  6. #6
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Inventory Reconciliation

    95% of the time it's just one payer. we've looked into adding a unique number for each payer, but payer names are not always the same on the funding and payment file.

    Quote Originally Posted by humdingaling View Post
    hmm doesnt really look like an inventory sheet, are you reconciling payment?

    is there ever cases where only 1 payer would occur?
    could you not assign code for the payer and add that in front of the RX + Date to make it truly unique?
    then only deduct inventory from 1 payer only?

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Inventory Reconciliation

    yes i noticed it is not the same which is why i suggested code
    maintain a seperate table/sheet with payers
    A = Prime
    A = PRIME THERAPEUTICS (011552)
    B = PHARMACY DATA MANAGEMENT (610020)
    B = PDMI
    i would imagine there isnt that many iterations/combinations and this is a one off thing
    ps just example i am sure you can be more creative as this would only yield 26 payers

    would not imagine it changes every transaction or your customer database is severally compromised

    at front or back of code
    if for any reason you do not need that in the code when you evaluating/comparing you could always LEFT or MID it out of the string

  8. #8
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Inventory Reconciliation

    There are currently 400 plus combinations, that I know of. That number grows and changes dependent upon the insurance company that week.

    Quote Originally Posted by humdingaling View Post
    yes i noticed it is not the same which is why i suggested code
    maintain a seperate table/sheet with payers
    A = Prime
    A = PRIME THERAPEUTICS (011552)
    B = PHARMACY DATA MANAGEMENT (610020)
    B = PDMI
    i would imagine there isnt that many iterations/combinations and this is a one off thing
    ps just example i am sure you can be more creative as this would only yield 26 payers

    would not imagine it changes every transaction or your customer database is severally compromised

    at front or back of code
    if for any reason you do not need that in the code when you evaluating/comparing you could always LEFT or MID it out of the string

  9. #9
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Inventory Reconciliation

    crickets, crickets, crickets, crickets, crickets, crickets

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Inventory Reconciliation

    Will there every be more than 2 payers per RX?

    if not try this out
    insert column and following formula
    Please Login or Register  to view this content.
    what it would do is add A for first count, B for second count of RX number + Date
    making truly unique RX number

    couldn't really test it on your sample size but it should work in theory

  11. #11
    Registered User
    Join Date
    07-02-2013
    Location
    Scottsdale
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Inventory Reconciliation

    Thank you, I'll give this shot.

    Quote Originally Posted by humdingaling View Post
    Will there every be more than 2 payers per RX?

    if not try this out
    insert column and following formula
    Please Login or Register  to view this content.
    what it would do is add A for first count, B for second count of RX number + Date
    making truly unique RX number

    couldn't really test it on your sample size but it should work in theory

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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