Forum Discussion

QTS_JordanB's avatar
QTS_JordanB
Copper Contributor
Oct 12, 2023

Return Top 10% Items Sold to Another Tab

Hello,

I have a table that shows Items sold by the company I work for, sorted descended by units sold per item. My goal is to create a list of the highest volume sold items so we can cycle count our inventory.

I would like to return a top % of the highest volume selling items to a separate tab, because my raw data is refreshed automatically from a database.

Is it possible to do this while also returning other information as well, such as item description, quantity on hand, and bin location, as an example?

My goal is to create a printable sheet, for the purpose of cycle counting the top 10% of our most sold items, that is separate from the raw data, yet still has enough information to be used for conducting the inventory such as: Item code, quantity, bin location, and item description.


Thanks!

 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    QTS_JordanB 

    You can create a separate tab or sheet that shows the top 10% of the highest volume selling items from your raw data, along with additional information like item description, quantity on hand, and bin location. You can use Excel's functions and features to achieve this. Here is a step-by-step guide on how to do it:

    Assuming your raw data is in a sheet called "RawData" and looks something like this:

    Item Code

    Item Description

    Quantity Sold

    Quantity on Hand

    Bin Location

    Item1

    Description1

    100

    200

    A1

    Item2

    Description2

    90

    180

    B2

    ...

    ...

    ...

    ...

    ...

    Here is how you can create a separate tab with the top 10% of items:

    1. Calculate the Threshold:
      • In your new sheet (let's call it "Top10Percent"), you'll first need to calculate the threshold for the top 10%. You can do this with a formula. In a cell, enter the following formula to calculate the threshold:

    =PERCENTILE.INC(RawData!C2:C100, 90%)

    This formula calculates the 90th percentile for the "Quantity Sold" column in your "RawData" sheet. Adjust the cell references and the range as needed to match your data.

    1. List the Top Items:
      • In the "Top10Percent" sheet, create a table that lists the items where "Quantity Sold" is greater than or equal to the threshold you calculated. You can use an IF function or filters to extract this data. For example, if your data starts from row 2, you can use the following formula in the "Item Code" column of the "Top10Percent" sheet:

    =IF(RawData!C2 >= Top10Percent!$A$1, RawData!A2, "")

    This formula checks if the quantity sold in the "RawData" sheet is greater than or equal to the threshold, and if so, it returns the item code. Otherwise, it returns an empty string.

    1. Additional Information:
      • To retrieve additional information (item description, quantity on hand, bin location), you can use VLOOKUP or INDEX/MATCH functions to match the item code from your "Top10Percent" sheet with the data in the "RawData" sheet. For example:

    =VLOOKUP([@Item Code], RawData!$A$2:$E$100, 2, FALSE)

    This formula retrieves the item description based on the item code in your "Top10Percent" sheet.

    1. Filter for Non-Empty Rows:
      • To filter for non-empty rows in your "Top10Percent" table, use Excel's AutoFilter feature. This allows you to show only rows where the item code is not blank.

    With these steps, you can create a separate sheet that shows the top 10% of the highest volume selling items, along with their item description, quantity on hand, and bin location. This sheet can be printed for inventory purposes while remaining separate from your raw data. Adjust the formulas and references to match the specific layout of your data. The text, steps and functions were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources