+ Reply to Thread
Results 1 to 4 of 4

Find the most occurring values in Column A and consolidating each value's quantity

  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    3

    Find the most occurring values in Column A and consolidating each value's quantity

    Hi All,

    As part of my work as an intern, my company wants me to look at all of the office supplies ordered by their retail stores and find out which items are most ordered.

    The invoice excel file I received from our office supplier has a column for the Item # and Quantity of that item. However, there are multiple occurrences of each Item # because this invoice is itemized by store. Is there a way to consolidate all of the values in the Item # with each other to remove the duplicates while also consolidating all of the quantities of each?

    For example:
    Office Supplies 2.PNG

    The invoice is much longer and has many more different Item #s and many more duplicates but this is more or less the same format.

    I used COUNTIF with an advanced filter with Unique Records Only selected but I realized that only gave me the count of the Item #s and didnt factor in the Quantity of each Item # ordered by each retail location.

    Thank you!
    Last edited by savagebabs; 06-12-2014 at 03:18 PM.

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

    Re: Find the most occurring values in Column A and consolidating each value's quantity

    You don't say what version of Excel you have but there are a number of ways.

    You could use formulas, probably a SUMIF formula
    or you could use a pivot table
    Set the Item # as a row item and SUM of Quantity in Data Field.
    I'd recommend the Pivot table as it is easily rearraigned to gleam additional information.
    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
    06-12-2014
    Posts
    3

    Re: Find the most occurring values in Column A and consolidating each value's quantity

    Quote Originally Posted by ChemistB View Post
    You don't say what version of Excel you have but there are a number of ways.

    You could use formulas, probably a SUMIF formula
    or you could use a pivot table
    Set the Item # as a row item and SUM of Quantity in Data Field.
    I'd recommend the Pivot table as it is easily rearraigned to gleam additional information.
    I apologize for not saying, but I'm using Excel 2010.

    How would a SUMIF formula work in regards to the three parts contained within the parentheses (range, criteria, [sum_range])?
    I'm assuming range would be column A, and criteria would be each individual item #?

  4. #4
    Registered User
    Join Date
    06-12-2014
    Posts
    3

    Re: Find the most occurring values in Column A and consolidating each value's quantity

    Messing around with the SUMIF formula I figured it out. Thank you for your help, ChemistB!

+ 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. Find most occurring text within a Table column
    By XxCMoneyxX in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-27-2012, 09:44 PM
  2. Color all cells with values that are conditional on quantity column
    By bainne in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-18-2012, 02:45 PM
  3. Consolidating numeric values into one column
    By jumbles in forum Excel General
    Replies: 7
    Last Post: 07-03-2011, 04:04 AM
  4. Consolidating the values in next column
    By Upkar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2010, 06:06 AM
  5. Replies: 3
    Last Post: 06-06-2009, 12:18 PM

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