+ Reply to Thread
Results 1 to 5 of 5

Merging a "physical count" to a "system on-hand"

  1. #1
    Registered User
    Join Date
    12-19-2010
    Location
    Akron, Ohio, USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Merging a "physical count" to a "system on-hand"

    Hi everyone! My name is Robert and I am in a jam! Please help!
    Ok, Ill try to make it simple...I have two sheets, One is a "physical count of items", the other is a "system on-hand" I want to merge them together to produce a "reconciliation" sheet.

    Example:

    Physical count sheet looks like this;

    Col A Col B
    Item Count

    111111 1
    222222 1
    333333 3
    444444 2
    999999 1

    On-hand Sheet looks like this

    Col A Col B Col C
    Item On-hand Avg Cost
    111111 1 $1.00
    222222 1 $1.00
    333333 2 $1.00
    444444 1 $1.00
    555555 1 $1.00

    As you can see, there is a need to reconcile, I would like to merge the two to produce something like this...

    *NEW* Reconcile sheet

    Col A Col B Col C Col D
    Item On-hand Avg Cost Phys Count
    111111 1 $1.00 1
    222222 1 $1.00 1
    333333 2 $1.00 3
    444444 1 $1.00 2
    555555 1 $1.00 0
    999999 0 $0.00 1

    *Note - item "555555" was not physically counted but is present in "on-hand" sheet & item "999999" was physically counted but did not match up with "on-hand" sheet; hence "zero" average cost.


    If I had money I would pay you! But i don't

    Thank you so much for your help!

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Merging a "physical count" to a "system on-hand"

    Hi raharms

    To analyze your issue, one of us would probably need to build a workbook. You've essentially done this with your sample data. Please take it another step and put the data in an ACTUAL workbook then upload the workbook; include all formulas. One of us will be able to assist you.

    John
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Merging a "physical count" to a "system on-hand"

    Hello raharms,

    Welcome to the Forum!

    I have attached an example workbook based on your post. The macro below has been added to the attached workbook. The "Reconciliation" sheet has a button which runs the macro.

    Reconciliation Macro Code
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Merging a "physical count" to a "system on-hand"

    Hello raharms,

    As a courtesy, we ask that members please post the links to other groups where they have posted their question. This reduces duplication of answers and respects the time, effort, and knowledge of those helping you.

    As a new member, please take some time to familiarize yourself with the rules.
    Forum Rules

    Cross posted at http://www.mrexcel.com/forum/showthread.php?t=516416

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Merging a "physical count" to a "system on-hand"

    READ THE FORUM RULES, YOU'VE BROKEN AT LEAST 3 in 2 POSTS
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ 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