+ Reply to Thread
Results 1 to 4 of 4

Help! How do I combine duplicates with different counts of inventory?

  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    Cincinnati, OH
    MS-Off Ver
    Excel 2007
    Posts
    2

    Help! How do I combine duplicates with different counts of inventory?

    Hello everyone! I'm new to this forum so I'm hoping I can find some help here! Thanks in advance for your time!!

    Here's my situation:

    I'm doing an inventory count on my company's sold product for the past two years. The only way to look this up is to compile all of the data (copy from our software and paste to an excel sheet). I've completed this step. The problem is that there are duplicates of the same item, but with different usage counts. It looks like this:

    ITEM (Column A) # of times sold (Column B)

    abc 3
    abc 7
    def 1
    ghi 4
    ghi 1
    ghi 1

    I need this to 1) combine the # of items sold column in correlation to the item #, and 2) to remove the duplicates. So it should look like this:

    abc 10
    def 1
    ghi 6

    Does anyone have a solution to this? After (if) I can solve this, I think I can do a vertical lookup to weed out the target items I am trying to locate. Again, thank you so much for your time and effort! I REALLY appreciate it!

    -tybles

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Help! How do I combine duplicates with different counts of inventory?

    hi tybles, welcome to the forum. 2 methods. 1 is a pivot, the other is using formulas. in column G is an array formula. you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

    adjust the ranges accordingly to your needs.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    02-14-2013
    Location
    Cincinnati, OH
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Help! How do I combine duplicates with different counts of inventory?

    Thank you so much for your help, benishiryo! I still don't quite understand how to operate the pivot table, but the formula worked like a charm! You're a lifesaver!

  4. #4
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Help! How do I combine duplicates with different counts of inventory?

    Good morning Tybles,

    benishiryo has nailed it on the formula side. It is definietly also worth taking a couple of minutes to play in the pivot tables, as they can be very handy to show you different things in a short turnaround time from the same data. Where formulas will show you what you want to see with one set of criteria, pivots can be changed with the click of a mouse button to show you different things.

    I have attached a little "Pivot table training" workbook that might get you started if interested. Hope it helps

    Have a good day
    Attached Files Attached Files
    Handy things to keep in mind:

    Click *, if my suggestion has helped you
    If your problem is solved, then please mark the thread as SOLVED

    Sharing is Caring .... spread the knowledge

+ 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