Forum Discussion
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!
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:
- 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.
- 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.
- 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.
- 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.
