Return Top 10% Items Sold to Another Tab

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:

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.