Tag Archives: excel

How to create a drop down menu in Excel

This short tutorial will show you how to create a drop down menu in Excel. The Drop down menu will allow you to ensure that only the correct data gets entered into a field.

In this example we want to ensure that the end user can only enter the following information into a selection of cells

  1. On Time
  2. Late
  3. Early
  4. Cancelled.

Step 1

Enter the the options for your drop down menu in cells in your workbook. in Our case we entered them in column M.

Step 2 

Now highlight the cells you want to have the drop down menu in and click Data.

Step 3

Click data Validation and data validation again

Step 4

In the box that appears select List

Step 5

Click into the Source menu box and then highlight the values that you want to be in your drop down menu, in our case M2 to M5. Then click ok

Step 6

Your Drop down options should now appear:

A full video tutorial ca be found here:

Python: Searching CSVs and exporting rows based on string macthes

Recently I had a need to search through a CSV log file, that was thousands of rows long and find certain names that existed in the log file and copy these rows to another CSV. I had to do this for dozens of names, each of which could have dozens of matches. Instead of searching for each of these names and copying the rows to another CSV file (which would have taken hours!) I decided to write a script that would do this task in a matter of seconds:

The script below opens one CSV; the file which you want to search. It then saves a new file with the results. There is a list at the start of of the program called people. You can add your own strings here which you are looking for in your CSV file:

 

The Script


import csv

people = [‘Joe Bloggs’, ‘Tim O Reilly’, ‘Mary Bloggs’]

with open(‘users.csv’) as oldfile, open(‘report.csv’, ‘w’) as newfile:
for line in oldfile:
if any(people in line for people in people):
newfile.write(line)

Example

Say we have a log file with 1000s of rows and we want to find all activity for the users Joe Bloggs, Tim O’Reilly and Mary Bloggs. The log file may look something like this:

User Action
Joe Bloggs Login
Mary Burke login
Darren O Neill Updated post
Joe Bloggs Updated post
Tim O Reilly login
Mary Bloggs login

Now if we run the script we can see the file returned only contains the users we are interested in.

Joe Bloggs Login
Joe Bloggs Updated post
Tim O Reilly login
Mary Bloggs login