So now I’d like to take some time and illustrate how you can use excel and firefox extensions to fill in data for you, create scripts, and generally reduce the amount of work you have to do.
First, if you’ve never used CONCATENATE, make it your friend. It’s super easy. Type in =CONCATENATE to any cell and then follow it with (“your input goes here”). Remember to use quotations around the text just like that there. This is useful for SQL scripting since you have defined columns which can be pasted directly from the database output into your spreadsheet. Ex: Product Name (Cell C1), Product ID (Cell B1), NewProductName (Cell C1).
You can write a script to update this information by typing: =CONCATENATE(“Update <ExampleTbl> set ProductName ‘”,C1,”‘ where ProductID = ‘”,B1,”‘”)
So the ‘ on either side of the cell info is to tell SQL that you are using variable characters, and the quotes and commas are how Excel knows what cell you’re referencing. The very last quotation mark is simply to close the quotes from the beginning of the concatenate formula. You can then drag that down and automatically fill in new names for all the products that need updating.
Another useful formula is the IF statement, which can be combined with many others and is very versatile. One of my favorite uses is when you have to changed a command at the very end of a list of scripts. For example, every single query needs to have “union all” at the end save the last one. Let’s say there’s a list of product IDs from A1 to A20. The IF(ISBLANK) formula needs to be used to do this, and you would tell it to see if the cell below the first one in the list is empty or not, which defines if you’ve reached the end of your list.
So now you’ve updated those product names and you want to make sure they’re succeeded.
Formula: =IF(ISBLANK(A2), CONCATENATE(“select from ExampleTbl with (nolock) where ProductId = ‘”,A1,”‘”), CONCATENATE(“select from ExampleTbl with (nolock) where ProductId = ‘”,A1,”‘ union all”).
This formula essentially looks to see if A2 is blank. If it is, the if commands tells Excel to use the first statement if the given criteria are met. But if not, the second command will be used. This will automatically fill in union all to all queries but the last one. If statements are also useful to make sure data matches:
=IF(A1=C3, “Names Match”,”Names Do Not Match”) which can make comparing data entered into the system and the original product info as easy as dragging and seeing if anything doesn’t match. Useful when you have lots of data entry to check.
My new position at work involves a lot of file manipulation and folder creation/moving. To make this as easy as possible, the best way is to start with VLOOKUPs. VLookup stands for vertical lookup, and will look up information from a table arranged vertically. A typical formula looks like this: =VLOOKUP(C2,A2:B20, 2, False). This is telling the vlookup to look for the information from cell C2 in columsn A2:B20 (your table, the first cell on top left and last on bottom right), and to return the corresponding information for whatever is in cell C2 from the 2nd column of the table. False tells the command to return only exact matches, while true will look for any matching words. For every category we may have, there is a standard directory (ex: Pet Items\Cats) and so you build out a list of all category names and their corresponding directories. In one cell of your template you will always have to paste the category name(C2), and then the next cell would have a vlookup like this: =VLOOKUP(C2,A2:B20, 2, False). A, the 1st column of the table, would be the names of your categories, and B (2nd column) would be the directory of each category.
I also make brand new folders, and you can concatenate the directory name to add the new names like this. D2 is directory lookup cell, E2 is new folder name: CONCATENATE(“”,D2,”",E2,”") which would get you Pet Items\Cats\NewFolderName in cell F2.
Say you want to create that new folder. Again you can use concatenate, this time with windows command line commands: =CONCATENATE(“CD “”Pet Items\Cats & MKDIR “”,E2,”"”) which would essentially create a new folder in that category for every new name you need. This drastically reduces the time spent creating new folders and is much simpler than right click, new folder, copy name, paste name, hit enter, do it again. It typically takes me about 30 seconds to create 80 folders in this way, if that long.
You can also use the windows command line and concatenate to copy new files to these folders. Again, this requires standardized file locations for the files in question. I recommend making one if you don’t have it already, and then adding a new column for the filename itself. The easiest way to get the file names is click start, click run, then type cmd and hit enter. In the command prompt box type this: CD “DirectoryName” and hit enter to change to the needed directory. then type dir > FileList.txt. Dir lists all the files in a directory, and > tells it to make a file out the output (in this case, a text file). Next just open the text file, and paste it into excel. Using text to columns, choose standard width, and move the line between the filesize and the file name, then next and finish. It will create a new column with all names which you can then add into cell F2
Then simply concatenate together the directories with the file names and you’re all set: =CONCATENATE(“XCOPY “”Pets\”,FilenameCell,” “”Pets\Cats\NewFolder”" /s /i”) this command will add in all needed info to copy any files you may need, and you can then select and paste all commands at once to run multiple copy actions at one time. I actually recommend doing 3-5 at a time as you cannot view all error messages after a certain amount.
Moving on to firefox, some of the extensions I use most at work are iMacros, AutoCopy, and Clippings. iMacros basically records all mouse clicks and input into text boxes, which you can save and run again over and over. Think of it like this: If you are constantly pasting in data, then clicking a button, and then typing in something else, that can be recorded once and then when you double click a macro the whole thing is run before your eyes, kind of like watching a ghost use your computer.
What makes this even more powerful is that you can edit the scripts, simply by right clicking on them. The most useful edit I’ve found is telling it to paste from your clipboard, instead of whatever value was used when you first made the macro. To do this, make a macro and save it. Then right click it, choose edit, and find the line with the copied information. Highlight the copied info and replace it with this: {{!CLIPBOARD}} This will tell it to paste whatever’s on the clipboard. This works really well unless you happen to have copied info your system won’t recognize, like your girlfriend’s phone number or an essay 
Other times, you can find out how webpages and dropdown menus were built by editing the macro. For example, at work when we choose a name from a dropdown, we just type it in. But each of those names actually has an ID associated with it, and when viewing the macro after saving it I found that it was not a name that showed up, but actually the matching ID. Knowing this, you can again replace that ID with {{!CLIPBOARD}} and then quickly get to the info you need just by copying an ID.
Autocopy will automatically copy highlighted text after a user-determined amount of time, and clippings allows you to save your most used responses, like “Hey , here’s the thing you wanted” where you can just copy and paste in the person’s name in the greeting. It may not be personal but it sure is quick and means you don’t have to do any more typing of repetitive forms and letters.
The uses are endless but your time is not, so try out these commands and make excel and firefox do all the hard work so you don’t have to!