Oct 12 2023 02:38 PM
Oct 12 2023 02:38 PM
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.
Oct 13 2023 10:30 AM
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:
Quantity on Hand
Here is how you can create a separate tab with the top 10% of items:
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.
=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.
=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.
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.