- 1 year ago
Looping through sales data and move sales over a certain amount to a new worksheet
If you want to loop through sales data in Excel and move sales over a certain amount to a new worksheet using Python, you can use the openpyxl
library. Here's how you can achieve this:
- Install the
openpyxl
library if you haven't already:
bashpip install openpyxl
- Create an Excel workbook with your sales data and create a Python script to process it.
Here's an example script that demonstrates how to loop through sales data in one worksheet, filter sales over a certain amount, and move them to a new worksheet:
pythonimport openpyxl
def move_sales_over_threshold(input_file, output_file, threshold):
# Load the input workbook
wb = openpyxl.load_workbook(input_file)
# Get the input worksheet
input_ws = wb.active
# Create a new worksheet for filtered sales
output_ws = wb.create_sheet(title="High Sales")
# Write headers to the output worksheet
output_ws.append(input_ws[1])
# Loop through rows in the input worksheet
for row in input_ws.iter_rows(min_row=2):
sale_amount = row[1].value # Assuming sale amount is in the second column
# Check if sale amount is above the threshold
if sale_amount > threshold:
# Copy row to the output worksheet
output_ws.append([cell.value for cell in row])
# Delete the original row from the input worksheet
input_ws.delete_rows(row[0].row)
# Save the updated workbook
wb.save(output_file)
if __name__ == "__main__":
input_file = "input.xlsx" # Replace with your input file
output_file = "output.xlsx" # Replace with your output file
threshold = 1000 # Specify your threshold amount
move_sales_over_threshold(input_file, output_file, threshold)
print("Sales moved to new worksheet.")
Replace "input.xlsx"
and "output.xlsx"
with the actual filenames of your input and output Excel files. Adjust the threshold
variable to specify the sales amount above which you want to move the data.
Note that this is a simple example, and you may need to adjust it to match the structure of your sales data and Excel file. Make sure your input file has a header row and the sale amount column is appropriately identified.
Also, ensure that the openpyxl
library is installed, and the script is executed in the same directory as your input Excel file.